[SQL] Creating a RULE for UPDATing a VIEW
Using PostgreSQL version 8.3.0: For various reasons, I have a number of VIEWs that are (except for the schema/table/view names) automatically generated as identity mappings of corresponding TABLEs; eg: CREATE VIEW public.yyy AS SELECT * FROM private.zzz; Since we don't have updatable VIEWS yet, I tried: CREATE RULE _update AS ON UPDATE TO public.yyy DO INSTEAD UPDATE private.zzz SET (*) = NEW.* WHERE key_field = OLD.key_field; In order to make the automatic generation easy, I'm trying to make the syntax as general as possible, in particular, so that I don't have to list all of the column names (that appears to work). However, the above (and other ingenious, but also incorrect, syntaxes) produces an error message. So, I tried: CREATE RULE _update AS ON UPDATE TO public.yyy DO INSTEAD (DELETE FROM private.zzz WHERE key_field = OLD.key_field; INSERT INTO private.zzz VALUES( NEW.*) ); This is syntactically accepted, but when I attempt to UPDATE a row, the old row is deleted but the new row is not inserted. Manually listing the NEW.columns in place of "NEW.*" doesn't help. -- Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a RULE for UPDATing a VIEW
"Dean Gibson (DB Administrator)" writes: > So, I tried: > CREATE RULE _update AS ON UPDATE TO public.yyy DO INSTEAD > (DELETE FROM private.zzz WHERE key_field = OLD.key_field; >INSERT INTO private.zzz VALUES( NEW.*) ); Nope, won't work, standard gotcha for rules newbies. As soon as you delete in the first command, the row no longer exists in the view, and "new.*" is just a macro for a view reference. AFAIK there really isn't any way to do it except ON UPDATE DO INSTEAD UPDATE private.zzz SET f1 = new.f1, f2 = new.f2, ... WHERE key_field = old.key_field; BTW, you should also consider adding RETURNING clauses to these rules so that UPDATE RETURNING &etc will work on the views. Here at least you can use "RETURNING *" ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using a list to query
On Saturday 02 May 2009 06:34:57 pm Craig Ringer wrote: > johnf wrote: > > I have a list (or array) of primary keys stored in a field (data type > > text). I would like to use the list to retrieve all the data from a table > > based on the list. > > > > my text field contains: > > '123,134,343,345' > > > > I would to do something like the following: > > > > Select * from table1 where table1.pkid in (select myTextfield from > > table2) > > > > So the question is there a simple way to use the list to retrieve my > > data? > > http://www.postgresql.org/docs/8.3/static/functions-matching.html > > SELECT * FROM table1 > WHERE table1.pkid IN ( > SELECT x FROM regexp_split_to_table(myTextfield, ',') AS x > ); > > ... but you should consider storing your list in an array instead, or > using a more conventional child table with a (pkid, refid) pair list. > > -- > Craig Ringer Thanks - I think this will work very well. I considered an array but at the moment I don't have an easy way of retrieving data from an array. I'm working on that as I type. The other solution would be a table but that seems over kill for one field. Thanks again -- John Fabiani -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
