Обсуждение: I guess I'm missing something here WRT FOUND

-- 

Ralph
_________________________

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!

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?

<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> 

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