Re: STABLE functions

Rafal Pietrak  writes:
> 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;

You should use the SECURITY DEFINER property instead of explicitly
fooling with role settings.  It's easy to think of cases where that
RESET will select the *wrong* setting leading to a security hole.

Also, if it has side effects, it IS NOT STABLE.  Period.  So you can't
mark anything doing DROP USER as stable.

> 1. I used the STABLE keyword to tell executor to evaluate the function
> just once per statement.

Wrong.  STABLE is not a directive to the system, it is a promise about
the behavior of your function ... and you're trying to break the
promise.

Your best bet for this is probably to put the DROP in an AFTER DELETE
trigger on the my_users table, instead of trying to use a rule.

> 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?

We don't support triggers on system catalogs.

			regards, tom lane