Passing a table as parameter

Hi,

Is there any way for a SQL or PL/pgSQL function to receive a table
as parameter? As an illustration, consider the dummy example below.
Note that functions get_from_data1 and get_from_data2 follow essentially
the same pattern; it would be nice to define instead a single polymorphic
function parameterised on the id and table. Is this possible?

Thanks in advance!
Jon

CREATE TABLE data1 (id int4, content text);
CREATE TABLE data2 (id int8, content text);

CREATE FUNCTION get_from_data1 (int4)
RETURNS SETOF text
LANGUAGE sql STABLE AS
$$
SELECT content FROM data1 WHERE id = $1;
$$;

CREATE FUNCTION get_from_data2 (int8)
RETURNS SETOF text
LANGUAGE sql STABLE AS
$$
SELECT content FROM data2 WHERE id = $1;
$$;

Hello

2011/3/21 Jon Smark <jon(dot)smark(at)yahoo(dot)com>:
> Hi,
>
> Is there any way for a SQL or PL/pgSQL function to receive a table
> as parameter?  As an illustration, consider the dummy example below.
> Note that functions get_from_data1 and get_from_data2 follow essentially
> the same pattern; it would be nice to define instead a single polymorphic
> function parameterised on the id and table.  Is this possible?
>
> Thanks in advance!
> Jon
>
>
> CREATE TABLE data1 (id int4, content text);
> CREATE TABLE data2 (id int8, content text);
>
>
> CREATE FUNCTION get_from_data1 (int4)
> RETURNS SETOF text
> LANGUAGE sql STABLE AS
> $$
>        SELECT content FROM data1 WHERE id = $1;
> $$;
>
>
> CREATE FUNCTION get_from_data2 (int8)
> RETURNS SETOF text
> LANGUAGE sql STABLE AS
> $$
>        SELECT content FROM data2 WHERE id = $1;
> $$;
>
>

you can pass a table name as parameter only:

CREATE FUNCTION foo(tablename text)
RETURNS SETOF text AS $$
BEGIN
RETURN QUERY EXECUTE 'SELECT content FROM ' || quote_ident(tablename);
END;
$$ LANGUAGE plpgsql;

Regards

Pavel Stehule

>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Mar 21, 2011, at 11:13 PM, Jon Smark wrote:

>
> Is there any way for a SQL or PL/pgSQL function to receive a table
> as parameter? As an illustration, consider the dummy example below.
> Note that functions get_from_data1 and get_from_data2 follow essentially
> the same pattern; it would be nice to define instead a single polymorphic
> function parameterised on the id and table. Is this possible?

Try Something like given below:
CREATE OR REPLACE FUNCTION tab_pass(text) returns setof numeric
as
$$
DECLARE
rec record;
BEGIN
for rec in execute 'select empno from '||$1||' where id = 1'
LOOP
return next rec.empno;
END LOOP;
return next rec.empno;
END;
$$ language plpgsql;

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com

2011/3/21 Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>:
>
> On Mar 21, 2011, at 11:13 PM, Jon Smark wrote:
>
>>
>> Is there any way for a SQL or PL/pgSQL function to receive a table
>> as parameter?  As an illustration, consider the dummy example below.
>> Note that functions get_from_data1 and get_from_data2 follow essentially
>> the same pattern; it would be nice to define instead a single polymorphic
>> function parameterised on the id and table.  Is this possible?
>
>
> Try Something like given below:
> CREATE OR REPLACE FUNCTION tab_pass(text) returns setof numeric
> as
> $$
>   DECLARE
>     rec record;
>   BEGIN
>      for rec in execute 'select empno from '||$1||' where id = 1'
>      LOOP
>          return next rec.empno;
>      END LOOP;
>      return next rec.empno;
>   END;
> $$ language plpgsql;

it can work too, but there is sql injection risk.

Do newer 'SELECT ... FROM ' || tabname || ' ...

Regards

Pavel Stehule

>
>
> Thanks & Regards,
> Vibhor Kumar
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> vibhor(dot)kumar(at)enterprisedb(dot)com
> Blog:http://vibhork.blogspot.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Mar 22, 2011, at 1:32 AM, Pavel Stehule wrote:

> it can work too, but there is sql injection risk.
>
> Do newer 'SELECT ... FROM ' || tabname || ' ...
>
> Regards
>
> Pavel Stehule

Yes true. Same with the following too:
CREATE FUNCTION foo(tablename text)
RETURNS SETOF text AS $$
BEGIN
RETURN QUERY EXECUTE 'SELECT content FROM ' || quote_ident(tablename);
END;
$$ LANGUAGE plpgsql;

To prevent from sql injection user can try with SQL Protect:
http://www.enterprisedb.com/docs/en/9.0/sqlprotect/Table%20of%20Contents.htm

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com

2011/3/21 Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>:
>
> On Mar 22, 2011, at 1:32 AM, Pavel Stehule wrote:
>
>> it can work too, but there is sql injection risk.
>>
>> Do newer 'SELECT ... FROM ' || tabname || ' ...
>>
>> Regards
>>
>> Pavel Stehule
>
> Yes true. Same with the following too:
> CREATE FUNCTION foo(tablename text)
> RETURNS SETOF text AS $$
> BEGIN
> RETURN QUERY EXECUTE 'SELECT content FROM ' || quote_ident(tablename);
> END;
> $$ LANGUAGE plpgsql;
>
> To prevent from sql injection user can try with SQL Protect:
> http://www.enterprisedb.com/docs/en/9.0/sqlprotect/Table%20of%20Contents.htm
>

simply thinks as using USAGE clause or functions quote_ident,
quote_literal are faster and absolutly secure :). Software like SQL
Protect is good for old unsecured applications but better do
development well.

Regards

Pavel Stehule

> Thanks & Regards,
> Vibhor Kumar
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> vibhor(dot)kumar(at)enterprisedb(dot)com
> Blog:http://vibhork.blogspot.com
>
>

On Mar 22, 2011, at 1:52 AM, Pavel Stehule wrote:

> simply thinks as using USAGE clause or functions quote_ident,
> quote_literal are faster and absolutly secure :). Software like SQL

I don't think usage of quote_ident in current requirement of user, would prevent sql injection.
Running sql multiple times, someone can guess the tabename which can give data:
ERROR: relation "am" does not exist
LINE 1: SELECT content FROM am ^QUERY: SELECT content FROM amCONTEXT: PL/pgSQL function "foo" line 2 at RETURN QUERY

SQL Protect will make above message something like given below:
ERROR: SQLPROTECT: Illegal Query: relations

Which stops user guessing relation.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com

2011/3/21 Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>:
>
> On Mar 22, 2011, at 1:52 AM, Pavel Stehule wrote:
>
>> simply thinks as using USAGE clause or functions quote_ident,
>> quote_literal are faster and absolutly secure :). Software like SQL
>
> I don't think usage of quote_ident in current requirement of user, would prevent sql injection.
> Running sql multiple times, someone can guess the tabename which can give data:
> ERROR:  relation "am" does not exist
> LINE 1: SELECT content FROM am ^QUERY:  SELECT content FROM amCONTEXT:  PL/pgSQL function "foo" line 2 at RETURN QUERY
>
> SQL Protect will make above message something like given below:
> ERROR:  SQLPROTECT: Illegal Query: relations
>

it is different view on security. When you have not a security gap,
then is irelevant if somebody has unlimited number of trials. SQL
Protect is "security by obscurity" - a logout can be a good sign for
blind injection.

well usage of quote_ident and quote_literal is a perfect protection
against sql injection. Wrong query doesn't mean a problem. Problem is
when attacker can change a semantic of SQL query.

Pavel

> Which stops user guessing relation.
>
> Thanks & Regards,
> Vibhor Kumar
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> vibhor(dot)kumar(at)enterprisedb(dot)com
> Blog:http://vibhork.blogspot.com
>
>