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