Re: Execute

On Feb 5, 9:11 am, "Дикий неадекватный кальмар"
<dima(dot)petchon(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> is there a way to catch error returned by EXECUTE statement?
>
> declare stmt character varying;
> begin
> stmt=crate user||'some_name'||' with password '''||'somepassword';
> execute stmt;
>
> end;
>
> here i need to know, actually to handle some errors, like user already
> exists, or not enough privileges for creating users.

CREATE OR REPLACE FUNCTION test_exec(p_username text, p_password text)
RETURNS text AS
$BODY$declare
stmt character varying;
begin
stmt = $S$create user $S$ || p_username || $S$ with password '$S$ ||
p_password || $S$'$S$;
execute stmt;
return 'OK';
exception
when others then
raise WARNING 'Exception in % %', SQLSTATE, SQLERRM;
return SQLSTATE || ': ' || SQLERRM;
end; $BODY$
LANGUAGE 'plpgsql' VOLATILE;

select test_exec('neadekwatnzj', 'kaljmar');

select test_exec('neadekwatnzj', 'kaljmar');

select test_exec('neadekwatnzj', 'kaljmar');