Re: looping on NEW and OLD in a trigger

Поиск
Список
Период
Сортировка
От Fabrízio de Royes Mello
Тема Re: looping on NEW and OLD in a trigger
Дата
Msg-id AANLkTik+gTf0iVj-1veWpymQTnQzdCeK8uRwF1pz_Gp_@mail.gmail.com
обсуждение исходный текст
Ответ на Re: looping on NEW and OLD in a trigger  (Dmitriy Igrishin <dmitigr@gmail.com>)
Ответы Re: looping on NEW and OLD in a trigger  (Merlin Moncure <mmoncure@gmail.com>)
Список pgsql-general


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$;

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

INSERT INTO person VALUES (1, 'Fabrizio', 'Mello', '1979-08-08');


This example works more then one version of PostgreSQL.

Best regards,

--
Fabrízio de Royes Mello
>> Blog sobre TI: http://fabriziomello.blogspot.com

В списке pgsql-general по дате отправления:

Предыдущее
От: Dmitriy Igrishin
Дата:
Сообщение: Re: looping on NEW and OLD in a trigger
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: looping on NEW and OLD in a trigger