How to use a temp sequence within a Postgresql function

Question

I have some lines of SQL which will take a set of IDs from the same GROUP_ID that are not contiguous (ex. if some rows got deleted) and will make them contiguous again. I wanted to turn this into a function for reusability purposes. The lines work if executed individually but when I try to create the function I get the error

ERROR:  relation "id_seq_temp" does not exist  
LINE 10: UPDATE THINGS SET ID=nextval('id_se...

If I create a sequence outside of the function and use that sequence in the function instead then the function is created successfully (schema qualified or unqualified). However I felt like creating the temp sequence inside of the function rather than leaving it in the schema was a cleaner solution.

I have seen this question: Function shows error "relation my_table does not exist"
However, I'm using the public schema and schema qualifying the sequence with public. does not seem to help.

I've also seen this question: How to create a sql function using temp sequences and a SELECT on PostgreSQL8. I probably could use generate_series but this adds a lot of complexity that SERIES solves such as needing to know how big of a series to generate.

Here is my function, I anonymized some of the names - just in case there's a typo.

CREATE OR REPLACE FUNCTION reindex_ids(IN BIGINT) RETURNS VOID
LANGUAGE SQL
AS $$
    CREATE TEMPORARY SEQUENCE id_seq_temp
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1;
    ALTER SEQUENCE id_seq_temp RESTART;
    UPDATE THINGS SET ID=ID+2000 WHERE GROUP_ID=$1;
    UPDATE THINGS SET ID=nextval('id_seq_temp') WHERE GROUP_ID=$1;
$$;

Is it possible to use a sequence you create within a function later in the function?

Answer 1

Answer to question

The reason is that SQL functions (LANGUAGE sql) are parsed and planned as one. All objects used must exist before the function runs.

You can switch to PL/pgSQL, (LANGUAGE plpgsql) which plans each statement on demand. There you can create objects and use them in the next command.

See:

• Why can PL/pgSQL functions have side effect, while SQL functions can't?

Since you are not returning anything, consider a PROCEDURE. (FUNCTION works, too.)

CREATE OR REPLACE PROCEDURE reindex_ids(IN bigint)
  LANGUAGE plpgsql AS
$proc$
BEGIN
   IF EXISTS ( SELECT FROM pg_catalog.pg_class
               WHERE  relname = 'id_seq_temp'
               AND    relnamespace = pg_my_temp_schema()
               AND    relkind = 'S') THEN
      ALTER SEQUENCE id_seq_temp RESTART;
   ELSE
      CREATE TEMP SEQUENCE id_seq_temp;
   END IF;

    UPDATE things SET id = id + 2000 WHERE group_id = $1;
    UPDATE things SET id = nextval('id_seq_temp') WHERE group_id = $1;
END
$proc$;

Call:

CALL reindex_ids(123);

This creates your temp sequence if it does not exist already.
If the sequence exists, it is reset. (Remember that temporary objects live for the duration of a session.)
In the unlikely event that some other object occupies the name, an exception is raised.

Alternative solutions

Solution 1

This usually works:

UPDATE things t
SET    id = t1.new_id
FROM  (
   SELECT pk_id, row_number() OVER (ORDER BY id) AS new_id
   FROM   things
   WHERE  group_id = $1     -- your input here
   ) t1
WHERE  t.pk_id = t1.pk_id;

And only updates each row once, so half the cost.

Replace pk_id with your PRIMARY KEY column, or any UNIQUE NOT NULL (combination of) column(s).

The trick is that the UPDATE typically processes rows according to the sort order of the subquery in the FROM clause. Updating in ascending order should never hit a duplicate key violation.
And the ORDER BY clause of the window function row_number() imposes that sort order on the resulting set. That's an undocumented implementation detail, so you might want to add an explicit ORDER BY to the subquery. But since the behavior of UPDATE is undocumented anyway, it still depends on an implementation detail.

You can wrap that into a plain SQL function.

Solution 2

Consider not doing what you are doing at all. Gaps in sequential numbers are typically expected and not a problem. Just live with it. See:

• Serial numbers per group of rows for compound key