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 $$
  mmv text;
  select format('%s - %s',min(id),max(id) )min_max_vals into mmv from towns;
  return mmv;
$$ ;

 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)
 LANGUAGE plpgsql
 AS $$


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

$$ ;
SELECT min_max_vals
    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 or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply