Re: Advisable to move SQL from PHP to functions?

Rory,

> I'm about to embark on a new project using a fairly extensive set of sql
> queries, many of them using SQL/Postgres features like unions, left
> outer joins etc. I'd like to know if it is advisable to write many of
> the queries as views or functions in plpgsql rather than having a 1000
> line file to parse in php.

I think you already know the answer to this, or you wouldn't be asking.

> I haven't been using functions and triggers much (in fact the only
> function/trigger pair I use updates a timestamp when tables are
> updated). Is it possible to make a function to receive values and then
> make records in several tables with these values? Can an insert return
> the oid or serial number resulting from the insert? Can a "select" cause
> values to be updated on the selected rows (eg a "seen" field).

Yes to all but the last. For the last, you would have to replace the "select"
call with a call to a function, which could then do anything you wanted.
>From your questions, you probably want to use a "data-push function" approach
rather than a trigger/rule based approach. The former means that instead of
doing INSERT ..., UPDATE, or SELECT ... from the PHP client, you instead do
calls to functions.

> If it is advisable to work in postgres I'd be grateful to know if there
> is a sensible system for editing triggers and functions on a live
> database. At present I can see my triggers in pg_trigger but can't edit
> them; \df doesn't seem to list my functions at all. I'm looking for a
> technique compatible with my technique of using readline (in vi mode) on
> the psql prompt; \e takes me to vim.

A couple of options:
1) I keep copies of the source for all of my functions, triggers, views, etc.
in text files on CVS on my local server, and whenever I modify them I do it
from PSQL, loading from the file.

2) Use a GUI that includes function/trigger/view editing:
http://techdocs.postgresql.org/guides/GUITools

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco