PSQL stored procedures using arrays as parameter

Question

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?

You shouldn't pass parameters using those dreaded comma separated values. They should be passed native arrays, declare your parameter, e.g. targetIds text[] — user330315
– user330315, Commented Nov 22, 2022 at 8:55

Ricardo · Accepted Answer · 2022-11-23 07:49:01Z

1

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[])

answered Nov 23, 2022 at 7:49

Ricardo

1971 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PSQL stored procedures using arrays as parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related