Re: Create user or role from inside a function?

# ml(at)mutox(dot)org / 2006-09-01 20:13:14 +1000:
> Hey,
>
> I am running PostgreSQL 8.1.4 and I want to create a user from inside a
> function. Is this possible in 8.1?
>
> Ive found quite a few references on google using EXECUTE, but this seems
> relevant to earlier versions, not 8.1.
>
> I have a function like this:
>
> CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS
> VARCHAR LANGUAGE plpgsql AS '
> BEGIN
> EXECUTE "CREATE USER " || un || " WITH PASSWORD " || pw;
>
> RETURN un;
> END
> ';

Double quotes can be used only for identifiers (table, column
names, etc), you want to use single quotes for strings. Now, since
you already are in a string (the function body), you need to escape
the single quotes inside. In SQL this is done by doubling the quote
character, IOW, by putting another single quote just before it:

CREATE FUNCTION foo() ...
AS '
BEGIN
EXECUTE ''CREATE USER '' || un || '' WITH PASSWORD '' || pw;
RETURN un;
END
';

That assumes that the un and pw parameters are always passed already
quoted, otherwise you'll get errors like this:

test=# CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS
test-# VARCHAR LANGUAGE plpgsql AS '
test'# BEGIN
test'# EXECUTE ''CREATE USER '' || un || '' WITH PASSWORD '' || pw;
test'# RETURN un;
test'# END
test'# ';
CREATE FUNCTION
test=# select user_create('fubar', 0, 'pass');
ERROR: syntax error at or near "pass" at character 33
QUERY: CREATE USER fubar WITH PASSWORD pass
CONTEXT: PL/pgSQL function "user_create" line 2 at execute statement
LINE 1: CREATE USER fubar WITH PASSWORD pass
^
test=#

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991