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

    Unanswered: Oracle Pipelined function and SET equivalent in Postgres.

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


    Regards,
    Sachin Vyas.

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

    Code:
    create or replace function get_data(p_id integer)
      returns table (id integer, full_name text)
    as
    $body$
      select id, concat(first_name, ' ', last_name) 
      from person;
    $body$
    language sql;
    More details in the manual: http://www.postgresql.org/docs/curre...CTION-EXAMPLES

    This of course also works with PL/pgSQL functions: http://www.postgresql.org/docs/curre...ENTS-RETURNING

    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:

    Code:
    select * from get_data(42);
    versus
    Code:
    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: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

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
  •