From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Advisable to move SQL from PHP to functions? |
Date: | 2003-05-13 20:20:24 |
Message-ID: | 20030513202024.GA6788@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Thanks very much for your email, Josh.
On 13/05/03, Josh Berkus (josh(at)agliodbs(dot)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(at)campbell-lange(dot)net>
<www.campbell-lange.net>
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-05-13 20:36:13 | Re: Advisable to move SQL from PHP to functions? |
Previous Message | Josh Berkus | 2003-05-13 19:32:45 | Re: can't alter user with the postgres user |