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(at)mail03a-free-gifts(dot)mx07(dot)com>');
>
> As you can see the message from name is:
> "Ann's Free Gifts & Coupons" server1(at)mail03a-free-gifts(dot)mx07(dot)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.
>
The only character you have to care about is the single quote. Do:
select msg_2_env('"Ann''s Free Gifts & Coupons"
<server1(at)mail03a-free-gifts(dot)mx07(dot)com>');

One more thing: As COUNT returns a bigint my coding would be
..
result bigint;
..

Regards, Christoph