Thread: retrieving function raise messages in ecpg embedded sql code

I'm trying to get some additional information back from a trigger to my embedded SQL
program, to essentially emulate Informix's way of generating serial values.
I can get the serial to be generated, but I'm trying to figure out how to get the
generated value back to my program with minimal changes to the SQL.

I have a trigger that looks a bit like this:

create table mytable (mycol integer, mycol2 integer);

create or replace function functionfoo() returns trigger as $QUOTED$
BEGIN
new.mycol = nextval(TG_TABLE_NAME || '_mycol_seq');
raise INFO using MESSAGE = 'A Message';
return new;
END;
$QUOTED$ LANGUAGE 'plpgsql';

create trigger mytable_insert_trig before insert on mytable for each row when (new.mycol = 0) execute procedure
functionfoo();


My ecpg program looks a bit like this:

    exec sql begin declare section;
    long mycol1;
    long mycol2;
    const char *mydb;
    exec sql end declare section;
    mycol1 = 0;
    mycol2 = 1;
    mydb = "mydb";
    exec sql connect to :mydb;
    exec sql prepare row_insert from "insert into mytable values (?, ?)";
    EXEC SQL EXECUTE row_insert using :mycol1, mycol2;


I can't figure out how to retrieve the message raised by the trigger.  I know it's
available in some cases, because I see the message when I insert a row through psql,
but even things like this:
   printf("%s\n", PQerrorMessage(ECPGget_PGconn(mydb)));

return nothing useful.  Is there a way to get this information?

Thanks,
eric

On Mon, 2012-06-18 at 21:35 +0000, Haszlakiewicz, Eric wrote:
> I'm trying to get some additional information back from a trigger to my embedded SQL
> program, to essentially emulate Informix's way of generating serial values.
> I can get the serial to be generated, but I'm trying to figure out how to get the
> generated value back to my program with minimal changes to the SQL.

Have you already looked at INSERT...RETURNING?

http://www.postgresql.org/docs/9.2/static/sql-insert.html

> I can't figure out how to retrieve the message raised by the trigger.  I know it's
> available in some cases, because I see the message when I insert a row through psql,
> but even things like this:
>    printf("%s\n", PQerrorMessage(ECPGget_PGconn(mydb)));
>
> return nothing useful.  Is there a way to get this information?

Yes, these messages are delivered via notice processing (not to be
confused with LISTEN/NOTIFY):

http://www.postgresql.org/docs/9.2/static/libpq-notice-processing.html

Regards,
    Jeff Davis

> -----Original Message-----
> From: Jeff Davis [mailto:pgsql@j-davis.com]
>
> On Mon, 2012-06-18 at 21:35 +0000, Haszlakiewicz, Eric wrote:
> > I'm trying to get some additional information back from a trigger to
> > my embedded SQL program, to essentially emulate Informix's way of
> generating serial values.
> > I can get the serial to be generated, but I'm trying to figure out
> how
> > to get the generated value back to my program with minimal changes to
> the SQL.
>
> Have you already looked at INSERT...RETURNING?
>
> http://www.postgresql.org/docs/9.2/static/sql-insert.html

Yes, I've started modifying things to use that, but I'd like to avoid doing
that so I can share the same code between postgresql and informix builds.

> > I can't figure out how to retrieve the message raised by the trigger.
> > I know it's available in some cases, because I see the message when I
> > insert a row through psql, but even things like this:
> >    printf("%s\n", PQerrorMessage(ECPGget_PGconn(mydb)));
> >
> > return nothing useful.  Is there a way to get this information?
>
> Yes, these messages are delivered via notice processing (not to be
> confused with LISTEN/NOTIFY):
>
> http://www.postgresql.org/docs/9.2/static/libpq-notice-processing.html

That looks like exactly what I'm looking for, I'll try it out.  Thanks!

eric