Re: Call function with dynamic schema name

Hello

2012/1/15 IlGenna <alessio(dot)gennari78(at)gmail(dot)com>:
> Hi to everyone,
> I would like to use in my function (plpgsql or sql) dynamic schema name to
> execute query or to call other functions.
>
> For exemple in oracle is possible to excute query in this manner:
>
>
> SELECT * FROM &&SCHEMA_NAME..TABLE_NAME;
>
> Where I think &&SCHEMA_NAME. is a sessione variable.
>
> I found tath I can use dynamic SQL like this:
>
> execute 'select * from ' || schema_name || '.table_name';
>
>
> However, I would like to know if exist any other system to use dynamic
> schema name more similiar to Oracle. Another pl language is also ok.

There are no similar way to Oracle. You can set a search_path
variable, but you have to be careful, because cached plans in PL/pgSQL
can do some issues, when function is called again with different
search path.

Regards

Pavel Stehule

>
>
> Thank you very much.
>
>
> Alessio
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146721.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql