Re: Create user or role from inside a function?

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
';

Executing this function yields:

# SELECT user_create('bob',1234,'bobspassword');
ERROR: column "CREATE USER " does not exist
CONTEXT: SQL statement "SELECT "CREATE USER " || $1 || " WITH PASSWORD
" || $2 "
PL/pgSQL function "user_create" line 2 at execute statement

Directly executing CREATE USER in this function also fails.. CREATE USER
$1 appears to be what is being executed.

Any tips would be appreciated.

Cheers,

Dan

On Fri, Sep 01, 2006 at 08:13:14PM +1000, Dan wrote:
> 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.

Does it make a difference if you use single quotes rather than double
(taking into account the need to escape then). They mean somewhat
different things...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Dan <ml(at)mutox(dot)org> schrieb:

> 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
> ';

This works:

CREATE or replace function user_create (un varchar, uid bigint, pw varchar) RETURNS VARCHAR AS $$
BEGIN
EXECUTE 'create user ' || un || ' with password ' || quote_literal(pw);
return $1;
end;
$$ language plpgsql;

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

On Fri, Sep 01, 2006 at 08:13:14PM +1000, Dan wrote:

> I am running PostgreSQL 8.1.4 and I want to create a user from inside a
> function. Is this possible in 8.1?
...
> I have a function like this:
...
> Executing this function yields:
>
> # SELECT user_create('bob',1234,'bobspassword');
> ERROR: column "CREATE USER " does not exist
> CONTEXT: SQL statement "SELECT "CREATE USER " || $1 || " WITH PASSWORD
> " || $2 "
> PL/pgSQL function "user_create" line 2 at execute statement
...
> Any tips would be appreciated.

http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/gmCreateUserFunction.sql?rev=1.5&root=gnumed&view=markup

This might help.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

thanks!

I actually came to a similar solution after Roman's post.

Thanks all for the replies!

> Dan <ml(at)mutox(dot)org> schrieb:
>
>> 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
>> ';
>
> This works:
>
>
> CREATE or replace function user_create (un varchar, uid bigint, pw
> varchar) RETURNS VARCHAR AS $$
> BEGIN
> EXECUTE 'create user ' || un || ' with password ' ||
> quote_literal(pw);
> return $1;
> end;
> $$ language plpgsql;
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknow)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

(Sending to the list as I accidently only replied to Roman with my earlier
reply)

Thanks for your assistance. This was a case of user error. To me the
examples I looked at used the double quote (") but on further inspection
they do indeed use double single quotes (').

The quote_literal function was also handy for this.

Thanks again!

Regards,

Dan

> 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:

# 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