Re: Question about PGSQL functions

From: Steve
Subject: Re: Question about PGSQL functions
Date: ,
Msg-id: Pine.GSO.4.64.0703082105340.11576@kittyhawk.tanabi.org
(view: Whole thread, Raw)
In response to: Re: Question about PGSQL functions  (Heikki Linnakangas)
List: pgsql-performance

Tree view

compact flash disks?  ("James Mansion", )
 Re: compact flash disks?  ("Merlin Moncure", )
 Re: compact flash disks?  (Florian Weimer, )
 Re: compact flash disks?  (Ron, )
  Re: compact flash disks?  (Carlos Moreno, )
   Re: compact flash disks?  (Csaba Nagy, )
    Re: compact flash disks?  ("James Mansion", )
     postgresql.conf file for PostgreSQL 8.2.3  (Eugene Ogurtsov, )
  Re: compact flash disks?  ("James Mansion", )
   Re: compact flash disks?  (Magnus Hagander, )
    Re: compact flash disks?  ("Merlin Moncure", )
    Re: compact flash disks?  ("James Mansion", )
  Re: compact flash disks?  (Ron, )
   Re: compact flash disks?  ("Merlin Moncure", )
    Question about PGSQL functions  (Steve, )
     Re: Question about PGSQL functions  (Heikki Linnakangas, )
      Re: Question about PGSQL functions  (Steve, )
 Re: compact flash disks?  (cedric, )

> 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?

     Yeah; we could use empty strings if that make it easier for
whatever reason, but to our front end software NULL vs. empty string
doesn't actually matter and we never query based on these columns, they're
for display purposes only.

>> 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;

     This looks interesting and I'm going to give this a shot tomorrow
and see how it goes.  Speed is somewhat of an issue which is why I
initially thought of the C function -- plus I wasn't aware you could do
CASE statements like that :)  Thanks for the idea!


Steve


pgsql-performance by date:

From: "James Mansion"
Date:
Subject: Re: compact flash disks?
From: "Bruce McAlister"
Date:
Subject: Re: PostgreSQL 8.2.3 VACUUM Timings/Performance