0

I have the following requirement,

Call a procedure with table of ID's and get the matching records from another table in PG. Eg:

CALL PROC_A(<input will be table type>, out < table type>)

CREATE PROC_A (in input <some_tbl_type>, inout output <some_tbl_typ>)
AS $$

    out = select some columns 
          from <some_tbl>
          where id in (select id from input)

$$;

How to do this in Postgres? i want to use only PROCEDURE not function.

So that i can migrate my existing code to PG.

Rajin
  • 93
  • 1
  • 3
  • 1
    Use a set returning function. Procedures are not intended to return results let alone result sets. –  May 09 '19 at 16:44
  • Does your existing code manage transactions? If not, stick with functions instead of procedures. – Jeremy May 09 '19 at 16:59
  • Related ? https://stackoverflow.com/questions/50940438/postgresql-11-procedures – Jaisus May 10 '19 at 07:56
  • And could you show the code that specifically make you write that you need a procedure? (That seems quite odd without context) – Jaisus May 10 '19 at 08:00
  • @Jaisus, I'm new to PG and trying to migrate procedures from Hana. I have lots procedure which do transactions and output the results in table format. – Rajin May 14 '19 at 16:20
  • So, as a_horse_with_no_name told you, in PG you should use function that returns a table : http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/ – Jaisus May 15 '19 at 13:36

0 Answers0