|
How is "COLLEEN" not there and there
at the same time? --------------------------------------------------------------------------------------------- NOTICE: did not = 11 K = 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 ; bname varchar(100) ; --business.conformedname%TYPE ; Word varchar(100) ; Word2 varchar(100) ; Wcount INTEGER ; I BIGINT DEFAULT 0 ; J BIGINT 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 _________________________ |
