Re: Use "CREATE USER" in plpgsql function

2010/9/15 Tatarnikov Alexander <cankrus(at)gmail(dot)com>:
> Thanks for response!
>
> Here is function
>
>             CREATE USER creds."userName" WITH PASSWORD creds."userPassword"
> IN GROUP ta_users; - there is error occured

You can not use variables for non-data entities. Use the dynamic SQL instead:

EXECUTE 'CREATE USER ' || creds."userName" ||
' WITH PASSWORD ' || creds."userPassword" ||
' IN GROUP ' || ta_users;

Read more here http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

p.s. I suggest you to read it with care
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems before
reporting a problem next time.

>
> DECLARE cred VARCHAR;
> DECLARE passed BOOLEAN;
> DECLARE creds RECORD;
> BEGIN
>         SELECT  (ta_base.user_accounts."password" = $2) INTO passed
>         FROM    ta_base.user_accounts
>         WHERE   ta_base.user_accounts.user_id = $1;
>         if (passed) THEN
>             SELECT * INTO creds FROM "ta_base"."credTable" WHERE
> "ta_base"."credTable"."inUse"=FALSE ORDER BY random() LIMIT 1;
>             INSERT INTO ta_base.logins VALUES (creds."userName",
> creds."userPassword", current_timestamp(2), NULL, NULL, $1, TRUE);
>             UPDATE "ta_base"."credTable" SET "inUse"=TRUE WHERE
> "credId"=creds."credId";
>             CREATE USER creds."userName" WITH PASSWORD creds."userPassword"
> IN GROUP ta_users; - there is error occured
>             cred:=N'pass';
>         else
>             cred:=N'failed';
>         end if;
>         return cred;
> END;
>
> 2010/9/15 Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
>>
>> Hi,
>>
>> On 15 September 2010 08:05, Tatarnikov Alexander <cankrus(at)gmail(dot)com>
>> wrote:
>> > 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.
>> >
>> > so question is how to "unembrace" this parameter (i mean
>> > creds."userName")?
>>
>> Show the whole function please.
>>
>> >
>> > Thanks
>> > --
>> > ------
>> > Alexander
>> >
>>
>>
>>
>> --
>> Sergey Konoplev
>>
>> Blog: http://gray-hemp.blogspot.com /
>> Linkedin: http://ru.linkedin.com/in/grayhemp /
>> JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802
>
>
>
> --
> ------
> С уважением,
> Татарников Александр
>

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802