How to have multiple colums in a variable/array-CodePudding

I have this query

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

I want to insert it into a variable in which I can loop and perform actions.

i.e unsatified_documents driver_expiring_documents;

where

create table if not exists driver_expiring_documents (
    driver_id text,
    expiration_date date
);

and then loop through it

if(unsatified_documents != '{}') then
     foreach expiring_doc in array unsatified_documents loop
     -- -- Get Driver ID --
        driver_id_arg := expiring_doc.driver_id;
         expiring_date := expiring_doc.expiration_date;

     end loop;
 end if;

How can I do it?

CodePudding user response：

You can store it in an array variable, but if you want to loop through the results, that would be unnecessarily complicated and inefficient. You'd have to pack it into a data structure that potentially uses a lot of RAM, only to unpack it right away.

Simply do

DECLARE
   r record;
BEGIN
   FOR r IN
      select expiration_date, driver_id from ... WHERE ...
      union
      select expiration_date, driver_id from ... where ...
   LOOP
      /* do something with "r.expiration_date" and "r.driver_id" */
   END LOOP;
END;

CodePudding user response：

You can use a custom type, for example:

CREATE TYPE my_doc
AS
(
    driver_id text,
    expiration_date date
);

Then, if you want make an array of these values you can do:

SELECT array_agg((driver_id, expiration_date)::my_doc)
FROM driver_expiring_documents;

But you don't even need the array, which can be pretty big, you can just loop in a function with this simple query:

SELECT (driver_id, expiration_date)::my_doc
FROM driver_expiring_documents;

Or, you can skip the intermediate table and select with the union:

select (expiration_date, driver_id)::my_doc 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)::my_doc from all_requirements_vehicle_documents where expiration_date <= (now()   interval '1 month')::DATE and expiration_date > (now() - interval '7 day')::DATE

And just a consideration: if you can, use UNION ALL, it will be faster.