Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (id int, broker text,affiliate text, leads int);
insert into test values
  (1, 'broker1','aff1',1)
 ,(2, 'broker1','aff2',2)
 ,(3, 'broker2','aff2',4)
returning *;

CREATE TABLE




id
broker
affiliate
leads


1
broker1
aff1
1

2
broker1
aff2
2

3
broker2
aff2
4



INSERT 0 3


DROP FUNCTION IF EXISTS report2(TEXT,TEXT);
CREATE OR REPLACE FUNCTION report2(
    group_by_column_name TEXT,
    sql_where TEXT
)
RETURNS SETOF RECORD
AS $f$
DECLARE
    query TEXT;
    result_row RECORD;
BEGIN
    query := 'SELECT ';
    IF group_by_column_name <> '' THEN
        query := query || group_by_column_name || ', ';
    END IF;
    query := query || 'sum(leads) FROM test';
    IF sql_where <> '' THEN
        query := query || ' WHERE ' || sql_where;
    END IF;
    IF group_by_column_name <> '' THEN
        query := query || ' GROUP BY(' || group_by_column_name || ')';
    END IF;
    RAISE NOTICE 'query: %;', query;

    RETURN QUERY EXECUTE query;
END
$f$ LANGUAGE 'plpgsql';

DROP FUNCTION



CREATE FUNCTION


select * from report2('broker', '')as(col1 text,col2 bigint);


col1
col2


broker1
3

broker2
4



SELECT 2


select row(col1,col2) from report2('broker', '')as(col1 text,col2 bigint);


row


(broker1,3)

(broker2,4)



SELECT 2


DROP FUNCTION IF EXISTS report2(TEXT,TEXT);
CREATE OR REPLACE FUNCTION report2(
    group_by_column_name TEXT,
    sql_where TEXT
)
RETURNS SETOF RECORD
AS $f$
DECLARE
    query TEXT;
    result_row RECORD;
BEGIN
    query := 'SELECT ';
    IF group_by_column_name <> '' THEN
        query := query || group_by_column_name || ', ';
    END IF;
    query := query || 'sum(leads) FROM test';
    IF sql_where <> '' THEN
        query := query || ' WHERE ' || sql_where;
    END IF;
    IF group_by_column_name <> '' THEN
        query := query || ' GROUP BY(' || group_by_column_name || ')';
    END IF;
    RAISE NOTICE 'query: %;', query;

     FOR result_row IN EXECUTE query
     LOOP
         RETURN NEXT row(result_row);
     END LOOP;
END
$f$ LANGUAGE 'plpgsql';

DROP FUNCTION



CREATE FUNCTION


select * from report2('broker', '')as(r record);


r


(broker1,3)

(broker2,4)



SELECT 2