Home > mailing lists

I guess I'm missing something here WRT FOUND - Mailing list pgsql-general

From	Ralph Smith
Subject	I guess I'm missing something here WRT FOUND
Date	November 9, 2010 00:11:37
Msg-id	4CD8C9EB.9020701@10kinfo.com Whole thread Raw
Responses	Re: I guess I'm missing something here WRT FOUND Re: I guess I'm missing something here WRT FOUND
List	pgsql-general

Tree view

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
_________________________

pgsql-general by date:

From: Tom Lane
Date: 08 November 2010, 22:46:38
Subject: Re: Removing pgsql_tmp files

From: Victor Hooi
Date: 09 November 2010, 00:23:21
Subject: Porting from MS Access 2007 to PostgreSQL

I guess I'm missing something here WRT FOUND - Mailing list pgsql-general

Previous

Next