Обсуждение: Indirect access to NEW or OLD records

Hello.

I have a variable with a field name and want to extract this field
value from NEW record:

DECLARE
    field VARCHAR = 'some_field';
BEGIN
    ...
    value := NEW.{field};  -- ???
END;

Is it possible in pl/pgsql?

I have found one speed-inefficient solution: convert NEW to string and
then - use EXECURE with customly-build query to extract a value from
that constant string. But it is too slow. Is there better solution?

"Dmitry Koterov" <dmitry@koterov.ru> writes:
> I have a variable with a field name and want to extract this field
> value from NEW record:

> DECLARE
>     field VARCHAR = 'some_field';
> BEGIN
>     ...
>     value := NEW.{field};  -- ???
> END;

> Is it possible in pl/pgsql?

No.  Quite aside from the lack of syntax for that, plpgsql would be
unhappy if the data type wasn't the same on successive executions.

Use one of the less strongly typed PLs instead.  I believe this is
pretty easy in plperl or pltcl.  (Maybe plpython too, but I don't know
that language.)

            regards, tom lane

I have tried plperl, but the following sample does not work:

CREATE FUNCTION "extract_field_as_varchar" (rec record, field varchar)
RETURNS varchar AS
$body$
...
$body$
LANGUAGE 'plperl' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

It says "ERROR:  plperl functions cannot take type record".

So, I cannot ever create an utility function to call it as:

value := extract_field_as_varchar(NEW, 'field');

Seems I have to write an entire trigger in plperl to access NEW record
indirectly?




On Thu, Sep 25, 2008 at 10:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dmitry Koterov" <dmitry@koterov.ru> writes:
>> I have a variable with a field name and want to extract this field
>> value from NEW record:
>
>> DECLARE
>>     field VARCHAR = 'some_field';
>> BEGIN
>>     ...
>>     value := NEW.{field};  -- ???
>> END;
>
>> Is it possible in pl/pgsql?
>
> No.  Quite aside from the lack of syntax for that, plpgsql would be
> unhappy if the data type wasn't the same on successive executions.
>
> Use one of the less strongly typed PLs instead.  I believe this is
> pretty easy in plperl or pltcl.  (Maybe plpython too, but I don't know
> that language.)
>
>                        regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>