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

1

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.

edited Jul 9, 2018 at 19:18

answered Jul 9, 2018 at 19:16

user330315

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

1 Comment

Lukasz Szozda Over a year ago

"desc" and "array" are reserved words and should be quoted.

Collectives™ on Stack Overflow

PostgreSQL - Array as function parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related