pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Creating a RULE for UPDATing a VIEW 2+ messages / 2 participants [nested] [flat]
* Creating a RULE for UPDATing a VIEW @ 2009-05-03 16:50 Dean Gibson (DB Administrator) <postgresql@ultimeth.com> 0 siblings, 1 reply; 2+ messages in thread From: Dean Gibson (DB Administrator) @ 2009-05-03 16:50 UTC (permalink / raw) To: pgsql-sql 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Creating a RULE for UPDATing a VIEW @ 2009-05-03 17:36 Tom Lane <tgl@sss.pgh.pa.us> parent: Dean Gibson (DB Administrator) <postgresql@ultimeth.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2009-05-03 17:36 UTC (permalink / raw) To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; +Cc: pgsql-sql "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-05-03 17:36 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2009-05-03 16:50 Creating a RULE for UPDATing a VIEW Dean Gibson (DB Administrator) <postgresql@ultimeth.com> 2009-05-03 17:36 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox