pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedTantalizing reference to passing data from NEW variable in a trigger function...
3+ messages / 3 participants
[nested] [flat]
* Tantalizing reference to passing data from NEW variable in a trigger function...
@ 2004-04-26 15:38 Rich Cullingford <rculling@sysd.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rich Cullingford @ 2004-04-26 15:38 UTC (permalink / raw)
To: pgsql-interfaces
All,
This question seems to have been asked a number of times on this list,
recently (including by me) without being answered: how to pass the row
data stored in the NEW variable from a trigger function into a
sub-function. None of the obvious syntaxes work, including one by a
respondent in this list that suggested assigning NEW to a record
variable, and then passing *that*. After searching up and down the
archives, I see the following tantalizing snippet (from 6/2003) in the
context of a rule:
Gunter Diehl <gdid ( at ) gmx ( dot ) de> writes:
> create funtion f1(v, v) returns void as '...'
> create rule vupt as on update to v do instead select f1(new, old)
> While accepting this definitions, pg says it can not handle "whole-tuple
> references" at runtime.
FWIW, the "old" case works fine. "new" doesn't work so well because the
new row hasn't been formed into a tuple at the point where the rule
runs; it only exists as a list of variables. (This is a rather handwavy
explanation, but I think it captures the key point.) While this could
probably be fixed with some effort, I doubt it's going to happen soon.
Is there any chance of doing what you want with a trigger instead of a
rule?
^^^^^
If there's a page somewhere that says how to do this, I'd be extremely
grateful for a pointer. The obvious maneuver of copying NEW's data
field-by-field into a composite variable is rather unappetizing when the
row is wide...
Rich Cullingford
rculling@sysd.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tantalizing reference to passing data from NEW variable
@ 2004-04-26 17:05 Joe Conway <mail@joeconway.com>
parent: Rich Cullingford <rculling@sysd.com>
0 siblings, 1 reply; 3+ messages in thread
From: Joe Conway @ 2004-04-26 17:05 UTC (permalink / raw)
To: Rich Cullingford <rculling@sysd.com>; +Cc: pgsql-interfaces
Rich Cullingford wrote:
> This question seems to have been asked a number of times on this list,
> recently (including by me) without being answered: how to pass the row
> data stored in the NEW variable from a trigger function into a
> sub-function. None of the obvious syntaxes work, including one by a
> respondent in this list that suggested assigning NEW to a record
> variable, and then passing *that*. After searching up and down the
> archives, I see the following tantalizing snippet (from 6/2003) in the
> context of a rule:
The standard answer to this has been to use PL/Tcl for your trigger
function instead of PL/pgSQL. I imagine PL/Python would work also, but
I'm not sure.
(FWIW, PL/R would work too, but I doubt you would want to install R just
to have this capability.)
I looked a bit at making this work in PL/pgSQL at some point -- IIRC the
issues to be solved were non-trivial.
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tantalizing reference to passing data from NEW variable
@ 2004-04-28 05:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-04-28 05:09 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Rich Cullingford <rculling@sysd.com>; pgsql-interfaces
Joe Conway <mail@joeconway.com> writes:
> Rich Cullingford wrote:
>> This question seems to have been asked a number of times on this list,
>> recently (including by me) without being answered: how to pass the row
>> data stored in the NEW variable from a trigger function into a
>> sub-function.
> I looked a bit at making this work in PL/pgSQL at some point -- IIRC the
> issues to be solved were non-trivial.
I think that this might be less non-trivial given the recent changes in
the composite-type-datum support. I haven't got round to looking at
plpgsql in particular, though.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-04-28 05:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-26 15:38 Tantalizing reference to passing data from NEW variable in a trigger function... Rich Cullingford <rculling@sysd.com>
2004-04-26 17:05 ` Re: Tantalizing reference to passing data from NEW variable Joe Conway <mail@joeconway.com>
2004-04-28 05:09 ` Re: Tantalizing reference to passing data from NEW variable Tom Lane <tgl@sss.pgh.pa.us>
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