Re: Use "CREATE USER" in plpgsql function

> -----Original Message-----
> From: Tatarnikov Alexander [mailto:cankrus(at)gmail(dot)com]
> Sent: Wednesday, September 15, 2010 12:05 AM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Use "CREATE USER" in plpgsql function
>
> Hello!
>
> I have function wich check user credentials and if test
> passed function must create new user with generated username
> and password.
>
> Language is plpgsql.
>
> For example:
>
> ....
> DECLARE creds RECORD;
> ...
> SELECT * INTO creds FROM ...
>
> creds is Record with fields userName(VARCHAR) and
> userPassword(VARCHAR)
>
> so when i use CREATE USER creds."userName" WITH PASSWORD
> creds."userPassword"
>
> i get an error, because creds."userName" is VARCHAR and thus
> when function runs it will be look like this:
> CREATE USER 'user_1'
> but right command is
> "CREATE USER user_1" OR " CREATE USER "user_1" "
>
> so question is how to "unembrace" this parameter (i mean
> creds."userName")?
>
> Thanks
> --
> ------
> Alexander
>

It is called "dynamic" sql:

EXECUTE 'CREATE USER creds.' || userName || ' WITH PASSWORD creds.' ||
userPassword;

Read about "dynamic" sql in PG docs:

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html

Specifically: "38.5.4. Executing Dynamic Commands"

Regards,
Igor Neyman