Table as argument in postgres function

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

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

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,

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.

On this note, Snowflake has the ability to to parameterize object names
(see:
https://docs.snowflake.net/manuals/sql-reference/identifier-literal.html )

So you can do things like
SELECT col_a, col_b FROM identifier('a_table_name')
or as a bind variable
SELECT col_a, col_b FROM identifier($1)

Which is their way of avoiding SQL injection attacks in *some* circumstances.
Their implementation of it is a bit uneven, but it has proven useful for my
work.

I can see where this obviously would prevent the planning of a prepared
statement when a table name is a parameter, but the request comes up often
enough, and the benefits to avoiding SQL injection attacks are significant
enough that maybe we should try to enable it for one-off. I don't
necessarily think we need an identifier(string) function, a
'schema.table'::regclass would be more our style.

Is there anything preventing us from having the planner resolve object
names from strings?

po 20. 5. 2019 v 7:56 odesílatel Corey Huinker <corey.huinker@gmail.com>
napsal:

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.

On this note, Snowflake has the ability to to parameterize object names
(see:
https://docs.snowflake.net/manuals/sql-reference/identifier-literal.html )

So you can do things like
SELECT col_a, col_b FROM identifier('a_table_name')
or as a bind variable
SELECT col_a, col_b FROM identifier($1)

Which is their way of avoiding SQL injection attacks in *some* circumstances.
Their implementation of it is a bit uneven, but it has proven useful for my
work.

I can see where this obviously would prevent the planning of a prepared
statement when a table name is a parameter, but the request comes up often
enough, and the benefits to avoiding SQL injection attacks are significant
enough that maybe we should try to enable it for one-off. I don't
necessarily think we need an identifier(string) function, a
'schema.table'::regclass would be more our style.

Is there anything preventing us from having the planner resolve object
names from strings?

The basic problem is fact so when you use PREPARE, EXECUTE protocol, you
has not parameters in planning time.

Regards

Pavel

Is there anything preventing us from having the planner resolve object
names from strings?

The basic problem is fact so when you use PREPARE, EXECUTE protocol, you
has not parameters in planning time.

I agree that it defeats PREPARE as it is currently implemented with
PQprepare(), and it would never be meaningful to have a query plan that
hasn't finalized which objects are involved.

But could it be made to work with PQexecParams(), where the parameter
values are already provided?

Could we make a version of PQprepare() that takes an extra array of
paramValues for object names that must be supplied at prepare-time?

út 21. 5. 2019 v 9:04 odesílatel Corey Huinker <corey.huinker@gmail.com>
napsal:

Is there anything preventing us from having the planner resolve object
names from strings?

The basic problem is fact so when you use PREPARE, EXECUTE protocol, you
has not parameters in planning time.

I agree that it defeats PREPARE as it is currently implemented with
PQprepare(), and it would never be meaningful to have a query plan that
hasn't finalized which objects are involved.

But could it be made to work with PQexecParams(), where the parameter
values are already provided?

Could we make a version of PQprepare() that takes an extra array of
paramValues for object names that must be supplied at prepare-time?

I think so it is possible, but there is a question how much this design
uglify source code. Passing query parameters is maybe too complex already.

Second question. I am not sure if described feature is some different. ANSI
SQL 2016 knows Polymorphic table functions - looks like that. For me, I
would to see implementation of PTF instead increasing complexity of work
with parameters.

https://www.doag.org/formes/pubfiles/11270472/2019-SQL-Andrej_Pashchenko-Polymorphic_Table_Functions_in_18c_Einfuehrung_und_Beispiele-Praesentation.pdf