Search Postgresql Archives

Re: looping on NEW and OLD in a trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:

CREATE TABLE person(id integer, fname text, lname text, birthday date);

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

CREATE OR REPLACE FUNCTION test_dynamic()
 RETURNS trigger
 LANGUAGE plpgsql
 AS $func$
DECLARE
  _newRec hstore := hstore(NEW);
  _field text;
BEGIN
  FOR _field IN SELECT * FROM skeys(_newRec) LOOP
    RAISE NOTICE '%', _field;
  END LOOP;

  RETURN NEW;
END;
 $func$;

Regards,
Dmitriy

2010/8/26 Michael P. Soulier <michael_soulier@xxxxxxxxx>
Hi,

I'm very new to writing postgres procedures, and I'm trying to loop over
the fields in the NEW and OLD variables available in an after trigger,
and I can't quite get the syntax correct.

Could someone point me at an example?

Thanks,
Mike
--
Michael P. Soulier <michael_soulier@xxxxxxxxx>, 613-592-2122 x2522
"Any intelligent fool can make things bigger and more complex... It takes a
touch of genius - and a lot of courage to move in the opposite direction."
--Albert Einstein

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux