CREATE USER within function

Jan, would you take a look at this? It is reproducable. I see the
query sending "CREATE USER $1 ..." in the server logs.

---------------------------------------------------------------------------

Michal Taborsky wrote:

Hi,

I am facing a problem. I am unable to call CREATE USER from within a
PL/PgSQL function. I am using Postgres 7.3.2.

The function looks like this (it is more complex in fact, but even if
stripped to this, it does not work anyway):

CREATE OR REPLACE FUNCTION
usr_createAccount(integer, name, text, text, bool, bool)
RETURNS bool AS '
BEGIN
CREATE USER $2 WITH PASSWORD $3 NOCREATEDB CREATEUSER IN GROUP "all",
"super";
RETURN true;
END;
' LANGUAGE 'plpgsql';

I keep getting this:

akcent=# select usr_createAccount(3, 'username', 'somepassword', 'cs',
true, false);
WARNING: Error occurred while executing PL/pgSQL function usr_createaccount
WARNING: line 2 at SQL statement
ERROR: parser: parse error at or near "$1" at character 14

I am a bit confused by this error, because there is no "$1" string in
this function. I tried to do a CREATE USER in transaction, because I
suspected it to be the problem, but it worked just fine. Does anyone see
something which I don't ? What am I doing wrong ?

Thanks,
Michal

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jan, would you take a look at this? It is reproducable. I see the
query sending "CREATE USER $1 ..." in the server logs.

Hardly requires much looking: utility statements don't take parameter
symbols, only literal constants. The only way to do this from plpgsql
at the moment is with EXECUTE.

regards, tom lane

Jan Wieck wrote:

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jan, would you take a look at this? It is reproducable. I see the
query sending "CREATE USER $1 ..." in the server logs.

Hardly requires much looking: utility statements don't take parameter
symbols, only literal constants. The only way to do this from plpgsql
at the moment is with EXECUTE.

We should add something to that extent to the FAQ :-)

Can we improve the error message reported to the user?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073