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?

Answer 1

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.

Answer 2

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

Answer 3

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