By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,956 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,956 IT Pros & Developers. It's quick & easy.

Problem in function with CREATE ROLE with parameters

P: 2
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!
Apr 7 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 534
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)

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. CREATE or replace FUNCTION new_user (text, text) RETURNS text AS '
  4. BEGIN
  5.   execute ''CREATE USER '' || quote_ident($1) || '' PASSWORD '' || quote_literal($2);
  6.   return  ''Created user '' || $1 || '' with password '' || $2;
  7. END;
  8. ' language plpgsql;
  9.  
  10.  
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:
Expand|Select|Wrap|Line Numbers
  1. testdb=# select new_user('simon', 'rio25');
  2.                 new_user                
  3. ----------------------------------------
  4.  Created user simon with password rio25
  5. (1 row)
  6.  
  7.  
You can check whether plpgsql is available on your database with this query:
Expand|Select|Wrap|Line Numbers
  1. testdb=# select lanname from pg_language;
  2.  lanname  
  3. ----------
  4.  plpgsql
  5.  sql
  6.  internal
  7.  c
  8. (4 rows)
  9.  
If it's not there you need to install it, see createlang
and CREATE LANGUAGE for details
Apr 7 '07 #2

P: 2
Hi Michaelb,

thanks for your welcome and your very fast response.

I used it and it worked well!

Thank you very much!
Apr 7 '07 #3

Expert 100+
P: 534
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!
Apr 8 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.