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.
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 |
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂