From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Steve <cheetah@tanabi.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Question about PGSQL functions
Date: Thu, 08 Mar 2007 23:17:52 +0000
Message-ID: <45F099A0.5080401@enterprisedb.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0703081715190.11576@kittyhawk.tanabi.org>
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>
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
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-performance@postgresql.org
Cc: heikki@enterprisedb.com, cheetah@tanabi.org
Subject: Re: Question about PGSQL functions
In-Reply-To: <45F099A0.5080401@enterprisedb.com>
* 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