Re: pl/PgSQL, variable names in NEW

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

| no, it's not possible in plpgsql. Please, use plperl or plpython.

thanks for the response. It's as I expected and was afraid of :-(
I select data from DB using pl/PgSQL in the replace_values trigger and
then call plPerl function which returns value from NEW and OLD. The
problem is that as I need to pass NEW and OLD to the Perl function I get
error message "no function matching get_value(x_lokalita, name)" as NEW
and OLD are records of table x_lokalita.
My plPerl function is declared as get_value(record, name). Is it
possible to cast table record type "x_lokalita" to generic type
"record"? (NEW::record doesn't work!)
I call the trigger replace_values() on several tables so I don't know
the record type. Do I have to create get_value() for each table, eg.
get_value(x_lokalita, name)?

I don't want to rewrite whole trigger to plPerl as I would have to use
DBD-PgSPI.

CREATE OR REPLACE FUNCTION get_value(record, name) RETURNS character
varying AS $BODY$
my($rec, $col) = @_;
return $rec->{$col};
$BODY$ LANGUAGE 'plperl' VOLATILE;

CREATE OR REPLACE FUNCTION replace_values() RETURNS "trigger" AS $BODY$
-- code with SQL queries
-- ...
newval := get_value(NEW, col.attname);
oldval := get_value(OLD, col.attname);
IF newval <> oldval THEN
-- call other functions
END IF;
-- code
RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

| On 07/04/2008, Martin Edlman <edlman(at)fortech(dot)cz> wrote:
|> Hello,
|>
|> is it possible to use variables as field names in the NEW record?
|> Let's suppose I have a varchar attname containg the name of the field
and I
|> want to know a value that field of the NEW record.
|>
|> Problem is that I get an error 'record "new" has no field "attname"'. Of
|> course I want to use a value of NEW.author when col.attname = attname =
|> 'author'.
|>
|> Is there a solution?
|>
|> Example trigger function. It finds all columns in the table which are
|> referenced in other tables and checks if the value of the column has
|> changed. If yes, then invoke some other function. The problem is that the
|> column name is in the 'col' record and is different during the loop
and at
|> each function call.
|>
|> CREATE OR REPLACE FUNCTION replace_values() RETURNS trigger AS
|> $BODY$
|> DECLARE
|> col record;
|> attname varchar;
|> BEGIN
|> FOR col IN
|> SELECT DISTINCT pgaf.attname, pgaf.attnum
|> FROM pg_constraint, pg_attribute AS pgaf
|> WHERE pg_constraint.contype = 'f' -- fkey
|> AND pg_constraint.confrelid = TG_RELID -- table oid
|> AND pgaf.attrelid = TG_RELID
|> AND pgaf.attnum = ANY(pg_constraint.confkey) LOOP
|>
|> attname := col.attname;
|> IF NEW.attname <> OLD.attname THEN
|> RAISE NOTICE ' value changed from "%" to
|> "%"', OLD.attname, NEW.attname;
|> -- INVOKE OTHER FUNCTION
|> END IF;
|> END LOOP;
|>
|> END;
|> $BODY$
|> LANGUAGE 'plpgsql' VOLATILE;
|>
|> --
|> Martin Edlman
|> Fortech Ltd.
|> 57001 Litomysl, CZ
|>
|> --
|> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
|> To make changes to your subscription:
|> http://www.postgresql.org/mailpref/pgsql-sql
|>

- --
S pozdravem,

Martin Edlman
Fortech, spol. s r.o,
Ropkova 51, 57001 Litomyšl
Public GPG key: http://edas.visaci.cz/#gpgkeys
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFH+1HHqmMakYm+VJ8RAn8qAKCRNAxBjv3kIQ5eCMkH/OkWshNEqACfYI0L
oN4Gbz6cuoqRuZN1yl4DMew=
=NM+K
-----END PGP SIGNATURE-----