Re: Passing a table as parameter

Hello

2011/3/21 Jon Smark <jon.smark@yahoo.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>