DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=EIxjMuUtF5J0n28XZG2jaBL9itDQ2ODJlmumtOOnEUt1RuqEXha+/LD4HAiHuI4MMivfn/Cf62w9BaLO+DZLpHywvCfrUs/5Rg/ZCt0MXeqTW3RAaJMmCUyd2rvU4C438I8bt8uKQRHlVnXwU/onG/5dqRgO+dOBVtYfiL+9du8=
Message-ID: <162867790804070511y7266c911w370db97a78c3c519@mail.gmail.com>
Date: Mon, 7 Apr 2008 14:11:23 +0200
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Martin Edlman" <edlman@fortech.cz>
Subject: Re: pl/PgSQL, variable names in NEW
Cc: pgsql-sql@postgresql.org
In-Reply-To: <47FA034B.7090201@fortech.cz>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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
>