Re: [GENERAL] I guess I'm missing something here WRT FOUND
On 11/08/2010 09:11 PM, Ralph Smith wrote: How is COLLEEN not there and there at the same time? - NOTICE: did not = 11K = 42 CONTEXT: PL/pgSQL function get_word line 37 at perform NOTICE: value = COLLEEN CONTEXT: PL/pgSQL function get_word line 29 at perform ERROR: duplicate key violates unique constraint uniq_tokens CONTEXT: PL/pgSQL function get_word line 30 at SQL statement # /* Generate a list of up to 7 tokens from the business table's conformedname field. Strip off leading and trailing commans and quotes, etc. Results are inserted into table zbus_tokens, not sorted. */ CREATE OR REPLACE FUNCTION get_word() RETURNS VOID AS ' DECLARE business business%ROWTYPE ; bnamevarchar(100) ; --business.conformedname%TYPE ; Word varchar(100) ; Word2varchar(100) ; Wcount INTEGER ; IBIGINT DEFAULT 0 ; JBIGINT DEFAULT 0 ; K BIGINT DEFAULT 0 ; IsThere INT ; BEGIN FOR business IN SELECT * FROM business limit 500 LOOP bname=business.conformedname ; I=I+1 ; FOR Wcount IN 1..7 LOOP Word=split_part(bname,'' '',Wcount) ; Word2=ltrim(Word,''!?.%()+$*/0123456789'') ; Word=rtrim(Word2,''!?.()+$*/0123456789'') ; Word2=rtrim(ltrim(Word,'',''),'','') ; Word=rtrim(ltrim(Word2,),) ; IF LENGTH(Word)0 THEN Word2=substring(Word from 1 for 50) ; -- PERFORM SELECT COUNT(*) FROM zbus_tokens WHERE token = Word2 ; -- IF FOUND THEN PERFORM RNotice1(1,''value'',Word2) ;-- line 29 INSERT INTO zbus_tokens (token) values(Word2); J=J+1 ; IF J % 100 = 0 THEN PERFORM Rnotice2(1,''Row'',I,''Inserts'',J) ; END IF ; ELSE K=K+1 ; PERFORM RNotice2(1,''did not'',I,''K'',K) ; -- line 37 -- END IF ; END IF ; END LOOP ; END LOOP ; RETURN ; END ; ' LANGUAGE plpgsql; -- == SELECT get_word (); SELECT token, count(token) from zbus_tokens group by 1 order by 2 desc ; SELECT count(*) from zbus_tokens where token='COLLEEN; drop function get_word() ; truncate zbus_tokens ; drop table zbus_tokens; create table zbus_tokens (id bigserial, token varchar(50), CONSTRAINT uniq_tokens UNIQUE (token)) ; === DOCTOR FINN'S CARD COMPANY SPECIALTY MAINTENANCE RIVERS LANDING RESTAURANT SEATTLE FUSION FC PROFESSIONAL PRACTICE ENVIRONMENTS INC CELEBRATE YOURSELF NEW ACTIVITEA BEVERAGE CO KARY ADAM HORWITZ JOHN CASTRO MAGICIAN RELIABLE AUTO RENTAL PARKING COLLEEN CASEY, LMP COLLEEN CASEY, LMP THANKS! Again, 7.4 BITES! -- Ralph _ I'm wondering if count(*) isn't ALWAYS found? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I guess I'm missing something here WRT FOUND
Yeah your right Alban, that looks bad, but it was an artifact of 'try-this, try-this, no, try-this'. The table is empty, and unfortunately remains that way; nothing gets inserted. I tried other variations, however FOUND just isn't behaving as I would think. --- OUTPUT SNIPPET: NOTICE: SQL cmd is = SELECT COUNT(*) FROM zbus_tokens WHERE token = PARKING NOTICE: Row = 10, Skipped INSERT Count = 32, Word2 = PARING NOTICE: SQL cmd is = SELECT COUNT(*) FROM zbus_tokens WHERE token = COLLEEN NOTICE: Row = 11, Skipped INSERT Count = 33, Word2 = COLLEEN --- Alban Hertroys wrote: On 9 Nov 2010, at 5:11, Ralph Smith wrote: Why is FOUND 'finding' and hence avoiding an INSERT? Not really sure what your point is (don't have time to look closely), but... PERFORM Rnotice1(1,''SQL cmd is'',''SELECT COUNT(*) FROM zbus_tokens WHERE token = ''||Word2::varchar) ; PERFORM (SELECT COUNT(*) FROM zbus_tokens WHERE token = Word2) ; IF NOT FOUND THEN PERFORM RNotice1(1,''value'',Word2) ; INSERT INTO zbus_tokens (token) values(Word2); J=J+1 ; IF J % 100 = 0 THEN PERFORM Rnotice2(1,''Row'',I,''Insert Count'',J) ; END IF ; ELSE K=K+1 ; PERFORM RNotice2(1,''Row'',I,''Skipped INSERT Count'',K) ; END IF ; You just connected this ELSE block to the IF statement it was nested inside. You probably need to comment out the rest of this ELSE block as well. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. (Assuming you're not turning the screw driver the wrong way.) -- Ralph _
[Fwd: Re: [GENERAL] I guess I'm missing something here WRT FOUND]
Yeah your right Alban, that looks bad, but it was an artifact of 'try-this, try-this, no, try-this'. The table is empty, and unfortunately remains that way; nothing gets inserted. I tried other variations, however FOUND just isn't behaving as I would think. --- OUTPUT SNIPPET: NOTICE: SQL cmd is = SELECT COUNT(*) FROM zbus_tokens WHERE token = PARKING NOTICE: Row = 10, Skipped INSERT Count = 32, Word2 = PARING NOTICE: SQL cmd is = SELECT COUNT(*) FROM zbus_tokens WHERE token = COLLEEN NOTICE: Row = 11, Skipped INSERT Count = 33, Word2 = COLLEEN --- Alban Hertroys wrote: On 9 Nov 2010, at 5:11, Ralph Smith wrote: Why is FOUND 'finding' and hence avoiding an INSERT? Not really sure what your point is (don't have time to look closely), but... PERFORM Rnotice1(1,''SQL cmd is'',''SELECT COUNT(*) FROM zbus_tokens WHERE token = ''||Word2::varchar) ; PERFORM (SELECT COUNT(*) FROM zbus_tokens WHERE token = Word2) ; IF NOT FOUND THEN PERFORM RNotice1(1,''value'',Word2) ; INSERT INTO zbus_tokens (token) values(Word2); J=J+1 ; IF J % 100 = 0 THEN PERFORM Rnotice2(1,''Row'',I,''Insert Count'',J) ; END IF ; ELSE K=K+1 ; PERFORM RNotice2(1,''Row'',I,''Skipped INSERT Count'',K) ; END IF ; You just connected this ELSE block to the IF statement it was nested inside. You probably need to comment out the rest of this ELSE block as well. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. (Assuming you're not turning the screw driver the wrong way.) -- Ralph _ -- Ralph _ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I guess I'm missing something here WRT FOUND
Ralph Smith rsm...@10kinfo.com writes: ttYeah your right Alban, that looks bad, but it was an artifact of 'try-this, try-this, no, try-this'.br br The table is empty, and unfortunately remains that way; nothing gets inserted.br I tried other variations, however FOUND just isn't behaving as I would think.br (Please avoid html-encoded email.) The original mail looked like you were trying to do perform count(*) from something where something; if found then ... This will in fact *always* set FOUND, because the query always yields exactly one row: that's the nature of aggregate functions. FOUND doesn't respond to whether the result of count(*) was zero or nonzero, but just to the fact that it did deliver a result row. You probably wanted something like perform 1 from something where something; if found then ... which will set FOUND depending on whether there are any rows matching the where-clause. Or you could avoid FOUND altogether: if exists(select 1 from something where something) then ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] I guess I'm missing something here WRT FOUND
How is COLLEEN not there and there at the same time? - NOTICE: did not = 11K = 42 CONTEXT: PL/pgSQL function get_word line 37 at perform NOTICE: value = COLLEEN CONTEXT: PL/pgSQL function get_word line 29 at perform ERROR: duplicate key violates unique constraint uniq_tokens CONTEXT: PL/pgSQL function get_word line 30 at SQL statement # /* Generate a list of up to 7 tokens from the business table's conformedname field. Strip off leading and trailing commans and quotes, etc. Results are inserted into table zbus_tokens, not sorted. */ CREATE OR REPLACE FUNCTION get_word() RETURNS VOID AS ' DECLARE business business%ROWTYPE ; bnamevarchar(100) ; --business.conformedname%TYPE ; Word varchar(100) ; Word2varchar(100) ; Wcount INTEGER ; IBIGINT DEFAULT 0 ; JBIGINT DEFAULT 0 ; K BIGINT DEFAULT 0 ; IsThere INT ; BEGIN FOR business IN SELECT * FROM business limit 500 LOOP bname=business.conformedname ; I=I+1 ; FOR Wcount IN 1..7 LOOP Word=split_part(bname,'' '',Wcount) ; Word2=ltrim(Word,''!?.%()+$*/0123456789'') ; Word=rtrim(Word2,''!?.()+$*/0123456789'') ; Word2=rtrim(ltrim(Word,'',''),'','') ; Word=rtrim(ltrim(Word2,),) ; IF LENGTH(Word)0 THEN Word2=substring(Word from 1 for 50) ; -- PERFORM SELECT COUNT(*) FROM zbus_tokens WHERE token = Word2 ; -- IF FOUND THEN PERFORM RNotice1(1,''value'',Word2) ;-- line 29 INSERT INTO zbus_tokens (token) values(Word2); J=J+1 ; IF J % 100 = 0 THEN PERFORM Rnotice2(1,''Row'',I,''Inserts'',J) ; END IF ; ELSE K=K+1 ; PERFORM RNotice2(1,''did not'',I,''K'',K) ; -- line 37 -- END IF ; END IF ; END LOOP ; END LOOP ; RETURN ; END ; ' LANGUAGE plpgsql; -- == SELECT get_word (); SELECT token, count(token) from zbus_tokens group by 1 order by 2 desc ; SELECT count(*) from zbus_tokens where token='COLLEEN; drop function get_word() ; truncate zbus_tokens ; drop table zbus_tokens; create table zbus_tokens (id bigserial, token varchar(50), CONSTRAINT uniq_tokens UNIQUE (token)) ; === DOCTOR FINN'S CARD COMPANY SPECIALTY MAINTENANCE RIVERS LANDING RESTAURANT SEATTLE FUSION FC PROFESSIONAL PRACTICE ENVIRONMENTS INC CELEBRATE YOURSELF NEW ACTIVITEA BEVERAGE CO KARY ADAM HORWITZ JOHN CASTRO MAGICIAN RELIABLE AUTO RENTAL PARKING COLLEEN CASEY, LMP COLLEEN CASEY, LMP THANKS! Again, 7.4 BITES! -- Ralph _
Re: [GENERAL] I guess I'm missing something here WRT FOUND
On 9 Nov 2010, at 5:11, Ralph Smith wrote: How is COLLEEN not there and there at the same time? Not really sure what your point is (don't have time to look closely), but... IF LENGTH(Word)0 THEN Word2=substring(Word from 1 for 50) ; -- PERFORM SELECT COUNT(*) FROM zbus_tokens WHERE token = Word2 ; -- IF FOUND THEN PERFORM RNotice1(1,''value'',Word2) ;-- line 29 INSERT INTO zbus_tokens (token) values(Word2); J=J+1 ; IF J % 100 = 0 THEN PERFORM Rnotice2(1,''Row'',I,''Inserts'',J) ; END IF ; ELSE K=K+1 ; PERFORM RNotice2(1,''did not'',I,''K'',K) ; -- line 37 -- END IF ; You just connected this ELSE block to the IF statement it was nested inside. You probably need to comment out the rest of this ELSE block as well. END IF ; Again, 7.4 BITES! Well, 8 is better, but 7.4 was pretty ok. I think you're blaming your own error on the database here ;) Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4cd8fdd810262051411171! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
