Re: Any risks in using FUNCTIONs (stored procedures) instead of raw sql queries?

On Wed, 2008-11-19 at 02:18 +0800, Phoenix Kiula wrote:
> I am looking to convert all my database access code into stored
> procedures in PL/PGSQL.

> But a DBA told me that it will be much better to do all of these
> things in a stored procedure as it may bring some performance
> benefits. He's an oracle DBA so I am not sure if the same applies to
> PG? Will a "function" that takes input values with 15 column data
> fields including two TEXT fields and then outputs perhaps an array of
> values to a PHP program be faster than 2-3 separate SQL queries issues
> from PHP?
>

Generally speaking, yes. A stored procedure will be faster, if nothing
else you don't have to deal with TCP delay. You will also want to make
sure you are utilizing a connection pool.

For database design, especially when dealing with web developers I find
it is always good to have them review:

http://www-01.ibm.com/support/docview.wss?uid=nas191f301ccd7abae2f862565c2007cf178

Joshua D. Drake

> Thanks for any input. Or please point me online to any resource that
> discusses this kind of info. I could not find any.
>
> PK
>
--