Re: Execute

[valgog <valgog@xxxxxxxxx>]

On Feb 5, 9:11 am, "Дикий неадекватный кальмар"
<dima.petchon...@xxxxxxxxx> 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');

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings