Create user or role from inside a function?

From: "Dan" <ml(at)mutox(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Create user or role from inside a function?
Date: 2006-09-01 10:13:14
Message-ID: 39844.203.143.226.132.1157105594.squirrel@secure.x32i.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey,

I am running PostgreSQL 8.1.4 and I want to create a user from inside a
function. Is this possible in 8.1?

Ive found quite a few references on google using EXECUTE, but this seems
relevant to earlier versions, not 8.1.

I have a function like this:

CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS
VARCHAR LANGUAGE plpgsql AS '
BEGIN
EXECUTE "CREATE USER " || un || " WITH PASSWORD " || pw;

RETURN un;
END
';

Executing this function yields:

# SELECT user_create('bob',1234,'bobspassword');
ERROR: column "CREATE USER " does not exist
CONTEXT: SQL statement "SELECT "CREATE USER " || $1 || " WITH PASSWORD
" || $2 "
PL/pgSQL function "user_create" line 2 at execute statement

Directly executing CREATE USER in this function also fails.. CREATE USER
$1 appears to be what is being executed.

Any tips would be appreciated.

Cheers,

Dan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stefan Kaltenbrunner 2006-09-01 10:40:53 Re: [pgsql-advocacy] Thought provoking piece on
Previous Message nhrcommu 2006-09-01 10:03:09 Training (from Thought provoking...)