Thread: exception handling in plpgsql
Hello folks, supose I already have an user called bob, with this function below I was expecting to see something like this:
-------------------------------------------------------------------------------
SELECT my_create_user('bob','bobpass');
ERROR: <Some string like XXXXX>:<Some text>
-------------------------------------------------------------------------------
If I try to make another user bob.
But instead the function doesn't compile.
Reading the manual it seemed that this should work.
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION my_create_user(text,text)
RETURNS int AS $$
DECLARE
login ALIAS FOR $1;
passwd ALIAS FOR $2;
answer int;
BEGIN
BEGIN
EXECUTE 'CREATE USER '||login||' NOCREATEUSER NOCREATEDB ENCRYPTED PASSWORD '||QUOTE_LITERAL(passwd);
EXCEPTION
WHEN OTHERS THEN RAISE EXCEPTION 'ERROR: % : %.',SQLSTATE,SQLERRM;
END;
RETURN answer;
END;
$$ LANGUAGE plpgsql;
-------------------------------------------------------------------------------
Navegue com o Yahoo! Acesso Grátis, assista aos jogos do Brasil na Copa e ganhe prêmios de hora em hora.
-------------------------------------------------------------------------------
SELECT my_create_user('bob','bobpass');
ERROR: <Some string like XXXXX>:<Some text>
-------------------------------------------------------------------------------
If I try to make another user bob.
But instead the function doesn't compile.
Reading the manual it seemed that this should work.
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION my_create_user(text,text)
RETURNS int AS $$
DECLARE
login ALIAS FOR $1;
passwd ALIAS FOR $2;
answer int;
BEGIN
BEGIN
EXECUTE 'CREATE USER '||login||' NOCREATEUSER NOCREATEDB ENCRYPTED PASSWORD '||QUOTE_LITERAL(passwd);
EXCEPTION
WHEN OTHERS THEN RAISE EXCEPTION 'ERROR: % : %.',SQLSTATE,SQLERRM;
END;
RETURN answer;
END;
$$ LANGUAGE plpgsql;
-------------------------------------------------------------------------------
Navegue com o Yahoo! Acesso Grátis, assista aos jogos do Brasil na Copa e ganhe prêmios de hora em hora.
On Thu, May 25, 2006 at 10:25:37PM +0000, Roberto Assis wrote:
> Hello folks, supose I already have an user called bob, with this
> function below I was expecting to see something like this:
> -------------------------------------------------------------------------------
> SELECT my_create_user('bob','bobpass');
> ERROR: <Some string like XXXXX>:<Some text>
> -------------------------------------------------------------------------------
> If I try to make another user bob.
> But instead the function doesn't compile.
> Reading the manual it seemed that this should work.
What's the exact error you get? What version of PostgreSQL are you
using? SQLSTATE and SQLERRM are available only since 8.1.
--
Michael Fuhr