From: Christoph Haller <ch@rodos.fzk.de>
To: wintrojr@tripos.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Need another way to do this, my sql much to slow...
Date: Fri, 21 Nov 2003 11:05:57 MET
Message-ID: <200311211005.LAA13146@rodos> (raw)
In-Reply-To: <4E676B0AAF74B443A18D7BC7AAB3CFFD1D44D8@s01-exch01.tripos.com>
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, wintrojr@tripos.com
Subject: Re: Need another way to do this, my sql much to slow...
In-Reply-To: <200311211005.LAA13146@rodos>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox