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:

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

7 Comments:

At October 19, 2009 at 12:02 PM , Anonymous Anonymous said...

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?

 
At October 19, 2009 at 12:08 PM , Blogger Pavel Stěhule said...

a) You need PostgreSQL 8.5 alfa 2 version, or code from CVS, b) you have to install hstore from contrib modules.

 
At October 19, 2009 at 12:32 PM , Anonymous Anonymous said...

Is 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)

 
At October 19, 2009 at 1:15 PM , Anonymous Anonymous said...

I forgot to say that I use the 8.4 version.

 
At October 20, 2009 at 8:30 AM , Blogger Pavel Stěhule said...

You 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.

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

 
At October 20, 2009 at 8:35 AM , Blogger Pavel Stěhule said...

look on http://archives.postgresql.org/pgsql-general/2008-05/msg00314.php

 
At March 8, 2012 at 8:43 AM , Blogger Joshua D. Burns said...

Thank 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!

 

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home