Hi All, May be someone can help me with the following problem: 1. I need to extend 'featurs' of database user account. 2. I did that by creating a table: CREATE TABLE users (username text, -- key matching 'current_user' freaturs text -- thing I need ); 3. I allow acces to that table through VIEWs: CREATE VIEW my_users AS SELECT * FROM users WHERE .... 4. one of the 'featurs' I need is a 'controlled' addition/deletion of database uses - by 'controlled' I mean "with a little help from stored procedures" 5. one of the procedures is: CREATE FUNCTION kill(text) RETURNS boolean as $$ SET ROLE MASTER_OF_THE_UNIVERSE; EXECUTE 'DROP USER ' || quote_ident($1); reset role; return true; END $$ LANGUAGE plpgsql STABLE; 6. which is used within: CREATE RULE goaway AS ON DELETE TO my_users DO INSTEAD DELETE .... WHEN kill(old.username) = true AND ....; Now, the problem is: ----------------an attempt execution------------- ERROR: SET is not allowed in a non-volatile function CONTEXT: SQL statement "set role MASTER_OF_THE_UNIVERSE" PL/pgSQL function kill line 1 at execute statement ----------------------------------------------- I've defined the function as STABLE, since it's *meant* to be called just once-per-statement (depends just on it's argument, not on any data within AND clause that follows not on any other actions happening within the statement). In other words, I wouldn't like this function to be called again and again (as with NONE-STABLE functions), for every row to be deleted. Just once per statement to retrieve the value it would have for this statement, which depends solely on "old.username" which is supposed to remain stable throuout the statement. So I fell into 'semantical ambiquity case': 1. I used the STABLE keyword to tell executor to evaluate the function just once per statement. 2. while it looks, that the STABLE keyword is there, to tell the executor, that 'whatever it does' - the outcome of the function remains the same within a statement. Which is not exactly the same meaning. Any one knows which one is 'according to standard'? But. Is there any implementable solution to my design? BTW: signifficant part of my stored procedures code is there for the sole reason, that manipulation of USERS (addition/deletion/etc) is not accessible for a member of priviledged groups.... until the role is set explicitly (as in the case of KILL function above). May be there is a configuration switch to change this default? BTW-2: My design would be much easier if only I counld: CREATE TABLE users( id int references pg_authid(oid) on delete cascade, ..... ); Which I cannot, apparently. Are there technical reasons for this restriction? -- -R
