using 'ALTER ROLE' in a function

From: Lutz Broedel <lb(at)fggm(dot)uni-hannover(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: using 'ALTER ROLE' in a function
Date: 2007-03-22 13:40:33
Message-ID: 46028751.2080807@fggm.uni-hannover.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear list,

I'm trying to write a PL/pgSQL function that executes some code every
time a user changes his/her password. The function is supposed to work
like this:

CREATE OR REPLACE FUNCTION changePwd(varchar(255)) RETURNS boolean AS $$
DECLARE
pwd varchar(255);
curr_user name;
BEGIN
curr_user = (SELECT session_user);
pwd = $2;

ALTER ROLE curr_user WITH ENCRYPTED PASSWORD pwd;
-- [... do some other stuff ...]
RETURN true;
END;
$$ LANGUAGE 'PLPGSQL';

but PostgreSQL returns:
ERROR: Error »syntax error« at »$1« at character 13
QUERY: ALTER ROLE $1 WITH ENCRYPTED PASSWORD $2

I have tried this in several ways and it seems, ALTER ROLE just does not
accept a parameter instead of name. Does anybody have a solution for this?

Thanks in advance and best regards,
Lutz

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2007-03-22 13:47:32 Re: to_tsvector in 8.2.3
Previous Message Alvaro Herrera 2007-03-22 13:40:10 Re: Dealing with table names in functions