Re: Table as argument in postgres function

Hi

ne 19. 5. 2019 v 18:00 odesílatel RAJIN RAJ K <rajin89(at)gmail(dot)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

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

Regards

Pavel

Regards,
> Rajin
>