Problem in function with CREATE ROLE with parameters

[jsmith31]

hi to everyone,
I tried to create a function that includes a role creation with the rolename and password as parameters to be called by other function but the postgresql raises an error like that:

ERROR: syntax error at or near "$1"
SQL state: 42601
Character: 81

The function definition is:
CREATE FUNCTION new_user (text,text) RETURNS integer AS $$
CREATE ROLE $1 PASSWORD $2;
$$ language sql;

I'm using the right syntax?
I tried also with pl/pgSQL giving names to the parameters like myname and mypassword with the same results. I'm using PostgreSQL 8.2 with winxp.
Please Help!!
Thanks in advance and happy Easter!

[michaelb]

Welcome to the Forum!

SQL state 42601 indicates a syntax error.

You may want to review the release notes, but from what I remember in the previous versions Postgresql utility functions (CREATE USER is one of them) did not take parameter symbols, meaning you have to pass literal constants to CREATE USER.

You can probably verify this by hard-coding name and password inside the function, and you may also need to change the return type to VOID - your code does not seem to return anything.

You can overcome this restriction by using EXECUTE from plpgsql function
as shown here (you can modify the return type as you need)

 
 
CREATE or replace FUNCTION new_user (text, text) RETURNS text AS '
BEGIN
  execute ''CREATE USER '' || quote_ident($1) || '' PASSWORD '' || quote_literal($2);
  return  ''Created user '' || $1 || '' with password '' || $2;
END;
' language plpgsql;
 
 

note that i'm using the old-style syntax, but with your database you can/should use $$ as you did in your code.

give it a test run:

testdb=# select new_user('simon', 'rio25');
                new_user                
----------------------------------------
 Created user simon with password rio25
(1 row)
 
 

You can check whether plpgsql is available on your database with this query:

testdb=# select lanname from pg_language;
 lanname  
----------
 plpgsql
 sql
 internal
 c
(4 rows)
 

If it's not there you need to install it, see createlang
and CREATE LANGUAGE for details

[jsmith31]

Hi Michaelb,

thanks for your welcome and your very fast response.

I used it and it worked well!

Thank you very much!

[michaelb]

Hi Michaelb,

thanks for your welcome and your very fast response.
I used it and it worked well!
Thank you very much!

You're very welcome!