DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=kxQgpzuFLMqCTHEMhedhv1RPHLY9IlrIDDckxjcxDG1IrZgypU0zJSU1h2erKATjyNeUw9NobnI/z4wUZOX7/gcyAa8opN9gECUtOwgvGRlzxLc62n23eGljWMoTrY40TOSccv02lpflBhZNMgq3Hw05hspmHMQQ2Yry2XygG4I=
Message-ID: <45F099A0.5080401@enterprisedb.com>
Date: Thu, 08 Mar 2007 23:17:52 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.8 (X11/20061116)
MIME-Version: 1.0
To: Steve <cheetah@tanabi.org>
CC: pgsql-performance@postgresql.org
Subject: Re: Question about PGSQL functions
References: <E1HOxqf-0005Mn-Uw@elasmtp-kukur.atl.sa.earthlink.net>
	<HCEPKPMCAJLDGJIBCLGHOEDCHEAA.james@mansionfamily.plus.com>
	<20070308084823.GA7216@svr2.hagander.net>
	<b42b73150703080611y64be6b11s2f580671d731cc09@mail.gmail.com>
	<E1HPNXd-0002mO-Rq@elasmtp-junco.atl.sa.earthlink.net>
	<b42b73150703081224j41bcc2barbc22495d833b8304@mail.gmail.com>
	<Pine.GSO.4.64.0703081715190.11576@kittyhawk.tanabi.org>
In-Reply-To: <Pine.GSO.4.64.0703081715190.11576@kittyhawk.tanabi.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Steve wrote:
> IF  strlen(source.corrected_procedure_code)
> THEN:
>   summary.procedure_code=source.corrected_procedure_code
>   summary.wrong_procedure_code=source.procedure_code
> ELSE:
>   summary.procedure_code=source.procedure_code
>   summary.wrong_procedure_code=NULL

Um, so you test if source.corrected_procedure_code is an empty string? 
And if it is, summary.procedure_code is set to an empty string? But in 
wrong_procedure_code, you use NULLs?

> Simple, right?  Making a C function to handle this should be no sweat -- 
> I would basically split this logic into two separate functions, one to 
> populate summary.procedure_code and one to populate 
> summary.wrong_procedure_code, and it removes the need of having any sort 
> of back and forth between the program and DB... I can just do like:
> 
> update summary_table
>  set procedure_code=pickCorrect(source.procedure_code,
>                                  source.corrected_procedure_code),
>      wrong_procedure_code=pickWrong(source.procedure_code,
>                                  source.corrected_procedure_code),....
>  from source where summary_table.source_id=source.source_id;

ISTM you could write this easily with a little bit of SQL, with no need 
for C-functions (I haven't run this, probably full of typos..) :

update summary_table
   set procedure_code = (CASE WHEN source.corrected_procedure_code = '' 
THEN '' ELSE source.procedure_code END;),
       wrong_procedure_code = (CASE WHEN source.corrected_procedure_code 
= '' THEN source.procedure_code ELSE NULL END;)
   from source where summary_table.source_id=source.source_id;


-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com