PostgreSQL - return a record[] from a plpgsql function

Question

I have an employer and an employee table. I have a join table that joins them.

CREATE TABLE employer (id int primary key, name text);
CREATE TABLE employee (id int primary key, name text);
CREATE TABLE employer_employee_join(
    employer_id int REFERENCES employer(id),
    employee_id int REFERENCES employee(id)
);

INSERT INTO employer (id, name) VALUES (1, 'the boss');
INSERT INTO employee (id, name) VALUES (1, 'employee1');
INSERT INTO employee (id, name) VALUES (2, 'employee2');

INSERT INTO employer_employee_join (employer_id, employee_id) VALUES(1, 1);
INSERT INTO employer_employee_join (employer_id, employee_id) VALUES(1, 2);

My sql query returns employer and aggregates employee returning an array of records (record[]).

SELECT 
employer.id, employer.name, array_agg((employee.id, employee.name))
FROM employer
LEFT JOIN employer_employee_join
ON employer_employee_join.employer_id = employer.id
LEFT JOIN employee
ON employee.id = employer_employee_join.employee_id
GROUP BY employer.id;

This works fine.

But when I put it in a PL/PGSQL function it fails:

CREATE OR REPLACE FUNCTION _test()
    RETURNS table(id integer, name text, agg record[]) 
    LANGUAGE 'plpgsql'
AS $BODY$
begin
    SELECT 
    employer.id, employer.name, array_agg((employee.id, employee.name))
    FROM employer
    LEFT JOIN employer_employee_join
    ON employer_employee_join.employer_id = employer.id
    LEFT JOIN employee
    ON employee.id = employer_employee_join.employee_id
    GROUP BY employer.id;
end;
$BODY$

The error is

ERROR:  PL/pgSQL functions cannot accept type record[]
SQL state: 0A000

How can I get a plpgsql function to return an array of records?

(I don't really want to use json_agg() because of another layer in the system outside of postgresql and my control)

Answer 1

Thanks to @Bergi. It was a composite type I needed.

create type employee_agg as (id int, name text);

CREATE OR REPLACE FUNCTION _test()
    RETURNS table(id integer, name text, agg employee_agg[]) 
    LANGUAGE plpgsql
AS $BODY$
begin
    return query
    SELECT 
    employer.id, 
    employer.name, 
    array_agg(row(employee.id, employee.name)::employee_agg)
    FROM employer
    LEFT JOIN employer_employee_join
    ON employer_employee_join.employer_id = employer.id
    LEFT JOIN employee
    ON employee.id = employer_employee_join.employee_id
    GROUP BY employer.id;
end;
$BODY$;

Answer 2

Use a VIEW

Just do this...

CREATE VIEW _test
AS
    SELECT 
    employer.id, 
    employer.name, 
    array_agg(row(employee.id, employee.name)::employee_agg)
    FROM employer
    LEFT JOIN employer_employee_join
    ON employer_employee_join.employer_id = employer.id
    LEFT JOIN employee
    ON employee.id = employer_employee_join.employee_id
    GROUP BY employer.id;

Your function has no arguments, it's just a query. This is a much better idea.

Alternatively, you can use a SQL function (but the view is better).

Answer 3

I got the same error below:

ERROR: PL/pgSQL functions cannot return type record[]

Because I set RECORD[] type to a RETURNS clause as shown below:

CREATE FUNCTION my_func() RETURNS RECORD[] AS $$
BEGIN                          -- ↑ ↑ ↑ ↑ 
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Actually, RECORD[] type doesn't exist in PostgreSQL according to the answer of my question.