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
_________________________
