2

I am getting an issue where when I try to reference the parameters directly by name I am getting back the literal values after the function gets called. Can anyone help me out with how I can use the parameter values here?

CREATE OR REPLACE FUNCTION dbo.reset_sequence( tablename text, columnname text, sequence_name text) RETURNS void AS $BODY$ DECLARE BEGIN IF( (SELECT MAX( columnname ) ) < (SELECT min_value FROM dbo.tablename) ) THEN -- EXECUTE 'SELECT setval( ' || sequence_name || ', (SELECT min_value FROM dbo.' || sequence_name ||')'; ELSE -- EXECUTE 'SELECT setval( ' || sequence_name || ', ' || '(SELECT MAX("' || columnname || '") FROM dbo."' || tablename || '")' || '+1)'; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE;

EDIT: The issue I am having is more specifically related to the syntax outside the EXECUTE commands. The other solution doesn't really help me there.

After researching another topic I am trying another solution but am still getting issues.

CREATE OR REPLACE FUNCTION dbo.reset_sequence( tablename text, columnname text, sequence_name text) RETURNS void AS $BODY$ DECLARE _maxVal int; _minVal int; BEGIN EXECUTE format('SELECT MAX( ''' || columnname || ''' ) FROM ' || schema_name || '."' || tablename || '"') INTO _maxVal; EXECUTE format('SELECT min_value FROM ' || schema_name || '."' || sequence_name || ''' ') INTO _minVal; IF( maxVal < _minVal) THEN -- EXECUTE 'SELECT setval( ' || sequence_name || ', (SELECT min_value FROM dbo.' || sequence_name ||')'; ELSE -- EXECUTE 'SELECT setval( ' || sequence_name || ', ' || '(SELECT MAX("' || columnname || '") FROM dbo."' || tablename || '")' || '+1)'; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE;

The syntax on this works, however when I call the function I get an error where it can't store the EXECUTE statements as integers, it seems to be returning the name of the column, not the max value in that column.

Improve this question
5
  • You're not trying to reference the parameters or their values. You're trying to reference the tables, sequences, and columns which have the same name as the value of the parameters. I think there are a number of questions here already answering this sort of thing, let's take a look. Commented Aug 31, 2015 at 19:51
  • 1
    possible duplicate of Table name as a PostgreSQL function parameter Commented Aug 31, 2015 at 19:52
  • I am trying to reference the parameters btw. This function gets called and passes in the text names of things such as the sequence. I literally want the min_value column data from dbo."passed_in_sequence_name" Commented Aug 31, 2015 at 20:11
  • Exactly what I'm saying. That's not the parameter. The parameter is a text value. Did that link answer your question? Commented Aug 31, 2015 at 20:17
  • Well apparently my issue is that I can't use variable names outside of an EXECUTE statement, which would be the issue. Unfortunately it looks like I can't stick an EXECUTE inside of an IF clause either. I think I'm stuck for now, will have to restructure my logic unless someone else has an idea of how to get this working. Commented Aug 31, 2015 at 20:21

2 Answers 2

Reset to default
3

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.

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

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 _max_val int; _min_val int; BEGIN EXECUTE format('SELECT MAX(%I) FROM %I.%I', col, sch, tbl) INTO _max_val; EXECUTE format('SELECT min_value FROM %I.%I', sch, seq) INTO _min_val; IF _max_val < _min_val 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;

Which can be simplified to:

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;

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

CREATE OR REPLACE FUNCTION pg_temp.reset_sequence(sch text, tbl text, col text, seq text) RETURNS void AS $func$ BEGIN EXECUTE format($$ SELECT setval('%1$I.%4$I', GREATEST(s.min, t.max + 1), false) -- now we need + 1 FROM (SELECT MAX(%3$I) FROM %1$I.%2$I) t(max) , (SELECT min_value FROM %1$I.%4$I) s(min) $$, sch, tbl, col, seq); END $func$ LANGUAGE plpgsql;

Various problems

  • 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:

    SELECT setval('foo', 42); -- Next nextval will return 43

  • On the other hand, if you want to start at the very beginning of the sequence, use:

    SELECT setval('my_sequence', min_value, false)

More explanation

  • It's only clean to run a query like:

    SELECT setval('my_sequence', min_value) FROM other_sequence;

    .. because the table of a SEQUENCE is guaranteed to have exactly 1 row.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can use the results of EXECUTE statements in IF statements, if you store them into temporary values first using EXECUTE .. INTO. For example:

DECLARE max_column_value int; BEGIN EXECUTE 'SELECT MAX(' || columnname || ') FROM dbo."' || tablename || '"' INTO max_column_value; IF max_column_value < 1000 THEN ...

If columnname were 'col' and tablename were 'tbl', this should be equivalent to:

IF (SELECT MAX(col) FROM dbo."tbl") < 100 THEN
Improve this answer

1 Comment

I actually just posted an edit doing this exact method, still having some issues though, i'll take a look how you structured yours here and see if I can figure out my error.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.