How to create an aggregate to find min and max value of an integer column and return output in text format?

All we need is an easy explanation of the problem, so here it is.

I have a function where I am specifying the table and column but I require an aggregate that can work for any table and column for similar output

create function min_max_val()
returns text
 LANGUAGE plpgsql
 AS $$
  declare
  mmv text;
  begin
  select format('%s - %s',min(id),max(id) )min_max_vals into mmv from towns;
  return mmv;
 end;
$$ ;

    min_max_vals
---------------------
 1000001 - 11000000

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

you need to dynamic sql as seen below

as seen in the last query, you need to use the result of the query and also address the return values

CREATE TABLE towns ("id" int)
INSERT INTO towns VALUEs (1),(2),(3)
select format('%s - %s',min(id),max(id) )min_max_vals  from towns;
| min_max_vals |
| :----------- |
| 1 - 3        |
create function min_max_val( cols text,tables text)
returns SETOF RECORD
 LANGUAGE plpgsql
 AS $$

  begin

      RETURN QUERY EXECUTE 'select format(''%s - %s'',min('||$1||E'),max('||$1||E') ) AS min_max_vals from '||$2||E';';

 end
$$ ;
SELECT min_max_vals
FROM 
    min_max_val('id','towns')
    AS t( min_max_vals tEXT) 
| min_max_vals |
| :----------- |
| 1 - 3        |

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply