Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2015

    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).

    Sachin Vyas.

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    re 1) just create a function that returns a table. It is actually a lot easier in Postgres than in Oracle

    create or replace function get_data(p_id integer)
      returns table (id integer, full_name text)
      select id, concat(first_name, ' ', last_name) 
      from person;
    language sql;
    More details in the manual:

    This of course also works with PL/pgSQL functions:

    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.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts