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 ofgenerating 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?
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