How to reference parameters inside a PL/pgSQL function?

Question

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.

Answer 1

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.

Answer 2

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