pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about PGSQL functions
3+ messages / 2 participants
[nested] [flat]
* Question about PGSQL functions
@ 2007-03-08 22:36 Steve <cheetah@tanabi.org>
0 siblings, 1 reply; 3+ messages in thread
From: Steve @ 2007-03-08 22:36 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about PGSQL functions
@ 2007-03-08 23:17 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Steve <cheetah@tanabi.org>
0 siblings, 1 reply; 3+ messages in thread
From: Heikki Linnakangas @ 2007-03-08 23:17 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about PGSQL functions
@ 2007-03-09 02:09 Steve <cheetah@tanabi.org>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 3+ messages in thread
From: Steve @ 2007-03-09 02:09 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-03-09 02:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-08 22:36 Question about PGSQL functions Steve <cheetah@tanabi.org>
2007-03-08 23:17 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-03-09 02:09 ` Steve <cheetah@tanabi.org>
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