Thread: plpgsql and prep statements (performance and sql injection)

Hallo,

good reasons to use prep statements
seem
- performance
- some sql injection defence

but when i look at plpgsql
http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html
i see that sql statements are translated into prep statements,
and live (the plan) as long the db connection is open

so there seems nothing to be gained from using
prep statements inside a plpgsql function,
the only reason could be the lifecycle of the prep statements
(which u don't really control for the translated sql functions inside
plpgsql functions). So i would be nice to have an extra feature in
plpgsql functions like imm,sta,vol (planning) but then to control the
duration/lifetime of the translated statements inside the plpgsql
function.

also,
since the keyword 'EXECUTE' is ambivalent i don't see at this point how
u can execute a prep statement inside a plpgsql function.

Comments?

mvg,
Wim

Hello

there is not reason for using PP from plpgsql. PostgreSQL's prepared
statement are limited by session too.

Regards

Pavel Stehule

2011/7/6 Wim Bertels <wim.bertels@khleuven.be>:
> Hallo,
>
> good reasons to use prep statements
> seem
> - performance
> - some sql injection defence
>
> but when i look at plpgsql
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html
> i see that sql statements are translated into prep statements,
> and live (the plan) as long the db connection is open
>
> so there seems nothing to be gained from using
> prep statements inside a plpgsql function,
> the only reason could be the lifecycle of the prep statements
> (which u don't really control for the translated sql functions inside
> plpgsql functions). So i would be nice to have an extra feature in
> plpgsql functions like imm,sta,vol (planning) but then to control the
> duration/lifetime of the translated statements inside the plpgsql
> function.
>
> also,
> since the keyword 'EXECUTE' is ambivalent i don't see at this point how
> u can execute a prep statement inside a plpgsql function.
>
> Comments?
>
> mvg,
> Wim
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>