Message-ID: <47FB51C7.6090509@fortech.cz>
Date: Tue, 08 Apr 2008 13:06:47 +0200
From: Martin Edlman <edlman@fortech.cz>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: pl/PgSQL, variable names in NEW
References: <47FA034B.7090201@fortech.cz>
	<162867790804070511y7266c911w370db97a78c3c519@mail.gmail.com>
In-Reply-To: <162867790804070511y7266c911w370db97a78c3c519@mail.gmail.com>
OpenPGP: id=89BE549F;
	url=http://edas.visaci.cz/#gpgkeys
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

-----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@fortech.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@postgresql.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-----