Re: change user password

> hi, is it possible to change the current user's password from a
> function/stored procedure , I mean, is there a system function/stored
> procedure to do it? like the dbo.sp_password found in adaptive server
> anywhere.
>

CREATE OR REPLACE FUNCTION public.alter_password(name, name)
RETURNS "varchar" AS
'
DECLARE
l_user ALIAS FOR $1;
l_pwd ALIAS FOR $2;
CMD VARCHAR;
BEGIN
CMD := \'ALTER USER "\' || l_user || \'" WITH ENCRYPTED PASSWORD \' ||
\'\'\'\' || l_pwd || \'\'\'\' || \' VALID UNTIL \' || \'\'\'\' ||
CURRENT_DATE+INTERVAL \'30 days\' || \'\'\'\';
EXECUTE CMD;
RETURN \'ALTER USER\';
END;
'
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

-- Of course, you'ld want to be careful about whom was granted execute
permission on this.

-- BMT