Обсуждение: Advisable to move SQL from PHP to functions?
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>
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@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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>
Rory, > 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. Well, let's put it this way: I've supported a thriving small business, for the past two years building applications *only* that way. > 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. Actually, it is significantly impaired in 7.2.x. You should force an upgrade; 7.3.x has been out for 6+ months and 7.4 is due out soon. Also 7.3 has better performance and debugging tools than 7.2. If you're upagainst a wall, you can do what I did for my 7.2 apps: write functions returning TEXT and have them return a query to the PHP, which it then executes. -- -Josh Berkus Aglio Database Solutions San Francisco
I'm getting my feet wet using PostgreSQL and have run in to a bit of a
snag with using the + operator.
I want to create a table that has a third column that is the sum of the
values of two other columns. In essence:
SELECT a.qty AS aa,
b.qty AS bb,
a.qty + b.qty AS cc
FROM ...
The FROM is a subquery that includes a number of full joins, so there
are occasions when a.qty or b.qty are NULL.
The select statement seems to work just fine, except that where a.qty
or b.qty are NULL, cc is also NULL. (I can see how this might not work,
PostgreSQL protesting, "Okay, you want me to add this and ... and ...
and what, exactly?" But maybe I've misconceived what the problem is.)
I've googled for "postgresql mathematical operator null argument",
taken a gander at the html help files included with the PostgreSQL
installation and through Practical PostgreSQL in the sections on
mathematical operators, but didn't find anything that mentions this.
I'm probably not looking in the right places.
If someone point me in the direction I should look, I'd really
appreciate it.
Thanks for your time!
Michael Glaesemann
grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes:
> SELECT a.qty AS aa,
> b.qty AS bb,
> a.qty + b.qty AS cc
> FROM ...
> The select statement seems to work just fine, except that where a.qty
> or b.qty are NULL, cc is also NULL.
Yup, that's how it should be according to the SQL spec. NULL is
effectively "unknown", so the result of adding it to anything else
is also unknown, ie NULL.
If you'd prefer to substitute something else, for either a.qty or b.qty
individually or the complete sum, see the COALESCE function.
"COALESCE(foo, bar)" means "if foo is not null then foo else bar".
(No, it's not a well-chosen function name ... we can blame the SQL
spec for this too ...)
regards, tom lane
According to the ANSI SQL-92 specification:
" 1) If the value of any <numeric primary> simply contained in a
<numeric value expression> is the null value, then the result of
the <numeric value expression> is the null value."
Here is a link to the link for the ANSI SQL-92 specification:
http://luuk.xs4all.nl/sql/sqlref.htm#sql92
My understanding is that any intra-row functions in which one of the
operators is a null will yield a null. Something that is confusing is that
inter-row functions may or may not yield a null.
That is
SELECT SUM(numvar) FROM tablex
Will treat any numvar which are null as if they were a zero. However, the
following will not treat it as zero but non-existent.
SELECT COUNT(numvar) FROM table x
Will pretend that any null numvar is non existent and will not count it.
Nulls are not part of relational theory and as far as I know the rules seem
to be to do whatever seems useful for most users. It should be noted that
Codd did try to fit nulls into relational theory using "three valued logic".
I don't think this was widely excepted.
Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"
www.UrbanaSoft.com
----- Original Message -----
From: "Michael Glaesemann" <grzm@myrealbox.com>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, May 28, 2003 10:58 PM
Subject: [NOVICE] + operator with a possible NULL operand
> I'm getting my feet wet using PostgreSQL and have run in to a bit of a
> snag with using the + operator.
>
> I want to create a table that has a third column that is the sum of the
> values of two other columns. In essence:
>
> SELECT a.qty AS aa,
> b.qty AS bb,
> a.qty + b.qty AS cc
> FROM ...
>
> The FROM is a subquery that includes a number of full joins, so there
> are occasions when a.qty or b.qty are NULL.
>
> The select statement seems to work just fine, except that where a.qty
> or b.qty are NULL, cc is also NULL. (I can see how this might not work,
> PostgreSQL protesting, "Okay, you want me to add this and ... and ...
> and what, exactly?" But maybe I've misconceived what the problem is.)
>
> I've googled for "postgresql mathematical operator null argument",
> taken a gander at the html help files included with the PostgreSQL
> installation and through Practical PostgreSQL in the sections on
> mathematical operators, but didn't find anything that mentions this.
> I'm probably not looking in the right places.
>
> If someone point me in the direction I should look, I'd really
> appreciate it.
>
> Thanks for your time!
>
> Michael Glaesemann
> grzm myrealbox com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
On Thursday, May 29, 2003, at 15:38 Asia/Tokyo, Tom Lane wrote: > Michael Glaesemann <grzm@myrealbox.com> writes: >> SELECT a.qty AS aa, >> b.qty AS bb, >> a.qty + b.qty AS cc >> FROM ... > >> The select statement seems to work just fine, except that where a.qty >> or b.qty are NULL, cc is also NULL. > > Yup, that's how it should be according to the SQL spec. NULL is > effectively "unknown", so the result of adding it to anything else > is also unknown, ie NULL. Tom (and Vincent), thanks for the confirmation of what I suspected. It's definitely good to know. And so fast! I get up to go to lunch, come back, and there's my answer. Wow. I'm just hoping someday I'll know enough to be able to contribute a bit back. > If you'd prefer to substitute something else, for either a.qty or b.qty > individually or the complete sum, see the COALESCE function. After a brief tour through the PostgreSQL documention (COALESCE isn't in the index) and subsequent modifications to my query, everything is golden! Thanks so much. I'm suspecting I'm going to use COALESCE (arg, 0) quite a bit. Ripe for a custom function or operator, I'm thinking. :) Thanks again! Michael Glaesemann grzm myrealbox com
On Thursday, May 29, 2003, at 15:38 Asia/Tokyo, Tom Lane wrote: > Michael Glaesemann <grzm@myrealbox.com> writes: >> SELECT a.qty AS aa, >> b.qty AS bb, >> a.qty + b.qty AS cc >> FROM ... > >> The select statement seems to work just fine, except that where a.qty >> or b.qty are NULL, cc is also NULL. > > Yup, that's how it should be according to the SQL spec. NULL is > effectively "unknown", so the result of adding it to anything else > is also unknown, ie NULL. Tom (and Vincent), thanks for the confirmation of what I suspected. It's definitely good to know. And so fast! I get up to go to lunch, come back, and there's my answer. Wow. I'm just hoping someday I'll know enough to be able to contribute a bit back. > If you'd prefer to substitute something else, for either a.qty or b.qty > individually or the complete sum, see the COALESCE function. After a brief tour through the PostgreSQL documention (COALESCE isn't in the index) and subsequent modifications to my query, everything is golden! Thanks so much. I'm suspecting I'm going to use COALESCE (arg, 0) quite a bit. Ripe for a custom function or operator, I'm thinking. :) Thanks again! Michael Glaesemann grzm myrealbox com
On Wed, May 28, 2003 at 23:43:19 -0700, Vincent Hikida <vhikida@inreach.com> wrote: > > That is > > SELECT SUM(numvar) FROM tablex > > Will treat any numvar which are null as if they were a zero. However, the > following will not treat it as zero but non-existent. Aggragates (except for count(*)) will skip any row for which the expression is null. For sum() that turns out to be equivalent to treating the number as zero because of the way math works. But the function isn't actually treating nulls as zeros.
You're right of course!! Small glitch in my program but I think it's been debugged now. Sorry, ... I just got back from seeing the Matrix. Vincent Hikida, Member of Technical Staff - Urbana Software, Inc. "A Personalized Learning Experience" www.UrbanaSoft.com ----- Original Message ----- From: "Bruno Wolff III" <bruno@wolff.to> To: "Vincent Hikida" <vhikida@inreach.com> Cc: "Michael Glaesemann" <grzm@myrealbox.com>; <pgsql-novice@postgresql.org> Sent: Thursday, May 29, 2003 4:16 AM Subject: Re: [NOVICE] + operator with a possible NULL operand > On Wed, May 28, 2003 at 23:43:19 -0700, > Vincent Hikida <vhikida@inreach.com> wrote: > > > > That is > > > > SELECT SUM(numvar) FROM tablex > > > > Will treat any numvar which are null as if they were a zero. However, the > > following will not treat it as zero but non-existent. > > Aggragates (except for count(*)) will skip any row for which the expression > is null. For sum() that turns out to be equivalent to treating the number > as zero because of the way math works. But the function isn't actually > treating nulls as zeros. >