Обсуждение: exception handling in plpgsql

Поиск
Список
Период
Сортировка

exception handling in plpgsql

От
Roberto Assis
Дата:
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.

Re: exception handling in plpgsql

От
Michael Fuhr
Дата:
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