On 11/08/2010 09:11 PM, Ralph Smith wrote: > 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 > _________________________ > I'm wondering if "count(*)" isn't ALWAYS found? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
