RE: pgsql-sql-digest V1 #281 - Mailing list pgsql-sql
From | Dionisio Barrantes |
---|---|
Subject | RE: pgsql-sql-digest V1 #281 |
Date | |
Msg-id | 002901bed20a$67109b00$8e7debc3@teleline.es Whole thread Raw |
List | pgsql-sql |
Sorry. I want unsuscribre from this mail list, but I don't know how ? ----- Original Message ----- From: pgsql-sql-digest <owner-pgsql-sql-digest@hub.org> To: <pgsql-sql-digest@hub.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@oumail.openu.ac.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@oumail.openu.ac.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 > ******************************* >