Thread: Retrieving a field from the NEW record
Hi all,
I'm tearing my hair out trying to solve the following problem. I
want to be able to retrieve the value of the primary key in a trigger
function. I have tried using PL/PGSQL and PL/PERL, but a PL/PERL
trigger function
crashes the postgress process.
I am able to use TG_RELNAME and dblink_get_pkey to retrieve the name of
the primary key, but I cannot for the life of me figure out how to
reference the primary key name I retrieved in the NEW rowtype variable.
In other words, I want:
NEW.pkName, where pkName is a variable holding the name of my primary key.
Is there any way to do this using PL/PGSQL?
Thanks,
Eric
On Tue, Jan 18, 2005 at 04:21:51PM -0500, Eric E wrote:
> I'm tearing my hair out trying to solve the following problem. I
> want to be able to retrieve the value of the primary key in a trigger
> function. I have tried using PL/PGSQL and PL/PERL, but a PL/PERL
> trigger function crashes the postgress process.
What versions of PostgreSQL and Perl are you using? If the backend
is crashing then check to see if the bug has been fixed in a newer
version of PostgreSQL. If not then please submit a bug report with
a repeatable test case.
I wrote a simple PL/Perl trigger in 8.0.0 and was able to access
$_TD->{new}{$pk}, where $pk held the name of a column. Maybe the
bug has been fixed in the latest code.
> In other words, I want:
>
> NEW.pkName, where pkName is a variable holding the name of my primary key.
I'm not aware of a way to do this in a PL/pgSQL trigger. I didn't
think EXECUTE would work and indeed it doesn't, at least not the
way I tried:
ERROR: NEW used in query that is not in a rule
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Howdy Michael,
Thanks for the help. I 'm presently using 8.0-beta2 on Windows. I
will upgrade that to 8.0 release today. However, my production server
is 7.4.2 (on Linux), and I'm somewhat reticent to migrate to 8.0 because
it is still pretty new. I belive I came across a message that indicated
that you could do such a thing in TCL. Do you know if this is true?
Can TCL in 7.4.2 get a field from a rowtype varaible like this?
What you outline with Perl is exactly the approach I would take, and
that's what led to crashing the server process. Perhaps PL/PERL is not
totally stable on Windows yet? If that's the case I will indeed submit
a bug report with a test case.
Many thanks,
Eric
Michael Fuhr wrote:
>On Tue, Jan 18, 2005 at 04:21:51PM -0500, Eric E wrote:
>
>
>
>> I'm tearing my hair out trying to solve the following problem. I
>>want to be able to retrieve the value of the primary key in a trigger
>>function. I have tried using PL/PGSQL and PL/PERL, but a PL/PERL
>>trigger function crashes the postgress process.
>>
>>
>
>What versions of PostgreSQL and Perl are you using? If the backend
>is crashing then check to see if the bug has been fixed in a newer
>version of PostgreSQL. If not then please submit a bug report with
>a repeatable test case.
>
>I wrote a simple PL/Perl trigger in 8.0.0 and was able to access
>$_TD->{new}{$pk}, where $pk held the name of a column. Maybe the
>bug has been fixed in the latest code.
>
>
>
>>In other words, I want:
>>
>>NEW.pkName, where pkName is a variable holding the name of my primary key.
>>
>>
>
>I'm not aware of a way to do this in a PL/pgSQL trigger. I didn't
>think EXECUTE would work and indeed it doesn't, at least not the
>way I tried:
>
>ERROR: NEW used in query that is not in a rule
>
>
>
On Wed, Jan 19, 2005 at 09:26:11AM -0500, Eric E wrote: > I belive I came across a message that indicated that you could do > such a thing in TCL. Do you know if this is true? Can TCL in 7.4.2 > get a field from a rowtype varaible like this? See the PL/Tcl trigger documentation -- it has an example of accessing columns whose names are in variables. > What you outline with Perl is exactly the approach I would take, and > that's what led to crashing the server process. Perhaps PL/PERL is not > totally stable on Windows yet? If that's the case I will indeed submit > a bug report with a test case. Please do, but try 8.0.0 first. I don't know how stable PL/Perl is on Windows, but there have been bug fixes in PL/Perl since the 8.0.0beta2 version you said you were running. -- Michael Fuhr http://www.fuhr.org/~mfuhr/