Re: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)

On Wed, Aug 24, 2011 at 1:03 PM, ivan_14_32 <ivan_14_32(at)mail(dot)ru> wrote:
> 01.05.2011 12:58, Basil Bourque wrote:
>>
>> Hoorah! I was able to complete my single PL/pgSQL function to create
>> history records tracking individual field value changes generically for all
>> my tables. Some developers call this an "audit trail", though an accountant
>> might say otherwise.
>>
> I made auditing based on triggers like aforementioned. And now I need fill
> audit table with already presented data. But there is a problem.
>
> within trigger
> EXECUTE 'SELECT ($1)."name"::text' INTO newVal USING NEW;
> works fine
>
> but function (table "decor" has field "name")
> CREATE OR REPLACE FUNCTION "odb_InitLog"()
>  RETURNS void AS
> DECLARE
>  obj record;
> BEGIN
>  FOR obj IN (SELECT * FROM "decor") LOOP
>    EXECUTE 'SELECT ($1)."name"::text' INTO newVal USING obj;
>  END LOOP;
> END;
> doesn't work - ERROR: could not identify column "name" in record data type

folks, this (dynamic field access of generic record in plpgsql) is the
number one FAQ on this list. please check the archives before
searching (not picking on you specifically, it just gets asked in some
variant an awful lot). First point: hstore > execute. if you _must_
use execute, you have to cast at some point. when you pass a record
to something, it doesn't have the necessary context to know the field
names. In your case, though, an explicit composite type is the way
to go:

DECLARE
 obj decor;
BEGIN
 FOR obj IN SELECT * FROM decor LOOP
newVal := obj.name;
 END LOOP;
END;

merlin