Re: [GENERAL] retrieving function raise messages in ecpg embedded sql code

2012-06-25 Thread Haszlakiewicz, Eric 
 -Original Message-
 From: Jeff Davis [mailto:pg...@j-davis.com]
 
 On Mon, 2012-06-18 at 21:35 +, 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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] retrieving function raise messages in ecpg embedded sql code

2012-06-22 Thread Jeff Davis 
On Mon, 2012-06-18 at 21:35 +, 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] retrieving function raise messages in ecpg embedded sql code

2012-06-18 Thread Haszlakiewicz, Eric 
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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general