Postgres how to return multiple columns in subquery

Question

I have a simple Postgres function:

CREATE OR REPLACE FUNCTION public.drivers_have_unsatisfied_documents()
 RETURNS driver_expiring_documents
AS $function$
DECLARE
    unsatified_documents driver_expiring_documents;
    expiring_doc driver_expiring_documents%rowtype;
    expiring_doc_driver_ids text[];
    expired_doc_driver_ids text[];
    expiring_date date;
    driver_id_arg text;
BEGIN
    unsatified_documents := array(
    select expiration_date, driver_id from all_requirements_driver_documents where expiration_date <= (now() + interval '1 month')::DATE and expiration_date > (now() - interval '7 day')::DATE
    union
    select expiration_date, driver_id from all_requirements_vehicle_documents where expiration_date <= (now() + interval '1 month')::DATE and expiration_date > (now() - interval '7 day')::DATE
    );
    -- Do some more stuff, code intentionally removed ---
    return unsatified_documents;
end;
$function$ stable language 'plpgsql';

This is throws error saying

multiple columns in subquery

Can someone please help me fix it?

You cannot put a query that returns multiple columns in an array. — Bergi, Jul 12 '22 at 20:33
Duplicate question: https://stackoverflow.com/questions/23106445/postgresql-function-to-return-multiple-columns — Umut TEKİN, Jul 12 '22 at 23:27
Does this answer your question? [How to return multiple rows from PL/pgSQL function?](https://stackoverflow.com/questions/17244256/how-to-return-multiple-rows-from-pl-pgsql-function) — Umut TEKİN, Jul 12 '22 at 23:27
Why not use `RETURNS setof driver_expiring_documents` to return multiple rows with multiple columns? — , Jul 13 '22 at 07:42

Postgres how to return multiple columns in subquery

0 Answers0