How to reset dynamic sequence in PostgreSQL

Question

I need to reset a sequence value in PostgreSQL. If the sequence value is lesser than MAX value of a Sequence column then the sequence value has to be reset.

I tried with below code using dynamic query:

CREATE OR REPLACE FUNCTION fn_test (
    schemaName VARCHAR
    , tableName VARCHAR
    , sequenceColumn VARCHAR
    , sequenceName VARCHAR
    , OUT result int
) AS
$func$
DECLARE
BEGIN    
  EXECUTE 'IF EXISTS (
    SELECT NULL
    FROM pg_catalog.pg_sequences
    WHERE sequencename = '''||sequenceName||'''
        and last_value <> (
                            Select MAX('||sequenceColumn||') 
                            FROM ' ||schemaName||'.'||tableName||' 
                            )
    
    THEN


            SELECT setval(
                            '''||schemaName||'.'||sequenceName||'''
                            ,(
                                SELECT MAX('||sequenceColumn||') 
                                FROM ' ||schemaName||'.'||tableName||'
                             )
                        )
         '
  INTO result;
--END IF
   
END
$func$ LANGUAGE plpgsql 
IMMUTABLE SECURITY DEFINER;

--function call

SELECT fn_test (
        schemaName => 'schemaName',tableName =>'NameOfTable',sequenceColumn => 'Id_Column',sequenceName => 'id_Column_seq'
);

Currently I'm getting a error

ERROR:

 syntax error at or near "IF"
LINE 1: IF EXISTS (
        ^

I'm familiar with handling dynamic queries in SQL server but facing an issue with PostgreSQL as I'm a beginner. Please suggest me a solution

Please check this syntax - https://stackoverflow.com/questions/8449011/dynamic-sql-execute-as-condition-for-if-statement — Yeras_QazaQ, Apr 14 '23 at 11:58
1) Why are you doing this? 2) The code has `and last_value <> (` which means the sequence value could be greater then as well as less then the `max(col_value)`. 3) I see this failing under any sort of load. — Adrian Klaver, Apr 14 '23 at 15:34

Pavel Stehule · Answer 1 · 2023-04-16T05:16:53.427

Your code is SQL injection vulnerable. Never use pattern like ... ''' || some | ''' ....

The code can looks like (with your argument names (see note bellow)):

DECLARE
  r record;
  v bigint;
BEGIN
  SELECT * INTO r
    FROM pg_sequences s
   WHERE s.sequencename = fn_test.sequenceName
     AND s.schemaname =  fn_test.schemaName;
  IF FOUND THEN
    EXECUTE format('SELECT max(%I) FROM %I.%I',
                   sequenceColumn,schemaName, tableName) INTO v;
    IF v <> r.last_value THEN
      PERFORM setval(format('%I.%I', r.schemaname, r.sequencename), v);
    END IF;
  END IF;
END;

When you need to use dynamic SQL, then it should be short as is possible. The variables inside dynamic SQL should be sanitized always.
The PL/pgSQL is not case sensitive language. This is reason why camel notation should not be used in this context. The identifiers schemaname and schemaName are same, and then there are identifier's collision. Usual convention is using snake notation, and when the routine contains some SQL, then PLpgSQL variables should to use some prefix (like _)

How to reset dynamic sequence in PostgreSQL

1 Answers1