Re: dynamically generated SQL and planner/performance

Ivan Sergio Borgonovo wrote:
> I've a lot of code that should call different functions according to
> values in tables.
>
> something like:
>
>
> create table FuncName(Name varchar(10), arg int);
>
> insert into FuncName values('ciro',5);
> insert into FuncName values('pino',7);
> insert into FuncName values('nano',11);
>
> create or replace function ciro(arg int, out res int) as
> $$
> begin
> res:=arg;
> return;
> end;
> $$ language plpgsql;
>
> create or replace function pino(arg int, out res int) as
> $$
> begin
> res:=arg*2;
> return;
> end;
> $$ language plpgsql;
>
> create or replace function nano(arg int, out res int) as
> $$
> begin
> res:=arg*4;
> return;
> end;
> $$ language plpgsql;
>
>
> create or replace function FBuilder(out res int) as
> $$
> declare
> statement varchar(256);
> _Name varchar(10);
> _arg int;
> begin
> select into _Name, _arg Name, arg from FuncName order by random();
> statement := ' select * from ' || _Name || '(' || _arg || ')';
> execute statement into res;
> return;
> end;
> $$ language plpgsql;
>
> select * from FBuilder();
>
> I don't understand which impact will have on the planner, caching
> etc... generating the statement dynamically.
>
> Will the execution of ciro, pino e nano be affected?
> Or will just the plan for executing FBuilder statement be affected?

The execution plan of dynamic queries from PL/pgSQL will not be cached;
the query will be prepared at execution time every time you execute it.

The plan for executing "select * from FBuilder()" will not be affected
by the SQL statements you execute from within FBuilder().
All you can do to hint at the planner that calling FBuilder() will be
expensive is (from version 8.3 on) to include a COST clause in the
CREATE FUNCTION statement.

Unless the dynamic statements are complicated or are called very often,
I would not worry too much about the additional cost of preparing the statement.

Yours,
Laurenz Albe