Table as argument in postgres function

From: RAJIN RAJ K <rajin89(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Table as argument in postgres function
Date: 2019-05-19 16:00:23
Message-ID: CAC+XFJjUgPKsAdEZvWA3VrWV0F4Or1MWL6tib2N7Q3i+BeKH4w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

Hi,

I'm trying to convert SAP Hana procedures in PG and i'm not able to handle
below scenario in Postgres 11

Scenario: I want to pass a table (Multiple rows) to function and use it
inside as a temp table.

Sample Code:

create a table tbl_id (id int, name character varying (10));
insert few rows to tbl_id;
create a function myfun (in tt_table <How to define a table type here> )
begin
return setof table(few columns)
begin
as
select id,name into lv_var1,lv_var2;
from tt_table --> Want to use the input table
where id = <some value>;
return query
select *
from tbl2 where id in (select id from tt_table); --> Want to use the input
table
end;
I don't want to go with dynamic sql, is there any other way to declare a
table as input argument and use it a normal temp table inside the function
body?
--> Function invocation issue:
select * from myfun(tbl_id);
How to invoke a function by passing a table as argument?
Regards,
Rajin

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2019-05-19 16:20:29 Re: Table as argument in postgres function
Previous Message Stephen Frost 2019-05-19 15:59:04 Re: PROXY protocol support

Browse pgsql-sql by date

  From Date Subject
Next Message Pavel Stehule 2019-05-19 16:20:29 Re: Table as argument in postgres function
Previous Message Tom Lane 2019-05-19 01:17:35 Re: create index on a jsonb timestamp field?