Sequence name taken from variable

Question

How do I create a new sequence taking its name is from a variable?

Let's take a look at the following example:

CREATE OR REPLACE FUNCTION get_value(_name_part character varying)
  RETURNS INTEGER AS
$BODY$
DECLARE
    result bigint;
    sequencename character varying(50);
BEGIN
    sequencename = CONCAT('constant_part_of_name_', _name_part);
    IF((SELECT CAST(COUNT(*) AS INTEGER) FROM pg_class
        WHERE relname LIKE sequencename) = 0)
    THEN
       CREATE SEQUENCE sequencename --here is the guy this is all about
       MINVALUE 6000000
       INCREMENT BY 1;
    END IF;
    SELECT nextval(sequencename) INTO result;
    RETURN result;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

Now, let's say I want a sequence with _name_part = 'Whatever', so I type:

SELECT get_value('Whatever');

If sequence constant_part_of_name_Whatever does not exist, my function should create it and take a value; if it exists it should only take a value. However, I created sequence constant_part_of_name_sequencename.

How do I put the value of the variable in sequence definition to make it work?

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

The currently accepted answer has a number of problems. Most importantly it fails to take the schema into account.

Use instead:

CREATE OR REPLACE FUNCTION get_value(_name_part text)
  RETURNS bigint AS
$func$
DECLARE
   _seq text  := 'constant_part_of_name_' || _name_part;
BEGIN

CASE (SELECT c.relkind = 'S'::"char"
      FROM   pg_namespace n
      JOIN   pg_class     c ON c.relnamespace = n.oid
      WHERE  n.nspname = current_schema()  -- or provide your schema!
      AND    c.relname = _seq)
WHEN TRUE THEN           -- sequence exists
    -- do nothing
WHEN FALSE THEN          -- not a sequence
   RAISE EXCEPTION '% is not a sequence!', _seq;
ELSE                     -- sequence does not exist, name is free
   EXECUTE format('CREATE SEQUENCE %I MINVALUE 6000000 INCREMENT BY 1', _seq);
END CASE;

RETURN nextval(_seq);

END
$func$  LANGUAGE plpgsql;

SQL Fiddle.

Major points

Your test was needlessly expensive and incorrect. You need to take the schema into account. A sequence of the same name can exist in another schema, which would make your function fail.
I use the current schema as default, since you did not specify otherwise. Details:
- How does the search_path influence identifier resolution and the "current schema"

You also need to be aware that the name of a sequence conflicts with other names of other objects in the same schema. Details:
- How to create sequence if not exists

varchar(50) as data type is pointless and may cause problems if you enter a longer string. Just use text or varchar.
The assignment operator in plpgsql is :=, not =.
You can assign a variable at declaration time. Shorter, cheaper, cleaner.
You need dynamic SQL, I am using format() with %I to escape the identifier properly. Details:
- INSERT with dynamic table name in trigger function

concat() is only useful if NULL values can be involved. I assume you don't want to pass NULL.
VOLATILE is default and therefore just noise.
If you want to return NULL on NULL input, add STRICT.

edited May 23 '17 at 10:33

Community

1
1

answered Sep 19 '14 at 12:54

Erwin Brandstetter

605,456
145
1,078
1,228

My function was pretty simple, because the database I'm running is not really complicated. However, writing things properly from the very beginning is definitely a good habit, so I'm gonna use your answer and look into topics provided in your links. Thanks for the answer. – spoko Sep 19 '14 at 13:15
@spoko: I updated the function once more. Slightly more speed & elegance. Added a fiddle and some more explanation. – Erwin Brandstetter Sep 19 '14 at 13:46
There's an inherent race condition when checking for existence through `pg_*` views. it's not necessary anyway, you may just call `nextval(seq)` and process the non-existence error in an exception clause. At least `nextval` will block if another session is busy dropping or creating the same sequence. – Daniel Vérité Sep 19 '14 at 18:22
@DanielVérité: Provoking an exception doesn't remove the race condition. And, [quoting the manual](http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING): `A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.` Would widen time frame for race. One could add `FOR SHARE` to the `SELECT` to prevent concurrent `DELETE` (never tried it). But there is no medicine against a concurrent `CREATE SEQUENCE`. Best keep the transaction small ... – Erwin Brandstetter Sep 19 '14 at 20:04
@ErwinBrandstetter thanks for your participation here. I know some time has already passed, but I hope you'll see this anyway. The code above works absolutely fine with Postgres 9.3. However, when I run it in Postgres 8.3.23, I get `syntax error at or near "CASE"` and ^ mark pointing at the beginning of the CASE word in your code. Do you know what do I need to add/remove/change to make it work? – spoko Oct 09 '14 at 10:44
@spoko: The procedural [`CASE` construct in plpgsql was introduced with pg 8.4](http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS). Not to be confused with the [SQL `CASE` expression](http://www.postgresql.org/docs/current/interactive/functions-conditional.html#FUNCTIONS-CASE)! Replace with `IF` (or preferably upgrade to a current version). – Erwin Brandstetter Oct 15 '14 at 23:03

score 0 · Answer 2 · edited Sep 19 '14 at 11:47

0

Try this. Hope this work for you.

CREATE OR REPLACE FUNCTION get_value(_name_part character varying) RETURNS INTEGER AS
$BODY$
DECLARE
    result bigint;
    sequencename character varying(50);
    v_sql character varying;
BEGIN
    sequencename = CONCAT('constant_part_of_name_', _name_part);
    IF((SELECT CAST(COUNT(*) AS INTEGER) FROM pg_class WHERE relname LIKE sequencename) = 0)
     THEN
     v_sql :=  'CREATE SEQUENCE '||sequencename||' 
       MINVALUE 6000000
       INCREMENT BY 1;';
       EXECUTE  v_sql;
    END IF;
    SELECT nextval(sequencename) INTO result ;
    RETURN result;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

edited Sep 19 '14 at 11:47

spoko

783
1
10
24

answered Sep 19 '14 at 11:37

Ilesh Patel

2,053
16
27

It did, that was what I was looking for. However, I allowed myself to slightly improve your answer, I hope you don't mind ;). – spoko Sep 19 '14 at 11:46
1

This function still has a number of problems. I provided another answer. – Erwin Brandstetter Sep 19 '14 at 12:59

score 0 · Answer 3 · answered Sep 19 '14 at 12:07

CREATE OR REPLACE FUNCTION get_value(_name_part character varying) RETURNS INTEGER AS
$BODY$
DECLARE
    result bigint;
    sequencename character varying(50);
BEGIN
    sequencename = CONCAT('constant_part_of_name_', _name_part);
    IF  (select exists(SELECT relname FROM pg_class c WHERE c.relkind = 'S' and relname = ''''||sequencename||'''') = false )
    THEN
    execute 'CREATE SEQUENCE '||sequencename||'MINVALUE 6000000 INCREMENT BY 1';
    else
    END IF;
    execute 'SELECT nextval('''||sequencename||''')' INTO result;
    RETURN result;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

Sequence name taken from variable

3 Answers3

Major points