[SQL] CREATE RULE ON UPDATE/DELETE
Can a rule see the where statement in a query which it has been triggered by? or is it simply ignored?? what happens? i.e. CREATE TABLE foo ( id INTEGER PRIMARY KEY, name TEXT ); CREATE VIEW bar AS SELECT * FROM foo; -- Great view? CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET id = NEW.id, name = NEW.name WHERE OLD.id = id; Now if I do a: UPDATE bar SET id = id + 10, WHERE id > 10; What really happens? Does the update first select from bar, and pick out which rows to do the update on, and then do the update on these rows or what? I tried it, and I got an answer I cannot explain, first it works, then it doesn't: envisity=# CREATE TABLE foo ( envisity(# id INTEGER PRIMARY KEY, envisity(# name TEXT envisity(# ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for tabl e 'foo' CREATE envisity=# envisity=# CREATE VIEW bar AS SELECT * FROM foo; -- Great view? CREATE envisity=# envisity=# CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET foo.id = NEW.id, foo.name = NEW.name WHERE OLD.id = foo.id; ERROR: parser: parse error at or near "." envisity=# CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE envisity=# INSERT INTO foo (1, 't'); ERROR: parser: parse error at or near "1" envisity=# INSERT INTO foo VALUES(1, 't'); INSERT 57054 1 envisity=# INSERT INTO foo VALUES(2, 'tr'); INSERT 57055 1 envisity=# INSERT INTO foo VALUES(12, 'tg'); INSERT 57056 1 envisity=# INSERT INTO foo VALUES(15, 'tgh'); INSERT 57057 1 envisity=# INSERT INTO foo VALUES(14, 'th'); INSERT 57058 1 envisity=# UPDATE bar SET id = id + 10 > envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 3 -- Here it works envisity=# select * from bar; id | name +-- 1 | t 2 | tr 22 | tg 24 | th 25 | tgh (5 rows) envisity=# #CREATE VIEW bar AS SELECT * FROM foo; -- Great view? ERROR: parser: parse error at or near "#" envisity=# DROP VIEW bar; DROP envisity=# CREATE VIEW bar AS SELECT id * 2 as id, name FROM foo; -- Great view ? CREATE envisity=# CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 0 envisity=# select * from bar; id | name +-- 2 | t 4 | tr 44 | tg 48 | th 50 | tgh (5 rows) envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 0 envisity=# select * from foo; id | name +-- 1 | t 2 | tr 22 | tg 24 | th 25 | tgh (5 rows) envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 0 -- Here it doesn't work. Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] oid's in views.
I apologize for not expressing myself clearly, and for replying without really giving any new information. In hindsight I understand perfectly well, why you did not want to continue communication, but I hope that you or someone else, will give me a second chance on the issue. Let me try to explain what I really meant: Let's say you are trying to make a generic database maintenance system. One of the tasks of this system is to update individual rows. For various reasons you may want to deal with views and not only tables. You may also want these views to be updated on and inserted on. I completely agree with you that using OID as a primary key as in PRIMARY KEY(oid), and then REFERENCES etc, or generally storing the oid anywhere in tables. However if you are simply trying to update it is very usefull - especially with views as you have no way of determining what the primary key of the table is! Therefore I would suggest that one could assign the field oid in a view, (but that it still remains hidden from *) such that these kinds of issues are simpler. I have solved the problem in my case otherwise, and I totally agree that it is not important - it's just very usefull, in this and a few other cases. Regards, Aasmund. On Sat, 20 Oct 2001 12:58:09 -0700, "Josh Berkus" <[EMAIL PROTECTED]> wrote: > Aasmund, > > > This is my last e-mail, as you are not listening to my advice. > > *Do NOT use OIDs as your primary key for any table!* If you persist in > doing so, the problems you encounter are your own fault, and you will > get no help with them from the list or the developers. > > -Josh > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > > Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] GROUPING
On Sat, 13 Oct 2001, Timothy J Hitchens wrote: > It's been a while since I used postgresql but today I have converted one > of my web apps but with one small problem. I goto do a group as > designed and executed in mysql and I get told that this and this must be > part of the aggreate etc I am puzzled and wonder if someone could bring > me up to stratch with grouping in postgresql this is my current sql: > > SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; > You should write: SELECT name_1 FROM telemetry where beat > 12 GROUP BY name_1 because, you must indicate on "SELECT name_1" the attribute name for GROUP it . did you understand? bye Esteban Gutierrez. [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Query On Case structure
Hi All, Can we create a query to find different values based on different criteria from a table using case structure. For example Select Into DBComment Case When localComment Is Null Then Comment When localComment = '123' Then 'Numeric Comment' Else 'String Comment' End From PDBUser Where UserIndex = 23; Using such type of structure can we evaluate multiple values. e.g Select DBComment , DBName Case When localComment Is Null Then Comment When localComment = '123' Then 'Numeric Comment' Else 'String Comment' End, Case When localName Is Null Then UserName When localName = 'SuperUser' Then 'Supervisor' Else 'NormalUser' End From PDBUser Where UserIndex = 23; Is this possible. If yes then How is it. Any help/suggestion may be benificial. Regards Dinesh Parikh NSTL New Delhi
Re: [SQL] CREATE RULE ON UPDATE/DELETE
On Sat, 20 Oct 2001, Aasmund Midttun Godal wrote: > Can a rule see the where statement in a query which it has been > triggered by? or is it simply ignored?? what happens? > Looking over your question, I wanted to clarify the problem a bit, so: (cleaned up example a bit from Aasmund) -- set up tables drop view normal; drop view dbl; drop table raw; CREATE TABLE raw (id INT PRIMARY KEY, name TEXT ); INSERT INTO raw VALUES(1, 'a'); INSERT INTO raw VALUES(2, 'b'); INSERT INTO raw VALUES(12, 'c'); INSERT INTO raw VALUES(15, 'd'); INSERT INTO raw VALUES(14, 'e'); -- set up two views: "normal", a simple view, -- and "dbl", which shows id * 2 -- create basic rules to allow update to both views CREATE VIEW normal AS SELECT * FROM raw; CREATE RULE normal_update AS ON UPDATE TO normal DO INSTEAD UPDATE raw SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw; CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET id = NEW.id, name = NEW.name WHERE OLD.id = id; -- now test this UPDATE normal SET id = id + 10 where id > 10; -- works fine UPDATE dbl SET id = id + 10 where id > 10;-- above shows UPDATE 0 -- even though there are ids > 10 UPDATE dbl SET id = id + 10; -- UPDATE 1; shows table SELECT * FROM dbl;-- inconsistencies: two "a"s SELECT * FROM raw; The issue is that there are no IDs over 10 that have another ID that is exactly their value, so the first update to "dbl" does nothing. The second time, w/o the ID>10 restriction, it finds 1(a), and double that, 2(b), and adds 10; getting confused about which record to edit. Is this the best way to interpret this? Is this a bug? -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
