Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table as argument in postgres function

Table as argument in postgres function

From
RAJIN RAJ K
Date:
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

Re: Table as argument in postgres function

From
Pavel Stehule
Date:
Hi

ne 19. 5. 2019 v 18:00 odesílatel RAJIN RAJ K <rajin89@gmail.com> napsal:
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

Regards,
Rajin

Re: Table as argument in postgres function

From
Pavel Stehule
Date:
Hi

ne 19. 5. 2019 v 18:00 odesílatel RAJIN RAJ K <rajin89@gmail.com> napsal:
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

Regards,
Rajin