PostgreSQL - Array as function parameter

Question

I want to know if it's posible to do something like this:

CREATE OR REPLACE function my_funct(some_data some_type, array int[]) RETURNS TABLE(code int, desc varchar) LANGUAGE plpgsql as $function$ DECLARE .. BEGIN WHILE some_condition < array.size --i know this doesn't exists LOOP INSERT INTO some_table values(array_data[1]); --I want to insert data from the array as long as it has data END LOOP; RETURN QUERY select 1001, cast ('DONE!' as varchar); END; $function$

i would appreciate any response! thanks!

score 1 · Accepted Answer · 2018-07-09 19:18:50Z

A loop is typically not very efficient, use unnest() and insert the result in a single query:

CREATE OR REPLACE function my_funct(some_data some_type, array_data int[]) RETURNS TABLE(code int, descr varchar) LANGUAGE plpgsql as $function$ DECLARE .. BEGIN INSERT INTO some_table select d.x from unnest(array_data) as d(x); RETURN QUERY select 1001, 'DONE!'; END; $function$

You can however loop through an array as documented in the manual

BEGIN FOREACH x IN ARRAY array_data LOOP INSERT INTO some_table values (x); END LOOP; RETURN QUERY select 1001, 'DONE!'; END;

That will be a lot slower though.

Collectives™ on Stack Overflow

PostgreSQL - Array as function parameter

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related