PSQL stored procedures using arrays as parameter

I’m using PostgreSQL and currently creating a stored procedure to do some calculations. I’ve been searching all morning and reading the documentation, but either the functionality I want isn’t supported or I must’ve overlooked something. Basically, this is what I have:

CREATE OR REPLACE FUNCTION predict_usage(preWaitTimes text, afterEndTimes text, targetIds text, drivingTime numeric, arrivalTime timestamp with time zone)
    RETURNS TABLE(
       target_id text,
       pre_wait_time numeric,
       after_end_time numeric,
       driving_time numeric,
       starting_time timestamp with time zone,
       ending_time timestamp with time zone
    )
DECLARE
    target_ids uuid[] := string_to_array(targetIds, ',')::uuid;
    pre_wait_times integer[] := string_to_array(preWaitTimes, ',')::integer[];
    after_end_times integer[] := string_to_array(afterEndTimes, ',')::integer[];
BEGIN
SELECT *
FROM generate_series(1, ceil(target.quantity/target.size)) as series,
END;

Basically what happens is that I calculate how often I need to make a travel to deliver something, which I use generate_series for. This works in a single SQL query, and I can use it to calculate how many drives I need to do to deliver it all, each with their own start and endtimes.

Now As different targets have different preparation and ending times (preWaitTimes and afterEndTimes), I would need to somehow connect the index of the target_id currently being used with the arrays of these additional times to make the calculations.

I’ve tried a FOR loop, but didn’t manage to get it running. It also seems to me like the wrong path; I doubt that the DB can optimize FOR Loops that well, where I could just solve it on the java backend. The target is to do it in the DB for speed, mostly.

EDIT:

After thinking about it a bit more, maybe joining the 3 arrays into a table to begin and then do my calculations could work?

Answer

After some more documentation and searching, I’ve found the solution using UNNEST:

SELECT UNNEST(arr1::uuid[]) as id, UNNEST(arr2::text[]) as name, UNNEST(arr3::numeric[]) as value
...

*Attention:

The created table has some limitation. For example, if the id is an uuid[] that uses the same ids that are PK in a table you want to join, it will somehow not do a hashed join. This can be circumvented with the following statement:

....
WHERE target_table.id = generated_table.id
    and target_table.id = any(arr1::uuid[])

Advertisement

Answer