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