RE: pgsql-sql-digest V1 #281

Sorry. I want unsuscribre from this mail list, but I don't know how ?
----- Original Message -----
From: pgsql-sql-digest <owner-pgsql-sql-digest(at)hub(dot)org>
To: <pgsql-sql-digest(at)hub(dot)org>
Sent: Monday, July 05, 1999 5:00 AM
Subject: pgsql-sql-digest V1 #281

>
> pgsql-sql-digest Sunday, July 4 1999 Volume 01 : Number
281
>
>
>
> Index:
>
> Re: [SQL] Escaping w/i regular expressions
> Re: [SQL] Tricky SQL (?)
>
> ----------------------------------------------------------------------
>
> Date: Sun, 4 Jul 1999 14:09:33 +0300
> From: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
> Subject: Re: [SQL] Escaping w/i regular expressions
>
> At 21:07 +0300 on 30/06/1999, Peter Eisentraut wrote:
>
>
> > How do you escape a ' (apostrophe) within a regular expression, so it
does
> > not close off the string? Something like
> > ... ~ '[0-9.,\']'
> > does not seem to work.
> > (Pgsql 6.4.2)
>
> Yes, it does work:
>
> testing=> select * from test;
> nm
> - -------------------------------------------------------
> This is a regular sentence ending with a full stop.
> In this sentence there is a 'quoted' word.
> not a sentence
> Fi, fye, foe, fam - I smell the blood of an Englishman.
> There are 10,000 bottles of beer on the wall.
> (5 rows)
>
> testing=> select * from test where nm ~ '[0-9.,\']';
> nm
> - -------------------------------------------------------
> This is a regular sentence ending with a full stop.
> In this sentence there is a 'quoted' word.
> Fi, fye, foe, fam - I smell the blood of an Englishman.
> There are 10,000 bottles of beer on the wall.
> (4 rows)
>
> See how it didn't match the 'not a sentence'? Now, to make sure, I removed
> the '.' from your regular expression, so the only thing to match in the
> 'quoted' row was the quotes:
>
> testing=> select * from test where nm ~ '[0-9,\']';
> nm
> - -------------------------------------------------------
> In this sentence there is a 'quoted' word.
> Fi, fye, foe, fam - I smell the blood of an Englishman.
> There are 10,000 bottles of beer on the wall.
> (3 rows)
>
> Convinced?
>
> Herouth
>
> - --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
> ------------------------------
>
> Date: Sun, 4 Jul 1999 14:59:07 +0300
> From: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
> Subject: Re: [SQL] Tricky SQL (?)
>
> At 00:04 +0300 on 03/07/1999, Peter Eisentraut wrote:
>
>
> > Unfortunately, this doesn't work because subselects are not allowed in
the
> > target list. The current solution is to read in all credits and refunds
> > and have the application (some PHP, some Perl) do the summing and
> > filtering. But this doesn't only seem clumsy but it creates unneccessay
> > network traffic.
>
> It seems that the current solution would be to create a temporary table,
> where you dump the results of your internal query, and then do the
external
> select. Would be more efficient than doing it on the frontend (network
> traffic and all).
>
> Version 6.5 has a facility for naming temporary tables so that you don't
> have to worry about exclusive names in a multiuser environment. If you are
> using a previous version, you should worry about this only if more than
one
> person uses said query at a time. Otherwise you should use a preexisting
> table, and lock it prior to the query, which would mean another user needs
> to wait until the first user finishes the query.
>
> All that said, the solution would be [I haven't tested]:
>
> CREATE TEMP TABLE int_qry ( customer_nr int4, amount decimal(2) );
>
> BEGIN;
>
> INSERT INTO int_qry( customer_nr, amount)
> SELECT
> customer_nr, amount
> FROM credits
> UNION
> customer_nr, -amount
> FROM refunds;
>
> SELECT customer_nr, sum(amount)
> FROM int_qry
> GROUP BY customer_nr
> HAVING sum(amount) > 0;
>
> END;
>
> DROP TABLE int_qry;
>
> I inserted the BEGIN and END just to assert that the two operations are in
> fact a single operation. Since temporary tables only exist in one session,
> there is little danger of multiple queries messing with each other's
> results.
>
> Herouth
>
> - --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
> ------------------------------
>
> End of pgsql-sql-digest V1 #281
> *******************************
>