PostgreSQL function with table and column names as parameters,and would return min and max of that column (any type) as an array?

Question

I am trying to make a function in PostgreSQL, which would take a name of a table and a name of one column in that table (in varchars); and return an array which would have minimum and maximum values of that column.

In advance I do not (want to) know the type of the column, ie. type of the elements in the return array.

I've tried to create a function with polymorphic anyarray as a return type, but keep hitting syntax errors. Have tried both in SQL and in PLPQSQL.

CREATE OR REPLACE FUNCTION minmax(tablename character varying, columnname character varying, OUT minmaxa anyarray) AS
$function$
BEGIN
   EXECUTE FORMAT('SELECT array_agg(foo) AS %s FROM (
            (SELECT min(%s) AS foo FROM %s)
            UNION
            (SELECT max(%s) AS foo FROM %s)
    ) AS foobar'
       , columnname
       , columnname, tablename
       , columnname, tablename)
   INTO minmaxa; 
END
$function$
LANGUAGE plpgsql;

The string from the FORMAT would be:

SELECT array_agg(foo) AS columnname FROM ( 
  (SELECT min(columnname) AS foo FROM tablename)
  UNION
  (SELECT max(columnname) AS foo FROM tablename)
 ) AS foobar

Then test case:

create table example (columnA int, columnB varchar);
insert into example (columnA, columnB) values (1, 'ac'), (2, 'ab'), (3, 'aa');

select minmax('example', 'columnA');
select minmax('example', 'columnB');

select array_agg(columnA) from (
   (select min(columnA) AS columnA from example)
   UNION
   (select max(columnA) AS columnA from example)
  ) AS foobar;

Should return:

{1,3}
{'aa','ac'}
{1,3}

But currently function definition gives: "SQL Error [42P13]: ERROR: cannot determine result data type Detail: A function returning a polymorphic type must have at least one polymorphic argument."

I can make it work, by defining a dummy function parameter 'foo':

CREATE OR REPLACE FUNCTION minmax(tablename character varying, columnname character varying, foo anyelement, OUT minmaxa anyarray)

(Note. A function parameter foo is not used in function body anywhere.)

And now giving a dummy typed value of the correct type of returning array when calling the function:

select minmax('example', 'columnB', ''::varchar);
select minmax('example', 'columnA', 0::int);

Then it works, but what should be changed to I would not need that dummy function parameter?

score 2 · Accepted Answer · answered May 29 '19 at 17:40

You can't do this with the standard polymorphic types, but you could do this with jsonb.

CREATE OR REPLACE FUNCTION minmax(tablename regclass, columnname character varying)
RETURNS JSONB AS
$function$
DECLARE
  minmaxa jsonb;
BEGIN
   EXECUTE FORMAT('SELECT jsonb_agg(foo) AS %I FROM (
            (SELECT min(%I) AS foo FROM %I)
            UNION
            (SELECT max(%I) AS foo FROM %I)
    ) AS foobar'
       , columnname
       , columnname, tablename
       , columnname, tablename)
   INTO minmaxa;
   RETURN minmaxa;
END
$function$
STABLE
LANGUAGE plpgsql;


-- Test with integers
CREATE TABLE test (a int);
insert into test (a) values (1), (2), (3);
select minmax('test'::regclass, 'a');
 minmax
--------
 [1, 2]
(1 row)


-- Test with timestamps
create table test2 (a timestamp);
insert into test2 values ('2012-01-01T00:00:00'), ('2015-01-01T00:00:00');
select minmax('test2'::regclass, 'a');
                     minmax
------------------------------------------------
 ["2012-01-01T00:00:00", "2015-01-01T00:00:00"]
(1 row)

I used regclass instead of string for the tablename to help find the correct table according to your search path.

score 1 · Answer 2 · answered May 29 '19 at 16:58

I am afraid it is not possible to easily get rid of that attribute you call foo. As I understand from the documentation the value passed as foo parameter is required to tell Postgres what the return data type would be.

38.2.5. Polymorphic Types

Five pseudo-types of special interest are anyelement, anyarray, anynonarray, anyenum, and anyrange, which are collectively called polymorphic types. Any function declared using these types is said to be a polymorphic function. A polymorphic function can operate on many different data types, with the specific data type(s) being determined by the data types actually passed to it in a particular call.

Polymorphic arguments and results are tied to each other and are resolved to a specific data type when a query calling a polymorphic function is parsed. Each position (either argument or return value) declared as anyelement is allowed to have any specific actual data type, but in any given call they must all be the same actual type.

So how I understand this is that with polymorphic function removing attribute foo is not possible and your function seems to be such since it uses pseudo-type anyarray.

Thanks for clearing and pointing (and confirming) that out. I was wondering if PostgreSQL-"language" or if SQL-language overall is "dynamically/static typed" and "strong/weak typed". https://stackoverflow.com/questions/2351190/static-dynamic-vs-strong-weak "Polymorphic arguments and results are tied to each other and are resolved to a specific data type **when a query calling a polymorphic function is parsed.**" — zimon, May 30 '19 at 09:26

PostgreSQL function with table and column names as parameters,and would return min and max of that column (any type) as an array?

2 Answers2