SEO

#1 (**permalink**) 04-09-2008, 09:35 AM

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#2 (**permalink**) 04-09-2008, 09:35 AM

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEW2jaIB7bNG8LQkwRAjZaAJ9BA4rIBURbE1c12TUq7I haBzidLwCgjcQC
17yEK/ZlpV5wvKZmlwyZI3s=
=q+7Y
-----END PGP SIGNATURE-----

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Field names and NEW