[SQL] View with Union and update rule - fails
Hi, I have trouble with following VIEW and RULE: CREATE SCHEMA test AUTHORIZATION postgres; CREATE TABLE test.table1 ( uid int4, col1 int4, col2 int4, col3 int4 ); CREATE TABLE test.table2 ( col4 int4 ) INHERITS (test.table1); CREATE TABLE test.table3 ( col5 int4 ) INHERITS (test.table1); CREATE VIEW test.view1 AS SELECT uid, col1,col2,col3 FROM test.table2 UNION SELECT uid, col1,col2,col3 FROM test.table3; INSERT INTO test.table1 VALUES (1,1,1,1); INSERT INTO test.table1 VALUES (2,2,2,2); INSERT INTO test.table1 VALUES (3,3,3,3); INSERT INTO test.table1 VALUES (4,4,4,4); INSERT INTO test.table2 VALUES (5,5,5,5,5); INSERT INTO test.table2 VALUES (6,6,6,6,6); INSERT INTO test.table2 VALUES (7,7,7,7,7); INSERT INTO test.table2 VALUES (8,8,8,8,8); INSERT INTO test.table3 VALUES (9,9,9,9,9); INSERT INTO test.table3 VALUES (10,10,10,10,10); INSERT INTO test.table3 VALUES (11,11,11,11,11); INSERT INTO test.table3 VALUES (12,12,12,12,12); CREATE OR REPLACE RULE r1 AS ON UPDATE TO test.view1 DO INSTEAD UPDATE test.table1 SET col1 = 100; UPDATE test.view1 SET col2 = 101 WHERE uid=1; I get: ERROR: unrecognized node type: 651 But UPDATE test.view1 SET col2 = 101; (without WHERE) works fine. View without UNION works also fine. Is there something wrong in my code, or is it some feature/bug? PostgreSQL is 7.4.5 running on Gentoo Linux. Thanks, Filip Jirsák ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] simulating row ownership
I have a table where I want everyone to be able to be able to insert and select. But they should only be able to update and delete rows that they “own”. The table has a column indicating the owner. What is the best way to accomplish this? I’m not real familiar with rules, but it seems that I can do this with rules for update and delete applied to the table. Someone had suggesting using views, but since I can’t update a view in postgres, I’m not sure that views help here. I assume if I use rules, then I need to grant all to public, and let the rules prevent users from updating the wrong rows? Any advice is appreciated.
Re: [SQL] View with Union and update rule - fails
=?ISO-8859-2?Q?Filip_Jirs=E1k?= <[EMAIL PROTECTED]> writes: > I have trouble with following VIEW and RULE: > ... > I get: > ERROR: unrecognized node type: 651 I can replicate this failure in 7.4.6 but not in 8.0. I believe it's fixed by the change discussed here: http://archives.postgresql.org/pgsql-general/2004-10/msg00069.php regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] simulating row ownership
On Fri, Jan 07, 2005 at 11:52:07AM -0500, Rick Schumeyer wrote: > I have a table where I want everyone to be able to be able to insert and > select. > > But they should only be able to update and delete rows that they "own". The > table has a column indicating the owner. What does the owner column refer to? A database user? If so, then you could use a trigger that checks CURRENT_USER or SESSION_USER and raises an exception if the user doesn't have permission to update or delete the affected row. In the PostgreSQL documentation, see the "Triggers" chapter and the "Trigger Procedures" section of the "PL/pgSQL - SQL Procedural Language" chapter. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
