Question 306105 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Returning dynamic query results from PL/PGSQL function, multiple rows

I work on the following test table:

select * from test;
 id | broker  | affiliate | leads 
----+---------+-----------+-------
  1 | broker1 | aff1      |     1
  2 | broker1 | aff2      |     2
  3 | broker2 | aff2      |     4
(3 rows)

I want to create a function which will accept group by column name and where predicate. Here is a working example of the function:

DROP FUNCTION report2(TEXT,TEXT);
CREATE OR REPLACE FUNCTION report2(
    group_by_column_name TEXT,
    sql_where TEXT
)
RETURNS RECORD
AS $$
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;

    

    EXECUTE query INTO result_row;
    RETURN result_row;

END
$$ LANGUAGE 'plpgsql';

and it’s usage:

select report2('broker', '');
NOTICE:  query: SELECT broker, sum(leads) FROM test GROUP BY(broker);
   report2   
-------------
 (broker1,3)
(1 row)

I’m trying to get not only a first row of the query but all the rows (obviously).

I tried the following:

DROP FUNCTION report2(TEXT,TEXT);
CREATE OR REPLACE FUNCTION report2(
    group_by_column_name TEXT,
    sql_where TEXT
)
RETURNS SETOF RECORD
AS $$
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;

--  Does not worK:
--     FOR result_row IN EXECUTE query
--     LOOP
--         RETURN NEXT result_row;
--     END LOOP;

-- Does not worK:
--     RETURN QUERY EXECUTE query;



END
$$ LANGUAGE 'plpgsql';

In both cases, with FOR or RETURN QUERY, the error occurs:

NOTICE:  query: SELECT broker, sum(leads) FROM test GROUP BY(broker);
ERROR:  materialize mode required, but it is not allowed in this context
CONTEXT:  PL/pgSQL function report2(text,text) line 21 at RETURN NEXT

From the [docs][1]:

If multiple rows are returned, only the first will be assigned to the INTO variable(s).

So, how is it possible to get the entire results?



  [1]: https://(https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)
Tags:

2

Answers


  1. 0

    I would return setof json instead of record because the number of result rows varies and json is more usable than record. So here is the function:

    create function report2(arg_group text default '', arg_where text default '')
returns setof json language plpgsql as
$$
begin
  return query execute format(
    'select to_json(t) from (select %s sum(leads) from test %s %s) t', 
    case when arg_group = '' then '' else arg_group || ',' end,
    case when arg_where = '' then '' else 'where ' || arg_where end,
    case when arg_group = '' then '' else 'group by ' || arg_group end
   ); 
end; 
$$

select report2('broker');
    report2
    {"broker":"broker1","sum":3}
    {"broker":"broker2","sum":4}
    Login or Signup to reply.
  2. 0

    You’re using a set-returning function in the select list. It’s possible for SRF with return types that are either clearly specified or predictable, but your set-of-record-returning monstrosity doesn’t qualify.

    Technically, the function you presented is perfectly correct, fine and usable – you can call it, just not the way you tried. Look: (demo at db<>fiddle)

    select * from report2('broker', '')as(col1 text,col2 bigint);
    col1 col2
    broker1 3
    broker2 4

    You can even get it to pack those back up into records:

    select row(col1,col2) from report2('broker', '')as(col1 text,col2 bigint);
    row
    (broker1,3)
    (broker2,4)

    You can even build that into the function if you use the row() constructor to prevent unpacking:

         FOR result_row IN EXECUTE query
     LOOP
         RETURN NEXT row(result_row);
     END LOOP;

    And then you can openly say it’s returning just records (the non-of-your-business type):

    select * from report2('broker', '')as(r record);
    r
    (broker1,3)
    (broker2,4)

    I have very little idea what it is you’re trying to do, why and why this way, but polymorphic types would probably help, packing things into a jsonb suggested by @Stefanov.sm might be easier if you don’t mind wrapping and unwrapping, casting and re-casting things back and forth. PL/pgSQL manual will definitely help.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search