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

From: "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com>
To: "PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Subject: Any risks in using FUNCTIONs (stored procedures) instead of raw sql queries?
Date: 2008-11-18 18:18:36
Message-ID: e373d31e0811181018v8abf852o9f95e18be7cbb058@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am looking to convert all my database access code into stored
procedures in PL/PGSQL.

I have googled but it looks like there's a whole variety of
information from 2003 (when PG must have been quite different) until
now--some people find stored functions slow for web based apps, others
find it is worth the maintenance.

To me, performance is critical as a heavy web access is critical. This
has to scale too. I use PHP currently to do the following:

1. Create a connection
2. Take the submitted form info and check if it already exists in db (SQL1)
3. If exists, then update db with submitted info and return new values (SQL 2)
4. If not exists, then insert new record (SQL 2.1)
5. If insert/update went well, we get the new values otherwise an
"ERROR" string depending on what the error was
6. Close the connection

All this works very fast for now, and it's in a separate class in PHP
so it's okay in terms of maintenance.

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?

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

PK

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-11-18 18:37:53 Re: No serial type
Previous Message Tom Lane 2008-11-18 18:11:11 Re: FreeBSD 7 needing to allocate lots of shared memory