I guess I'm missing something here WRT FOUND

Started by Ralph Smithover 15 years ago5 messagesgeneral

rsmith@10kinfo.com

over 15 years ago

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 ;

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
_________________________

Alban Hertroys

dalroi@solfertje.student.utwente.nl

over 15 years ago

In reply to: Ralph Smith (#1)

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

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 of this 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!

Rob Sargent

robjsargent@gmail.com

over 15 years ago

In reply to: Ralph Smith (#1)

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

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?

Ralph Smith

rsmith@10kinfo.com

over 15 years ago

In reply to: Alban Hertroys (#2)

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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Yeah your right Alban, that looks bad, but it was an artifact of
'try-this, try-this, no, try-this'. 
 
The table is empty, and unfortunately remains that way; nothing gets
inserted. 
I tried other variations, however FOUND just isn't behaving as I would
think. 
 
----------------------------------------------- 
OUTPUT SNIPPET: 
NOTICE:  SQL cmd is = SELECT COUNT(*) FROM zbus_tokens WHERE token =
PARKING 
NOTICE:  Row = 10,   Skipped INSERT Count = 32,   Word2 = PARING 
NOTICE:  SQL cmd is = SELECT COUNT(*) FROM zbus_tokens WHERE token =
COLLEEN 
NOTICE:  Row = 11,   Skipped INSERT Count = 33,   Word2 = COLLEEN 
 
</tt><tt>-----------------------------------------------</tt> 
<tt>    Alban Hertroys wrote:</tt> 
<tt>        On 9 Nov 2010, at 5:11, Ralph Smith wrote:</tt> 
<tt></tt> 
<tt>        Why is FOUND 'finding' and hence avoiding an INSERT? 
        
    Not really sure what your point is (don't have time to look
closely), but... 
 
        PERFORM Rnotice1(1,''SQL cmd is'',''SELECT COUNT(*) FROM
zbus_tokens WHERE token = ''||Word2::varchar) ;</tt> 
<tt>        PERFORM (SELECT COUNT(*) FROM zbus_tokens WHERE token =
Word2) ;</tt> 
<tt>        IF NOT FOUND THEN</tt> 
<tt>          PERFORM RNotice1(1,''value'',Word2) ;</tt> 
<tt>          INSERT INTO zbus_tokens (token) values(Word2); </tt> 
<tt>          J=J+1 ;</tt> 
<tt>          IF J % 100 = 0 THEN</tt> 
<tt>            PERFORM Rnotice2(1,''Row'',I,''Insert Count'',J) ;</tt> 
<tt>          END IF ;</tt> 
<tt>        ELSE</tt> 
<tt>          K=K+1 ;</tt> 
<tt>          PERFORM RNotice2(1,''Row'',I,''Skipped INSERT Count'',K) ;</tt> 
<tt>        END IF ; 
   You just connected this ELSE block to the IF statement it was nested
inside. 
   You probably need to comment out the rest of this ELSE block as well. 
 
 
</tt><tt>   Alban Hertroys 
 
</tt><tt>-- 
</tt><tt>Screwing up is an excellent way to attach something to the
ceiling. 
(Assuming you're not turning the screw driver the wrong way.) 
 
</tt><tt> 
-- 
Ralph 
_________________________</tt>
</body>
</html>

Tom Lane

tgl@sss.pgh.pa.us

over 15 years ago

In reply to: Ralph Smith (#4)

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

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'. 
 
The table is empty, and unfortunately remains that way; nothing gets
inserted. 
I tried other variations, however FOUND just isn't behaving as I would
think.

(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