postgres=# SELECT * FROM foo;
a | b | c
----+----+--------
10 | 20 | Pavel
30 | 40 | Zdenek
(2 rows)
postgres=# select (each(hstore(foo))).* from foo;
key | value
-----+--------
a | 10
b | 20
c | Pavel
a | 30
b | 40
c | Zdenek
(6 rows)
CREATE OR REPLACE FUNCTION trgfce()
RETURNS trigger AS $$
DECLARE r record;
BEGIN
FOR r IN SELECT (each(hstore(NEW))).*
LOOP
RAISE NOTICE 'key:%, value: %', r.key, r.value;
END LOOP;
RETURN new;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE trgfce();
postgres=# INSERT INTO foo VALUES(80,90,'Zbynek');
NOTICE: key:a, value: 80
NOTICE: key:b, value: 90
NOTICE: key:c, value: Zbynek
INSERT 0 1
Pages
▼
Thursday, October 1, 2009
Dynamic access to record fields in PL/pgSQL
PL/pgSQL isn't dynamic language. Usually it isn't problem. This language is best as glue of SQL statements. One typical feature is strongly static access to record or row fields. But sometime we need dynamic access. This is very hard task in older version. Some better situation is in 8.4, and maybe in 8.5, this problem is definitely out.
New an enhancing of hstore module allows conversion from ROW type to hstore type (this is similar to hash table in Perl). We could to use this functionality:
It didn't work, an error saying that the hstore does not exist is shown. What do I have to install? Can I change the NEW reference by the table name?
ReplyDeletea) You need PostgreSQL 8.5 alfa 2 version, or code from CVS, b) you have to install hstore from contrib modules.
ReplyDeleteIs there any similar usage in 8.4 version? (I need to make an insert trigger that will check if have changes in all columns but the inserted data may not have all table's fields. The function hstore would be very helpful to do this)
ReplyDeleteI forgot to say that I use the 8.4 version.
ReplyDeleteYou can try to backport hstore module from 8.5 to 8.4 - I thing so this is possible, there are not significant changes in related areas.
ReplyDeleteIn 8.5 you can use EXECUTE USING trick
create or replace function foo() returns trigger as $$
declare
t text;
begin
for i in 1 .. tg_argv[0] loop
execute 'select cast ( $1 . ' || tg_argv[i] || ' as text)'
into t using new;
raise notice '% = %', tg_argv[i], t;
end loop;
return new;
end $$ language plpgsql;
look on http://archives.postgresql.org/pgsql-general/2008-05/msg00314.php
ReplyDeleteThank you sooo, so much for the "(each(hstore(foo))).*" piece, I've spent the past hour scouring the internet for this exact solution with no avail until now. :-D!
ReplyDelete