On Sat, Aug 28, 2010 at 6:23 PM, Fabrízio de Royes Mello <fabriziomello@xxxxxxxxx> wrote: > 2010/8/28 Dmitriy Igrishin <dmitigr@xxxxxxxxx> >> >> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
