Re: Need another way to do this, my sql much to slow...

Ok, I figured that part out by simply changing the way I'm doing to
query, and writing a function to handle the reply. But it will introduce
another problem. How to I pass special characters, any character, to a
function like this:

select msg_2_env('"Ann's Free Gifts & Coupons"
<server1@mail03a-free-gifts.mx07.com>');

As you can see the message from name is:
"Ann's Free Gifts & Coupons" server1@mail03a-free-gifts.mx07.com

I need that whole string to match. Including the ",&,@, and yes the
single quote in Ann's. Passed as a variable this should not be a
problem, I think, but how do I test this on the command line with psql?

Oh, here is the simple function in case anyone cares to have it...very
simple. Now processing about 100000 records takes 1ms. Down from the
12-15 seconds. WooHoo. Just that other little issue..hehehe.

========================================================================
====
CREATE FUNCTION msg_2_env (text) RETURNS int4 AS
'   DECLARE       intext ALIAS FOR $1;       result int4;
   BEGIN
       result := ( SELECT count(DISTINCT
record_of_claims.msg_sender_num) AS mclaim_count FROM record_of_claims
WHERE (record_of_claims.env_sender_num = (SELECT
env_from_senders.env_sender_num FROM env_from_senders WHERE
(env_from_senders.envelope_from = intext::character varying))) GROUP BY
record_of_claims.env_sender_num );
       RETURN result;
   END;
' LANGUAGE 'plpgsql';

========================================================================
====

Jerry Wintrode
Network Administrator
Tripos, Inc.





---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend