Issue in Converting composite type of Oracle to Postgres and Bulk collect compatible equivalent in Postgres

Question

In Oracle we have a function where we are using bulk collect to fetch data and store in a variable like.

      SELECT DISTINCT emp_id
      BULK COLLECT INTO v_emp_array
      FROM   emp_details;

where v_emp_array is a type:

Oracle: CREATE OR REPLACE TYPE v_emp_array IS TABLE OF VARCHAR2(4000);

Please help to get this functionality in PostgreSQL.

Being new to these, I am not able to find the correct syntax for it.

there is a similar question [here](https://stackoverflow.com/questions/12533802/sql-server-equivalent-of-bulk-collect-in-oracle) — Prashanth Benny, Jul 20 '17 at 13:58
@PrashanthBenny Similar is not helpful. Especially when they relate to a different DB vendor. — VynlJunkie, Jul 20 '17 at 14:03
They are after Postgres. Your link is SQL server to Oracle. ?? — VynlJunkie, Jul 20 '17 at 14:09
Try using arrays. Documentation. https://www.postgresql.org/docs/current/static/arrays.html — VynlJunkie, Jul 20 '17 at 14:09

score 0 · Answer 1 · answered Apr 02 '18 at 11:29

0

do $$
declare
v_emp_array varchar[];
begin
select array(select distinct emp_id from emp_details) into v_emp_array;
end;
$$;

answered Apr 02 '18 at 11:29

Vinod

1

Code only answers are discouraged on StackOverflow. Also, try to avoid answering a duplicate question. If you can, flag it as duplicate or comment on the question as such. – philantrovert Apr 02 '18 at 12:15

1 Answers1