A column definition list is required for functions returning "record" in Postgresql

Question

In the Below Postgresql Function i am trying to get results from 2 different tables but it throws error ERROR: 42601: a column definition list is required for functions returning "record".Can anyone please help me.

CREATE OR REPLACE FUNCTION load_page_record(IN _session INT) RETURNS RECORD AS
$$
DECLARE r1 RECORD;
DECLARE r2 RECORD;
DECLARE RESULT RECORD;
BEGIN
    SELECT array_agg(sq.*) AS arr INTO r1
    FROM (SELECT user_id, user_name 
          FROM "user" 
          ) sq;
    SELECT array_agg(sq.*) AS arr INTO r2
    FROM (SELECT client_id, client_name 
          FROM "clients"
          ) sq;

    SELECT r1.arr, r2.arr INTO RESULT;
    RETURN RESULT;
END;
$$ LANGUAGE plpgsql;

Answer 1

It returns a record,

so you should call the function as below,

select load_page_record(5);

The error come if you call it as a table

select * from load_page_record(5);

If you want to return a table place you query with join inside the body as follows,

CREATE OR REPLACE FUNCTION load_page_record1(IN _session INT) 
RETURNS TABLE (column1 integer, column2 integer) as
$BODY$

    SELECT column1, column2
    FROM
    table1 a
    join
    table2 b
    ON a.id = b.id

 $BODY$

LANGUAGE plpgsql;

Answer 2

try this, procedur return table

CREATE OR REPLACE FUNCTION load_page_record(IN _session INT) 
RETURNS table(col1 record[],col2 record[]) AS
$BODY$
BEGIN
    RETURN QUERY 
    select
    (SELECT array_agg(sq.*)
    FROM (SELECT user_id, user_name 
          FROM "user" 
          ) sq
    ),
    (SELECT array_agg(sq.*)
    FROM (SELECT client_id, client_name 
          FROM "clients"
          ) sq
    );
END;
$BODY$ LANGUAGE plpgsql stable;

edit: convert to text, try it

CREATE OR REPLACE FUNCTION load_page_record(IN _session INT) 
RETURNS table(col1 text,col2 text) AS
$BODY$
BEGIN
    RETURN QUERY 
    select
    (SELECT array_agg(sq.*)
    FROM (SELECT user_id, user_name 
          FROM "user" 
          ) sq
    )::text,
    (SELECT array_agg(sq.*)
    FROM (SELECT client_id, client_name 
          FROM "clients"
          ) sq
    )::text;
END;
$BODY$ LANGUAGE plpgsql stable;