Re: Question about PGSQL functions

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