How to get the Notice from pgsql function text

[aspirevishal]

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

[Alexey.mdr]

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.