Thread: I guess I'm missing something here WRT FOUND

I guess I'm missing something here WRT FOUND

From
Ralph Smith
Date:
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
_________________________

Re: I guess I'm missing something here WRT FOUND

From
Alban Hertroys
Date:
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
ofthis 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!



Re: I guess I'm missing something here WRT FOUND

From
Rob Sargent
Date:

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?

Re: I guess I'm missing something here WRT FOUND

From
Ralph Smith
Date:
<tt>Yeah your right Alban, that looks bad, but it was an artifact of 'try-this, try-this, no, try-this'.<br /><br />
Thetable is empty, and unfortunately remains that way; nothing gets inserted.<br /> I tried other variations, however
FOUNDjust isn't behaving as I would think.<br /><br /> -----------------------------------------------<br /> OUTPUT
SNIPPET:<br/> NOTICE:  SQL cmd is = SELECT COUNT(*) FROM zbus_tokens WHERE token = PARKING<br /> NOTICE:  Row = 10,  
SkippedINSERT Count = 32,   Word2 = PARING<br /> NOTICE:  SQL cmd is = SELECT COUNT(*) FROM zbus_tokens WHERE token =
COLLEEN<br/> NOTICE:  Row = 11,   Skipped INSERT Count = 33,   Word2 = COLLEEN<br /><br
/></tt><tt>-----------------------------------------------</tt><br/><tt>    Alban Hertroys wrote:</tt><br /><tt>       
On9 Nov 2010, at 5:11, Ralph Smith wrote:</tt><br /><tt></tt><br /><tt>        Why is FOUND 'finding' and hence
avoidingan INSERT?<br />         <br />     Not really sure what your point is (don't have time to look closely),
but...<br/><br />         PERFORM Rnotice1(1,''SQL cmd is'',''SELECT COUNT(*) FROM zbus_tokens WHERE token =
''||Word2::varchar);</tt><br /><tt>        PERFORM (SELECT COUNT(*) FROM zbus_tokens WHERE token = Word2) ;</tt><br
/><tt>       IF NOT FOUND THEN</tt><br /><tt>          PERFORM RNotice1(1,''value'',Word2) ;</tt><br /><tt>         
INSERTINTO zbus_tokens (token) values(Word2); </tt><br /><tt>          J=J+1 ;</tt><br /><tt>          IF J % 100 = 0
THEN</tt><br/><tt>            PERFORM Rnotice2(1,''Row'',I,''Insert Count'',J) ;</tt><br /><tt>          END IF
;</tt><br/><tt>        ELSE</tt><br /><tt>          K=K+1 ;</tt><br /><tt>          PERFORM
RNotice2(1,''Row'',I,''SkippedINSERT Count'',K) ;</tt><br /><tt>        END IF ;<br />    You just connected this ELSE
blockto the IF statement it was nested inside.<br />    You probably need to comment out the rest of this ELSE block as
well.<br/><br /><br /></tt><tt>   Alban Hertroys<br /><br /></tt><tt>--<br /></tt><tt>Screwing up is an excellent way
toattach something to the ceiling.<br /> (Assuming you're not turning the screw driver the wrong way.)<br /><br
/></tt><tt><br/> -- <br /> Ralph<br /> _________________________</tt> 

Re: I guess I'm missing something here WRT FOUND

From
Tom Lane
Date:
Ralph Smith <rsmith@10kinfo.com> writes:
> <tt>Yeah 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