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