CODEWITHSUNDEEP
postgresqlplpgsql
Phoenix
Phoenix

Reputation: 83

Create a procedure/function that creates new sequence

I need to create a new procedure/function in Postgres that creates a new sequence. The procedure/function will get the name of the sequence as a variable and creates it. I tried to follow the documentation but it's not very helpful. This is what I've got so far but it's not working (of course):

CREATE FUNCTION create_seq(text) RETURNS text  
    AS 'CREATE SEQUENCE $1 START 100;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

ERROR:  return type mismatch in a function declared to return text
DETAIL:  Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
CONTEXT:  SQL function "create_seq"```

Upvotes: 0

Views: 739

Answers (2)

user330315
user330315

Reputation:

You need dynamic SQL for that, parameters can't be used as identifiers.

To properly deal with names that potentially need quoting, it is highly recommended to use the format() function to generate the SQL.

And you need to declare the function as returns void as you don't want to return anything.

CREATE FUNCTION create_seq(p_seq_name, text) 
  RETURNS void
AS
$$
begin
 execute format('CREATE SEQUENCE %I START 100', p_seq_name);
end;
$$
LANGUAGE plpgsql;

If you are on Postgres 11, you could also use a procedure instead of a function for that.

Upvotes: 1

Anthony Sotolongo
Anthony Sotolongo

Reputation: 1648

you need use dynamic SQL like said @a_horse_with_no_name, but you need use the plpgsql language not sql language for the function, for remember return void,example,: 

CREATE FUNCTION create_seq_plpgsql(p_seq_name text) 
 RETURNS void
AS
$$
begin
 execute format('CREATE SEQUENCE %I START 100', p_seq_name);
end;
$$
LANGUAGE plpgsql;

Upvotes: 2

Related Questions