From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200311211005.LAA13146@rodos>
Subject: Re: Need another way to do this, my sql much to slow...
To: wintrojr@tripos.com (Jerry Wintrode)
Date: Fri, 21 Nov 2003 11:05:57 MET
Cc: pgsql-sql@postgresql.org
In-Reply-To: <4E676B0AAF74B443A18D7BC7AAB3CFFD1D44D8@s01-exch01.tripos.com>;
	from "Jerry Wintrode" at Nov 21, 103 12:30 (midnight)

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

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

Regards, Christoph