And dynamic SQL leads easily to SQL injection so quoting is required there.
execute 'create user ' || quote_ident(i_username) || ' password ' || quote_literal(i_password); On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman <iney...@perceptron.com> wrote: > > > > -----Original Message----- > > From: Tatarnikov Alexander [mailto:cank...@gmail.com] > > Sent: Wednesday, September 15, 2010 12:05 AM > > To: pgsql-sql@postgresql.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 > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >