Postgres function using DDL statement

Question

I am trying to create function in Postgres which can automate user creation process but it doesn;t accept parameter in DDL statement.

CREATE OR REPLACE FUNCTION AUTOUSER (uname varchar(20))
RETURNS TEXT AS $$
DECLARE 
      nm varchar(20);
BEGIN
    nm=$1;
    CREATE USER nm WITH PASSWORD 'Iash12';
    GRANT ALL ON DATABASE iashdb TO nm;
    GRANT ALL ON  ALL TABLES IN SCHEMA public TO nm;
    RETURN CONCAT(nm,' Created');
END;
$$ 
LANGUAGE plpgsql;

Above function create user as 'nm' instead of passed parameter name however RETURN statement showing correct result. Thanks in advance,

You need dynamic SQL for this: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Apr 26, 2016 at 21:40
Dynamic SQL doesn't support DDL statement, it only support, SELECT, UPDATE, INSERT and DELETE. — Nik
– Nik, Commented Apr 27, 2016 at 14:07
Of course it supports DDL. Where in the manual did you find that statement? — user330315
– user330315, Commented Apr 27, 2016 at 14:36
EXECUTE 'CREATE USER $1 WITH PASSWORD 'Iash12' '; shows me syntax error: syntax error at or near "' '"; tried with couple of different syntax but no luck so far. — Nik
– Nik, Commented Apr 27, 2016 at 14:58
You cant use it like this because: "Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values." — Sergey
– Sergey, Commented Jan 20, 2021 at 10:45

user330315 · Accepted Answer · 2016-04-27 15:17:41Z

4

You need to use dynamic SQL and you need to quote the parameters properly. The easiest way is to use the format() function with the appropriate placeholders:

CREATE OR REPLACE FUNCTION AUTOUSER (uname varchar(20))
RETURNS TEXT AS $$
BEGIN
    execute format('CREATE USER %I WITH PASSWORD %L', uname, 'Iash12');
    execute format('GRANT ALL ON DATABASE iashdb TO %I', uname);
    execute format('GRANT ALL ON  ALL TABLES IN SCHEMA public TO %I', uname);
    RETURN CONCAT(uname,' Created');
END;
$$ 
LANGUAGE plpgsql;

The placeholder %I properly quotes SQL identifiers. The placeholder %L properly deals with string literals.

answered Apr 27, 2016 at 15:17

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres function using DDL statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related