How to get the Notice from pgsql function text

How to get the Notice from pgsql function text

Postby aspirevishal » Wed 19 Dec 2007 01:19

Please guide me if I am on the right track
I want to display the notice from a pgsql function
e.g

Code: Select all
CREATE FUNCTION my_function () RETURNS integer
    AS 'DECLARE
mxid integer := 20;
BEGIN
for i in 1..mxid loop

RAISE NOTICE '' I am working on following % '' ,i;
end loop

RETURN
mxid;
END;
'


Now to get the notice displayed should I do this

Code: Select all
try
'execute the function as stored procedure

catch ex as CoreLab.PostgreSql.PgSqlException
label1.text = ex.message
end try





please ignore any errors, if any in postgresql function
aspirevishal
 
Posts: 30
Joined: Fri 31 Aug 2007 03:30
Location: India

Postby Alexey.mdr » Thu 20 Dec 2007 11:11

The function code:

Code: Select all
CREATE FUNCTION my_function () RETURNS integer
    AS 'DECLARE
mxid integer := 20;
BEGIN
for i in 1..mxid loop

RAISE NOTICE '' I am working on following % '' ,i;
end loop;

RETURN
mxid;
END;
'
language plpgsql;


And basically a piece of code you wanted:

Code: Select all

   private void button1_Click(object sender, EventArgs e)
        {           
            PgSqlCommand command = new PgSqlCommand();
            command.Connection = pgSqlConnection;
            command.CommandText = "my_function";
            command.CommandType = CommandType.StoredProcedure;

            pgSqlConnection.Open();
            command.ExecuteNonQuery();                 
        }

        private void pgSqlConnection_InfoMessage(object sender,
            CoreLab.PostgreSql.PgSqlInfoMessageEventArgs e)
        {
            MessageBox.Show(e.Errors[0].Message);
        }


If you raise a notice in a function you can catch this event by subscribing to the PgSqlConnection "InfoMessage" event.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for PostgreSQL