Re: [Gnumed-devel] Help with a Function Needed Please

[Karsten Hilbert]

On Tue, Jan 24, 2006 at 11:21:45AM +1100, Richard wrote:

> To one and all,
> 
> I need help with this function. It does the following. There are about 3500 
> products in the mims full product information, and each product may have one 
> or more generic name. As an exercise in learning functions, I wanted to go 
> through every product in the database, and con-catenate the generics to a 
> single string, and write the result to a new table, along with its mancode.
What you are doing there is pretty close to a Materialized View.

> Probably could have been done easier with a query or something, but I wanted 
> to learn functions.
Fine, no problem. A couple of points:

- using dollar quoting ($body$) restricts you to PG 8.0 and above

> 1) When you just want a function to 'do something' as opposed to return 
> something, is there a different syntax - ie does a function have to return 
> anything - here it simply returns the last value of the _generics as 
> declared. If so, how to you write it
I usually return "True" or "False" (eg boolean) in this case.

You could also declare the function to return "void" in
which case it doesn't matter what you put behind the
"return" statement.

> 2) Would this whole thing be better acheived via queries, ie is this the 
> 'wrong way' to do what I want.
It's not the Wrong Way.

For speed it's good to aggregate/format data as close to the
source as possible. For maintainability it's better to keep
formatting in the application. One *could* make a
function/view in the database which generates properly
formatted HTML output just the way your app needs it.
However, if you want to change that output you will have to
change the view in the database. If done at the application
level (say, with Cheetah) one would just need to replace a
template. Now, the tradeoff is in the likelihood of later
change. Aggregation/denormalization is best done in the
database. Complex parsing in the application. Simple parsing
or pre-parsing with low likelihood of needing change can
safely be done in the database, too.

In the given example I would do away with the manxx_generic
table and put the whole thing into a view, eg
v_manxx_generic which properly aggregates the data for me
but allows me to select on any criteria I might want. Only
if that - combined with proper indices - proves to be too
slow would I think about a denormalized helper table.

> 3) I know you previously sent me this info, but due to me trashing it I can't 
> find it - could you remind me the syntax of concatenation, so as not to end 
> up with the comma at the end of the row.
There isn't any way to do that in pl/pgsql unless
explicitely (eg removing the superfluous comma at the
front/end manually).

> COMMENT ON FUNCTION "drugreferencefull"."get_generics"(integer)
> IS 'gets all generics for a particular drug in mims annual, returned as a 
> comma separated string.';
The question is why you would want to do that. There's two
possible reasons:

1) concatenation for display purposes
   - this is a case of *simple* formatting
   - that's fine doing in the database
   - but could be done in a view, too, with less overhead

2) aggregation of generics for further processing
   - this is better done using a query that returns a *list*
     of results and processing those in the application one by one

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346