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
|
| Список | 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 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$;
END LOOP;
RETURN NEW;
END;
$func$;
CREATE TRIGGER person_test_trigger BEFORE INSERT
ON person FOR EACH ROW
EXECUTE PROCEDURE test_dynamic();
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,
В списке pgsql-general по дате отправления: