From: Pavel Stehule <pavel.stehule@gmail.com>
To: Martin Edlman <edlman@fortech.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: pl/PgSQL, variable names in NEW
Date: Mon, 7 Apr 2008 14:11:23 +0200
Message-ID: <162867790804070511y7266c911w370db97a78c3c519@mail.gmail.com> (raw)
In-Reply-To: <47FA034B.7090201@fortech.cz>
References: <47FA034B.7090201@fortech.cz>
Hello
no, it's not possible in plpgsql. Please, use plperl or plpython.
Regards
Pavel Stehule
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, edlman@fortech.cz
Subject: Re: pl/PgSQL, variable names in NEW
In-Reply-To: <162867790804070511y7266c911w370db97a78c3c519@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox