Unanswered: Oracle Pipelined function and SET equivalent in Postgres.
I am a newbie to Postgres & am using Postgres 9.4.
I have been using Oracle 11g for a while now & was searching whether we can have :
1) Oracle's PIPELINED function implementaion in Postgres.
2) Procedure in Postgres to pass a "SET" (as in Oracle) as an OUT parameter. such that the Java code remains the same.
I referred & tried the following things in Postgres, but the Java side of implementation is different, we are targeting to have a common java code for both these DB for the the above two requirements.
Postgre Options tried -
Postgres function returning SETOF, RECORD, TABLE, EXECUTE QUERY as OUTPUT via OUT parameter (in case of SET as OUT parameter) and rows (in case of PIPELINED function).
The SQL queries are different though. Whereas in Oracle you need to wrap the function call in a table() cast, this is not necessary (and possible) in Postgres:
select * from get_data(42);
select * from table(get_data(42));
re 2) the closes to an Oracle collection (set) would be an array in Postgres. Postgres' array handling is actually extremely good and efficient.
But "*such that the Java code remains the same*" is not going to happen.