Re: Advisable to move SQL from PHP to functions?

Thanks very much for your email, Josh.

On 13/05/03, Josh Berkus (josh@agliodbs.com) wrote:

> > ...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'll take that to mean "yes, do it in functions and views"! I'm cautious
about doing a month's work and then having to move that work back into
php -- hence the question. I'm hoping that the function/view approach
will provide faster database access too.

> > I haven't been using functions and triggers much ...

> ...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.

OK, this makes sense. I'm not sure how to return multiple tuples though;
I'm hoping the ability to do this isn't limited on Postgresql 7.2.x;
most of the servers I have access have this version. I believe that the
ability to manage "Set Returning Functions" has been much improved in
7.3.

> > ...is [there] a sensible system for editing triggers and functions
> > on a live database...

> 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.

That makes sense. I use \i quite a bit too. Presumably you drop the
triggers/functions/views you are about to work on before you update
them? It seems a little frustrating that one can't get at the contents
of a trigger, view or function more easily though.

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

Thanks for the url. I tried pgaccess a while ago but didn't like all the
tables it made for itself in my databases!

Thanks again,
Rory

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>