Re: looping on NEW and OLD in a trigger

On Sat, Aug 28, 2010 at 6:23 PM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)com> wrote:
> 2010/8/28 Dmitriy Igrishin <dmitigr(at)gmail(dot)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