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?
You can pass table name as text or table object id as regclass type.
inside procedure you should to use dynamic sql - execute statement. Generally you cannot to use a variable as table or column name ever.
Dynamic SQL is other mechanism - attention on SQL injection.
create or replace function foo(regclass)
returns setof record as $$
begin
return query execute format('select * from %s', $1); -- cast from regclass to text is safe
end;
$$ language plpgsql;
with text type a escaping is necessary
create or replace function foo(text)
returns setof record as $$
begin
return query execute format('select * from %I', $1); -- %I ensure necessary escaping against SQL injection
end;
$$ language plpgsql;
you need to call "setof record" function with special syntax
select * from foo('xxx') as (a int, b int);
Sometimes you can use polymorphic types, then the function will be different
create or replace function foo2(regclass, anyelement)
returns setof anyelement as $$
begin
return query execute format('select * from %s', $1); -- cast from regclass to text is safe
end;
$$ language plpgsql;
select * from foo2('xxx', null::xxx);
you can read some more in doc
Regards
Pavel