Re: [SQL] Funtion to clean up strings?

, but not both. This is the standard format for phone numbers. Parenthesized digits -- as you suspected -- represent digits which must only be dialled when using the number locally and must be omitted from outside. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained

Re: [SQL] regexp_replace and UTF8

. You could use a plperl function to use one of the many html parsing perl modules? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your

Re: [SQL] Subqueries

. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] REFERENCES and INHERITS restrictions?

. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL

Re: [SQL] Check before INSERT INTO

can certainly change the SQL all you like, or on the server where you can have triggers which change the values being stored or executing additional queries. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support

Re: [SQL] Check before INSERT INTO

=' n_sheet ' AND TOT_N_SHEET=' tot_n_sheet ') For what it's worth your script is a security hole. Look into using query parameters which in ASP will probably be represented by ?. The method above will allow hackers to get direct access to your database and do nasty things. -- Gregory Stark

Re: [SQL] Sql ORDER BY and ASC/DESC question

an opaque expression. It won't be able to use any indexes on these columns. Also, incidentally you might want to use text strings instead of integer labels. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL

Re: [SQL] Sql ORDER BY and ASC/DESC question

Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: ORDER BY CASE ? WHEN 1 THEN name ASC Uh, no, putting the ASC/DESC decoration inside a CASE like that is not gonna work doh! I had a feeling something was wrong but couldn't put my finger on it before I hit

Re: [SQL] UTF8 encoding and non-text data types

was invented in the Arabic world after all). But other writing systems have some pretty baroque notations which would be far more difficult to convert. If anything I would expect this kind of conversion to live in the same place as things like roman numerals or other more flexible formatting. -- Gregory

Re: [SQL] marking tuples

cases though they are used by the direct parent of the node which added it, so the planner can just mark a field in the parent indicating which column it should look at for the flag. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand

Re: [SQL] pg_clog (?) problem with VACUMM

to fill the file with nul bytes though. Something like dd if=/dev/zero of= bs=1k count=nnn where nnn is, uh, I'm not sure how large, it won't take much to cover transactionid 118 though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand

Re: [SQL] postgres bogged down beyond tolerance

) so you don't have to run sysctl every time you reboot. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] functions are returns columns

int) returns setof record(int,int) AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; The return type if present has to match the OUT (and BOTH) parameters. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me

Re: [SQL] functions are returns columns

Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: You're almost there: CREATE FUNCTION getfoo (IN int, OUT int, OUT int) returns setof record(int,int) AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; Not quite --- it's just returns

Re: [SQL] Returning the total number of rows as a separate column when using limit

catalogs quite frequently. They're not really well suited for this task. Alternatively you could create a cursor and play with that. But I don't think that's a great solution either. (yet? I think cursors are getting more useful in Postgres, perhaps it will be eventually.) -- Gregory Stark

Re: [SQL] Returning the total number of rows as a separate column when using limit

snapshot-info in the index, so counting only involves the index AFAIK. Well that's only going to be true if the index satisfies the whole query which is not going to be true for the simplest cases. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's

Re: [SQL] ERROR: failed to re-find parent key in pk_ep07

has been seen before... It was fixed in these bug-fix releases: 7.4.15, 8.0.10, and 8.1.6 which were released on August 1st of this year. There have actually been 3 to 4 more bug-fix releases since those too. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com

Re: [SQL] Speeding up schema changes

Decibel! [EMAIL PROTECTED] writes: On Sep 3, 2007, at 7:26 AM, Gregory Stark wrote: Also, incidentally do you have a good reason to use CHAR instead of varchar or text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you don't store anything more in it. text or varchar

Re: [SQL] Extracting hostname from URI column

be bought directly under .ca like amazon.ca. I think you'll have to store a specific list of tlds and how deep you want to look. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget

Re: [SQL] Index usage in bitwise operation context

for a GIST index method for varbit which would be superior to both of the above tactics. I'm still not sure it would be able to handle a join clause though, but maybe? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [SQL] Speeding up schema changes

(actually 68 bytes in 8.2) even if you don't store anything more in it. text or varchar will take only as many bytes as the data you're storing (plus 4 bytes). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [SQL] Incomprehensible dogged sort in Merge Join

) (actual time=0.051..1474.143 rows=547796 loops=1) gesundheit. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] SELECT syntax synopsis: column_definition?

postgres=# select * from testf() as (i integer); i --- 1 (1 row) I haven't quite figured out how this is useful though. It probably makes more sense if you use plpgsql but I still don't quite see what the use case is. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com

Re: [SQL] PG won't use index on ORDER BY expression

is the one you want for handling ordering. The pattern_ops operator set is for handling things like x LIKE 'foo%' -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ

Re: [SQL] PG won't use index on ORDER BY expression

and the pattern order are different. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] The nested view from hell - Restricting a subquerry

max(invoice_id) sum(mileage) 1 1 100 Whereas the correct output would be to output no records at all. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [SQL] The nested view from hell - Restricting a subquerry

Gregory Stark [EMAIL PROTECTED] writes: Nis Jørgensen [EMAIL PROTECTED] writes: 1. Look up all order_ids for which (order_id,my_invoice_id) appear in eg_orders 2. Find all rows (in both branches of the UNION) with these id_s Oh, did you mean look up the order_ids for which

Re: [SQL] The nested view from hell - Restricting a subquerry

that information. The more I look at this view the more I think it's just seriously broken. Why is it grouping by order_id at all if, I suspect, there will only be one record per order_id in eg_orders?? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end

Re: [SQL] Converting from MS Access field aliases

in the resulting output columns, not in scope for the where clause. In fact the expressions used aren't even evaluated for rows which don't match the where clause which is pretty important if those expressions are subqueries or volatile functions. -- Gregory Stark EnterpriseDB http

Re: [SQL] percentages of a column

)::text||'%' as % from basket group by fruit_name order by %; fruit_name | count | % +---+- Bananas| 5 | 10% Apples |15 | 30% Oranges|30 | 60% (3 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com

Re: [SQL] trigger firing order

if the trigger on B doesn't actually get triggered until someone updates B and it's the trigger on A doing the update? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support

Re: [SQL] JOIN

there are multiple join matches. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] aggregate query

then bay_5_use else null end AS bay_use FROM ( SELECT *, generate_series(1,number_of_bays) AS bay FROM survey_load ) as x -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [SQL] Recursively traversing a partially ordered set

they didn't expect. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] query to select a linked list

denormalizing it slightly which makes it hard to reparent children, but if that isn't an operation you have to support I think it makes most other operations you might want to do much easier to support. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com

Re: [SQL] query to select a linked list

tried using them. I would think that connectby is at least worth looking into. Uhm, no, I guess I'm not sure. I didn't realize it was in the tablefunc module either. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [SQL] Create Assertion -- Question from a newbie

Alvaro Herrera [EMAIL PROTECTED] writes: Well, it's on the SQL standard, so we should look forward to implementing (some form of) it some day. Do *any* databases implement SQL standard Assertions? -- greg ---(end of broadcast)--- TIP 5: don't