pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
PQexec and SPI_exec 2+ messages / 2 participants [nested] [flat]
* PQexec and SPI_exec @ 2004-08-25 14:21 Pedro B. <pedro.borracha@netcabo.pt> 0 siblings, 1 reply; 2+ messages in thread From: Pedro B. @ 2004-08-25 14:21 UTC (permalink / raw) To: pgsql-sql Hello everyone. I'm experiencing some doubts regarding a procedure i have (.c compiled as .so) running as an 'after insert for each row' trigger. This trigger is supposed to do a simple query, something like SELECT * FROM table order by id where processed=0 limit 1 It's not the perfect way to get the vars of the insert itself, but the result is a set of 45 columns, and the operations of the trigger are somehow complex, so .c is really a necessity on this one, and as long as this select actually returns the proper values, i can deal with it later. But my problem is not one of a structure nature: my problem is the fact that using SPI_exec, and the SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, columnX), and DatumGetInt32(DirectFunctionCall1(int4in, CStringGetDatum(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, columnX)))), etc, it all works fine. the SPI-running-from-the-triggered-.so can detect the correct values - from the insert that triggered it. I would prefer to use the more friendly PQexec and the simpler PQgetvalue(res,0,X), but this last approach does not return the values of the insert that triggered it. It returns the values from the "the last insert before this one". What is the proper way to make this method work? I'm sorry if this might be a basic question, but i have tried so many things, that i'm probably too puzzled right now to make any sense. Any help is deeply appreciated. Thanks, \\Pedro ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PQexec and SPI_exec @ 2004-08-25 14:53 Jan Wieck <JanWieck@Yahoo.com> parent: Pedro B. <pedro.borracha@netcabo.pt> 0 siblings, 0 replies; 2+ messages in thread From: Jan Wieck @ 2004-08-25 14:53 UTC (permalink / raw) To: Pedro B. <pedro.borracha@netcabo.pt>; +Cc: pgsql-sql On 8/25/2004 10:21 AM, Pedro B. wrote: > Hello everyone. > > I'm experiencing some doubts regarding a procedure i have (.c compiled > as .so) running as an 'after insert for each row' trigger. > > This trigger is supposed to do a simple query, something like > SELECT * FROM table order by id where processed=0 limit 1 > > It's not the perfect way to get the vars of the insert itself, but the > result is a set of 45 columns, and the operations of the trigger are > somehow complex, so .c is really a necessity on this one, and as long as > this select actually returns the proper values, i can deal with it later. > > But my problem is not one of a structure nature: my problem is the fact > that using SPI_exec, and the SPI_getvalue(SPI_tuptable->vals[0], > SPI_tuptable->tupdesc, columnX), and > DatumGetInt32(DirectFunctionCall1(int4in, > CStringGetDatum(SPI_getvalue(SPI_tuptable->vals[0], > SPI_tuptable->tupdesc, columnX)))), etc, it all works fine. > the SPI-running-from-the-triggered-.so can detect the correct values - > from the insert that triggered it If the trigger is supposed to use values from the tuple that triggered its call, then it should not select them from the table but use the provided tg_trigtuple in the TriggerData structure. See http://www.postgresql.org/docs/current/static/trigger-interface.html for details. > > I would prefer to use the more friendly PQexec and the simpler > PQgetvalue(res,0,X), but this last approach does not return the values > of the insert that triggered it. It returns the values from the "the > last insert before this one". What is the proper way to make this method > work? There is no proper way to make this work at all. What you are doing in this case is to have the database server process that is handling your query open another client connection to the server, starting another database server process. This cannot work. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-08-25 14:53 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-08-25 14:21 PQexec and SPI_exec Pedro B. <pedro.borracha@netcabo.pt> 2004-08-25 14:53 ` Jan Wieck <JanWieck@Yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox