[Gnumed-devel] Help with a Function Needed Please

[Richard Terry]

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. 
Probably could have been done easier with a query or something, but I wanted 
to learn functions.

 table is created to hold the new data:

 createtable manxxdat_generics(
        id serial,
             mancode integer not null,
             compound_generic
             );

I created two functions, the second, uses the first internally (that way I 
made sure the first one worked ok first, I guess you would normally put them 
both together).
================================
Function 1  simply returns a concatenated
string for a single drug: Expects the drugs mancode  (the product code)
ie mancode in , out = generic1, generic2, generic3....etc


CREATE OR REPLACE FUNCTION "drugreferencefull"."get_generics" (integer) 
RETURNS text AS
$body$
declare
    _mancode alias for $1;
    _generics text;
    _row record;
begin
     _generics :='';
     for _row in
      SELECT
            GMMAN.mancode,
            GENMAN.generic
            FROM
                          drugreferencefull.gmman GMMAN
            INNER JOIN    drugreferencefull.genman GENMAN
            ON            (GMMAN.gencode = GENMAN.gencode)
            WHERE
                         GMMAN.mancode = _mancode
      loop
          _generics := _generics ||  coalesce(_row.generic, '')||', ';
     end loop;

        return _generics;
end
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

================================
Function2:
Loops through all drugs in the productdb(3500) supplies the mancode 
(theproduct code) to the first function which returns the concatenated string 
as above , then writes the result to a new row in the table manxxdat_generics

CREATE OR REPLACE FUNCTION "drugreferencefull"."function6" () RETURNS text AS
$body$
DECLARE
_generics text;
_row record;
begin
     _generics = '';
     for _row in
                SELECT
               drugreferencefull.manxxdat.product,
               drugreferencefull.manxxdat.mancode
               FROM
                drugreferencefull.manxxdat
     loop
             select into 
_generics  drugreferencefull.get_generics(_row.mancode) ;
             insert into 
drugreferencefull.manxxdat_generics(mancode,compound_generic)
             values(_row.mancode,_generics);
     end loop;
return _generics;
end
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
============================================



Now, this function works fine - it that it does what I want, however I have 
two questions.

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

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

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.


Regards

Richard T

drugreferencefull_function6()_DDL.sql
Description: Text document

drugreferencefull_get_generics(integer)_DDL.sql
Description: Text document