Re: Create user or role from inside a function?

# ml@mutox.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