# ml@xxxxxxxxx / 2006-09-01 20:13:14 +1000: > 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 > '; Double quotes can be used only for identifiers (table, column names, etc), you want to use single quotes for strings. Now, since you already are in a string (the function body), you need to escape the single quotes inside. In SQL this is done by doubling the quote character, IOW, by putting another single quote just before it: CREATE FUNCTION foo() ... AS ' BEGIN EXECUTE ''CREATE USER '' || un || '' WITH PASSWORD '' || pw; RETURN un; END '; That assumes that the un and pw parameters are always passed already quoted, otherwise you'll get errors like this: test=# CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS test-# VARCHAR LANGUAGE plpgsql AS ' test'# BEGIN test'# EXECUTE ''CREATE USER '' || un || '' WITH PASSWORD '' || pw; test'# RETURN un; test'# END test'# '; CREATE FUNCTION test=# select user_create('fubar', 0, 'pass'); ERROR: syntax error at or near "pass" at character 33 QUERY: CREATE USER fubar WITH PASSWORD pass CONTEXT: PL/pgSQL function "user_create" line 2 at execute statement LINE 1: CREATE USER fubar WITH PASSWORD pass ^ test=# -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991