On Thu, Sep 22, 2011 at 5:28 AM, Rafal Pietrak <ra...@zorro.isa-geek.com> wrote: > Hi All, > > I have this function: > CREATE FUNCTION mypass(newpass text) returns text .... > EXECUTE 'ALTER USER ' || quote_ident(session_user) || ' PASSWORD ' || > quote_literal(newpass); return session_user::text; > > to varify user passwords before allowing a change. > > I've put that function in a RULE that some housekeeping, like updating > user state (last pass change, etc): > > CREATE RULE pass AS ON UPDATE TO myself WHERE old.pass <> new.pass DO > INSTEAD UPDATE people SET .... WHERE username=mypass(new.username) > > but I get: > ERROR: ALTER ROLE is not allowed in a non-volatile function > > Why??? > > 1. The function is "obviously STABLE", since it's outcome will not > change enything in datatables (I think) - and I can arrange for its > output being stable within a transaction (if I don't do SET > AUTHORIZATION within the transation, right?). > 2. for the purpose I need, the function could/should be "computted > once", and result used "meny times" (for filtering PEOPLE rows). Having > it get evaluated for every row is a signifficant unnecesary cost > panelty.
if you change the state of the database, including (and especially) system catalogs, your function is volatile, period. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
