Question about PGSQL functions

От: Steve
Тема: Question about PGSQL functions
Дата: ,
Msg-id: Pine.GSO.4.64.0703081715190.11576@kittyhawk.tanabi.org
(см: обсуждение, исходный текст)
Ответ на: Re: compact flash disks?  ("Merlin Moncure")
Ответы: Re: Question about PGSQL functions  (Heikki Linnakangas)
Список: pgsql-performance

Скрыть дерево обсуждения

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, )

Hey there :)

I'm re-writing a summarization process that used to be very 'back and
forth' intensive (i.e. very chatty between my summarization software and
the DB).  I'm trying to reduce that by practically reducing all this back
and forth to a single SQL query, but in order to support certain
complexities it looks to me like I'm going to have to write some postgres
C language functions.

This is something I'm actually familiar with and have done before, but let
me describe what I'm trying to do here so I can be sure that this is the
right thing to do, and to be sure I do it correctly and don't cause memory
leaks :)


---

I have two columns, one called "procedure_code" and the other called
"wrong_procedure_code" in my summary table.  These are both of type
varchar(32) I believe or possibly text -- if it matters I can double check
but because all the involved columns are the same type and size it
shouldn't matter. :)

These are actually populated by the "procedure_code" and
"corrected_procedure_code" in the source table.  The logic is, basically:

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


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;


Make sense?  So question 1, is this the good way to do all this?


Question 2: Assuming it is the good way to do all this, would this
function be correct assuming I did all the other stuff right (like
PG_FUNCTION_INFO_V1, etc.):

Datum pickCorrect(PG_FUNCTION_ARGS){
     text*    procedure_code=PG_GETARG_TEXT_P(0);
     text*    corrected_code=PG_GETARG_TEXT_P(1);

     if(VARSIZE(corrected_code)-VARHDRSZ){
         PG_RETURN_TEXT_P(corrected_code);
     }else{
         PG_RETURN_TEXT_P(procedure_code);
     }
}

Would that simply work because I'm not actually modifying the data, or
would I have to pmalloc a separate chunk of memory, copy the data, and
return the newly allocated memory because the memory allocated for the
args "goes away" or gets corrupted or something otherwise?



Thanks a lot for the info!


Steve


В списке pgsql-performance по дате сообщения:

От: Steve
Дата:
Сообщение: Re: Question about PGSQL functions
От: "James Mansion"
Дата:
Сообщение: Re: compact flash disks?