Return to Answer

There is no explanation what the function is supposed to exactly and the code is ambiguous in multiple places. As I understand your code, the purpose of the function is:

Reset a given sequence in a given schema to the maximum value of a given column in a given table of the same schema - or to the minimum value of the given sequence if that should be bigger.

• You are mixing format() with plain string concatenation in nonsensical ways. Be sure to read the manual on format() before you continue.

• The variable schema_name was undefined. I added another function parameter to pas it. It's odd that you use the schema dbo in the two setval() calls at the end. Also "dbo" is a typical identifier for SQL Server, but not in Postgres. Maybe another error or on purpose?

• The variable maxVal was undefined. Probably should be _maxVal. I removed that variable completely in the simplified version.

• You don't need + 1 for setval(), since the next value returned is incremented by default. Example in the manual:

There is no explanation what the function is supposed to do exactly and the code is ambiguous. As I understand it, this is the purpose of the function:

Reset a given sequence in a given schema to the maximum value of a given column in a given table of the same schema - or the minimum of the given sequence if that should be bigger.

• You are mixing format() with plain string concatenation in nonsensical ways. Be sure to read the manual on format() before you continue.

• The variable schema_name was undefined. I added another function parameter to pass it. It's odd that you use the schema dbo in the two setval() calls at the end. Also "dbo" is a typical identifier for SQL Server, but not in Postgres. Maybe another error or on purpose?

• The variable maxVal was undefined. Probably should be _maxVal. I removed that variable completely in the simplified version.

• You don't need + 1 for setval(), since the next value returned is incremented by default. Example in the manual:

There is no explanation what the function is supposed to exactly and the code is ambiguous in multiple places. This should work:

Which can be further simplified to:

If the use of dbo is just a typo (as I suspect), you can get that even much simpler:

There is no explanation what the function is supposed to exactly and the code is ambiguous in multiple places. As I understand your code, the purpose of the function is:

Reset a given sequence in a given schema to the maximum value of a given column in a given table of the same schema - or to the minimum value of the given sequence if that should be bigger.

It's unclear whether the schema dbo is also involved. Keeping dbo in the loop, this should work:

Which can be simplified to:

If the use of dbo is just the typo I suspect, even much simpler:

CREATE OR REPLACE FUNCTION reset_sequence( sch text, -- schema_name  tbl text, -- table_name col text, -- column_name  seq text -- sequence_name -- all unquoted and case-SENSITIVE!   ) RETURNS void AS $func$ DECLARE _found bool; BEGIN EXECUTE format('SELECT true FROM %1$I.%2$I HAVING MAX(%3$I) < (SELECT min_value FROM %1$I.%4$I)' , sch, tbl, col, seq) INTO _found; IF _found THEN EXECUTE format($$SELECT setval('%1$I.%2$I', min_value, false) FROM dbo.%2$I;$$ , sch, seq); ELSE EXECUTE format($$SELECT setval('%I.%I', max(%I)) FROM dbo.%I;$$ , sch, seq, col, tbl); END IF; END $func$ LANGUAGE plpgsql; 

CREATE OR REPLACE FUNCTION pg_temp.reset_sequence(sch text, tbl text, col text, seq text) RETURNS void AS $func$ DECLARE _found bool; BEGIN EXECUTE format('SELECT true FROM %1$I.%2$I HAVING MAX(%3$I) < (SELECT min_value FROM %1$I.%4$I)' , sch, tbl, col, seq) INTO _found; IF _found THEN EXECUTE format($$SELECT setval('%1$I.%2$I', min_value, false) FROM dbo.%2$I;$$ , sch, seq); ELSE EXECUTE format($$SELECT setval('%I.%I', max(%I)) FROM dbo.%I;$$ , sch, seq, col, tbl); END IF; END $func$ LANGUAGE plpgsql;