CODEWITHSUNDEEP
arrayspostgresqlplpgsql
J.Carlos
J.Carlos

Reputation: 37

PostgreSQL - Array as function parameter

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!

Upvotes: 0

Views: 127

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions