-----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) = @[EMAIL PROTECTED]
$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@[EMAIL PROTECTED]
> 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@[EMAIL PROTECTED]
)
|> 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-----
--
Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
|
