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 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).
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.
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>