Обсуждение: looping on NEW and OLD in a trigger

Hi,

I'm very new to writing postgres procedures, and I'm trying to loop over
the fields in the NEW and OLD variables available in an after trigger,
and I can't quite get the syntax correct.

Could someone point me at an example?

Thanks,
Mike
--
Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522
"Any intelligent fool can make things bigger and more complex... It takes a
touch of genius - and a lot of courage to move in the opposite direction."
--Albert Einstein

"Michael P. Soulier" <michael_soulier@mitel.com> writes:
> I'm very new to writing postgres procedures, and I'm trying to loop over
> the fields in the NEW and OLD variables available in an after trigger,
> and I can't quite get the syntax correct.

If you're trying to do this in plpgsql, the answer is you can't.
plpgsql doesn't support dynamic field references, which is what you'd
need for what (I think) you're trying to do.

You can do it in pltcl or plperl, and probably also plpython though
I don't know enough python to be sure.

            regards, tom lane

On 26/08/10 Tom Lane did say:

> If you're trying to do this in plpgsql, the answer is you can't.
> plpgsql doesn't support dynamic field references, which is what you'd
> need for what (I think) you're trying to do.
>
> You can do it in pltcl or plperl, and probably also plpython though
> I don't know enough python to be sure.

Ok, I'll try plpython then.

Thanks,
Mike
--
Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522
"Any intelligent fool can make things bigger and more complex... It takes a
touch of genius - and a lot of courage to move in the opposite direction."
--Albert Einstein

On Thu, Aug 26, 2010 at 12:59 PM, Michael P. Soulier
<michael_soulier@mitel.com> wrote:
> On 26/08/10 Tom Lane did say:
>
>> If you're trying to do this in plpgsql, the answer is you can't.
>> plpgsql doesn't support dynamic field references, which is what you'd
>> need for what (I think) you're trying to do.
>>
>> You can do it in pltcl or plperl, and probably also plpython though
>> I don't know enough python to be sure.
>
> Ok, I'll try plpython then.

For a more in-depth treatment of the topic, see:

http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg139182.html

(the entire thread is worth reading IMO if you want to really
understand dynamic record inspection really well, both in pl/pgsql and
other PLs).

merlin

Hey Michael,

As of PostgreSQL 9.0 you can do it from PL/pgSQL by
using hstore module
(http://www.postgresql.org/docs/9.0/static/hstore.html)

I wrote an example for you:

CREATE TABLE person(id integer, fname text, lname text, birthday date);

CREATE TRIGGER person_test_trigger BEFORE INSERT
  ON person FOR EACH ROW
  EXECUTE PROCEDURE test_dynamic();

CREATE OR REPLACE FUNCTION test_dynamic()
 RETURNS trigger
 LANGUAGE plpgsql
 AS $func$
DECLARE
  _newRec hstore := hstore(NEW);
  _field text;
BEGIN
  FOR _field IN SELECT * FROM skeys(_newRec) LOOP
    RAISE NOTICE '%', _field;
  END LOOP;

  RETURN NEW;
END;
 $func$;

Regards,
Dmitriy

Another way to do that is create a temp table from NEW or OLD record and loop over the fields using system catalog.

On Sat, Aug 28, 2010 at 6:23 PM, Fabrízio de Royes Mello
<fabriziomello@gmail.com> wrote:
> 2010/8/28 Dmitriy Igrishin <dmitigr@gmail.com>
>>
>> Hey Michael,
>>
>> As of PostgreSQL 9.0 you can do it from PL/pgSQL by
>> using hstore module
>> (http://www.postgresql.org/docs/9.0/static/hstore.html)
>>
>> I wrote an example for you:
>>
>> <cut>
>>
>
> Another way to do that is create a temp table from NEW or OLD record and
> loop over the fields using system catalog.
>
> CREATE TABLE person(id integer, fname text, lname text, birthday date);
>
> CREATE OR REPLACE FUNCTION test_dynamic()
>  RETURNS trigger
>  LANGUAGE plpgsql
>  AS $func$
> DECLARE
>   _field text;
> BEGIN
>   CREATE TEMP TABLE tmp_new AS SELECT NEW.*;
>   FOR _field IN SELECT column_name FROM information_schema.columns WHERE
> table_name = 'tmp_new' AND table_schema ~ '^pg_temp' LOOP
>     RAISE NOTICE '%', _field;
>   END LOOP;
>
>   RETURN NEW;
> END;
>  $func$;

If you're going to do it that way -- I'd greatly prefer using
TG_TABLE_NAME/TG_TABLE_SCHEMA.  These are directly intended for this
kind of purpose.  Temporary tables are a bit of of a bugaboo in terms
of pl/pgsql performance...especially in high traffic functions like
per row triggers...double especially 'on commit drop' temp tables.

merlin