CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sqlpostgresql-9.3
Pavel V.
Pavel V.

Reputation: 2790

USING section ignored in EXECUTE...USING in PL/pgSQL

I try to make a sequence dynamically through EXECUTE..USING and I have problems to pass the parameters into it. According to documentation, I understand that the correct form should be this:

CREATE OR REPLACE FUNCTION create_dyn_seq( /* some params */ ) 
RETURNS void AS $$
DECLARE
  _seq_name text;
  _min integer;
  _max integer;
BEGIN
  /*
    some code assigning the variables
    sample values:
    _seq_name := 'hu01/1/0_seq';
    _min := 101;
    _max := 500;
  */    
  EXECUTE 'CREATE SEQUENCE "' || _seq_name || '" MINVALUE $1 MAXVALUE $2 ' 
  USING _min::INT, _max::INT;
  RETURN;
END;
$$ LANGUAGE plpgsql;

I added quotation marks and the _seq_name itself seems fine. However, when I insert these values, it throws following error:

ERROR:  syntax error at or near "$1"
LINE 1: CREATE SEQUENCE "hu01/1/0_seq" MINVALUE $1 MAXVALUE $2

I also tried an alternative syntax of the EXECUTE, as suggested here:

EXECUTE 
  'CREATE SEQUENCE "' || _seq_name || '" MINVALUE ' || $1 || ' MAXVALUE ' || $2  
  USING _min::INT, _max::INT;

Now the error is different:

ERROR:  syntax error at or near "hu01"
LINE 1: CREATE SEQUENCE "hu01/1/0_seq" MINVALUE hu01 MAXVALUE 1

Dollar signs now translate, but the variables in the USING section are overridden by parts of the _seq_name variable. I tried to replace slashes in the sequence name by underscores, but nothing changed.

I also tried it through format() combined with USING, as suggested here, but nothing changed, the same errors occur:

EXECUTE format('CREATE SEQUENCE %I MINVALUE $1 MAXVALUE $2 ', _seq_name) 
USING _min::INT, _max::INT;

EXECUTE format('CREATE SEQUENCE %I MINVALUE ' || $1 || ' MAXVALUE ' || $2, _nazev_seq) 
USING _min::INT, _max::INT;

Then I completely replaced the USING part with format() arguments:

EXECUTE format('CREATE SEQUENCE %I MINVALUE %L MAXVALUE %L ', _seq_name, _min::INT, _max::INT);

Now I am almost where I want to be, the error is different:

ERROR:  syntax error at or near "'101'"
LINE 1: CREATE SEQUENCE "hu01/1/0_seq" MINVALUE '101' MAXVALUE '500'...

I found a question on "how to use integer within FORMAT()" and one of its answers suggests %s. Now it works:

EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s ', _seq_name, _min, _max);

However, the other answer suggested having the integers in the USING section, which is what I would do if it wasn't bugged. My "solution" feels to be a dirty workaround and I would like to do it right, so my question is:

Why the USING section and dollar-sign-escaped values don't work for me?

Upvotes: 0

Views: 437

Answers (1)

pozs
pozs

Reputation: 36214

EXECUTE 'CREATE SEQUENCE "' || _seq_name || '" MINVALUE $1 MAXVALUE $2 ' 
USING _min::INT, _max::INT;

This won't work, because parameter substitution will work only within SELECT, INSERT, UPDATE, and DELETE commands:

Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. Only these statements has execution plan and only these statements should be parametrized.

EXECUTE 'CREATE SEQUENCE "' || _seq_name || '" MINVALUE ' || $1 || ' MAXVALUE ' || $2  
USING _min::INT, _max::INT;

This won't work, because in that context $1 refers to the 1st argument of the function.

EXECUTE format('CREATE SEQUENCE %I MINVALUE %L MAXVALUE %L ', _seq_name, _min::INT, _max::INT);

This won't work, because both MINVALUE & MAXVALUE options of CREATE SEQUENCE statement only accepts integers, not texts (& no implicit cast will be done in ddl).

EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s ', _seq_name, _min, _max);

This is perfectly safe as long as _min & _max is some type of integer. When they are not, use explicit cast here.

Upvotes: 3

Related Questions