From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Michal Taborsky <michal(at)taborsky(dot)cz> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: CREATE USER from within function |
Date: | 2003-04-09 14:56:14 |
Message-ID: | 20030409075416.S65184-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 9 Apr 2003, Michal Taborsky wrote:
> 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 ?
I think it's getting confused by USER which is also a reserved word to
return the current user. You can use EXECUTE to do this however (as a
note, you'll also need single quotes around the password afaict).
Something like:
CREATE OR REPLACE FUNCTION
usr_createAccount(integer, name, text, text, bool, bool)
RETURNS bool AS '
BEGIN
EXECUTE ''CREATE USER '' || $2 || '' WITH PASSWORD '''''' || $3 || ''''''
NOCREATEDB CREATEUSER IN GROUP "all", "super";'';
return true;
end;' language 'plpgsql';
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-04-09 14:58:23 | Re: multiple fields index |
Previous Message | Michal Taborsky | 2003-04-09 14:43:43 | CREATE USER from within function |