Thread: Problem with function-arguments

Hello everyone!

First of all: I'm using Postgres 8.2.4 on a WinXP machine.

I'm trying to create a function that creates a new user-role to allow
non-super-users to create users.

The function should look something like that (well, obviously it should
not...but it's to give you an idea, what I'm talking about):

CREATE OR REPLACE FUNCTION sys.create_user(text, text)
   RETURNS void AS
$BODY$
    CREATE ROLE "$1" LOGIN PASSWORD '$2' INHERIT NOCREATEDB CREATEROLE;
$BODY$
   LANGUAGE 'sql' VOLATILE SECURITY DEFINER;

The statement "SELECT sys.create_user('myusername', 'mypassword'); "
creates a user named $1 with password $2.......well....that's not whwat
I want the function to do.....

Nearly any combination of double-quotes, single-quotes leads to a
syntax-error when trying to create the function...

At the moment I'm running out of ideas...can anyone help?

Greetings and TIA,
Hans Peter Ertz

Hello again!

Problem solved!

I switched my language to plpgsql and used the execute-command. The
function now looks like:

CREATE OR REPLACE FUNCTION sys.create_user(character varying, character
varying)
   RETURNS void AS
$BODY$
BEGIN
    execute 'CREATE USER ' || quote_ident($1) || ' PASSWORD ' ||
quote_literal($2) || ' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE';
END;
$BODY$
   LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

Nevertheless - thanks for thinking ;-),
Hans Peter Ertz