Thread: Field names and NEW

Greetings to everyone on the list

I am attempting to write a system which would copy primary key values
to another table after insert. To achieve this, I wrote a trigger
function in Perl. I also wanted to make the function reusable, so the
fields included in the query are passed as function arguments. Here
is the code:

CREATE OR REPLACE FUNCTION ModifyInsert () RETURNS TRIGGER AS $$
     my $fieldnames = '';
     my $fieldvalues = '';
     my $mTablename = $_TD->{args}[1];

     for (my $i = 2; $i < $_TD->{argc}; $i++)
     {
         my $currentName = $_TD->{args}[$i];
         $fieldnames .= ", \"$currentName\"";
         $fieldvalues .= ", '$_TD->{new}{$currentName}'";
     }

     my $query = "INSERT INTO $mTablename (pgts_modification_type
$fieldnames) VALUES ('I' $fieldvalues)";
     spi_exec_query ($query);
     return;
$$ LANGUAGE PLPERL;

However, I would rather use PL/PgSQL, since the Perl interpreter
might not be installed by default. Is there a way to access the NEW
record without knowing the field names in advance?

--
Best regards,
Tuukka Norri

On Fri, May 05, 2006 at 11:50:05AM +0300, Tuukka Norri wrote:
> However, I would rather use PL/PgSQL, since the Perl interpreter
> might not be installed by default. Is there a way to access the NEW
> record without knowing the field names in advance?

No. pl/pgsql is not flexible enough as a language to handle
transformations like this effectively. If you know the table name you
may be able to use EXECUTE to build query strings, but it won't be
easy...

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

I would add that I also found on the pgsql-admin list a similar pltcl
way to do what you're after posted in October of 04 by Ian.

One way or the other though likely you will need to install first the

ActiveTCL

AND/OR

ActivePerl

From
http://www.activestate.com

if may also want to read in the PGDoc's about trusted vs untrusted
languages

Then run
createlang "<pltcl or plperl>" --dbname=yourdatabase
--username=superuser

If you're unable to find Ian's post (if you go looking) let me know I
will forward you a copy

There is also a project started called "pglog" located on

http://www.berlios.de/index.php.en

http://svn.berlios.de/svnroot/repos/pglog/trunk


Jim

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martijn van
Oosterhout
Sent: Friday, May 05, 2006 10:02 AM
To: Tuukka Norri
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Field names and NEW

On Fri, May 05, 2006 at 11:50:05AM +0300, Tuukka Norri wrote:
> However, I would rather use PL/PgSQL, since the Perl interpreter
> might not be installed by default. Is there a way to access the NEW
> record without knowing the field names in advance?

No. pl/pgsql is not flexible enough as a language to handle
transformations like this effectively. If you know the table name you
may be able to use EXECUTE to build query strings, but it won't be
easy...

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability
to litigate.