[Gnumed-devel] re: comm views

[sjtan]

> I'd prefer to generate them in v_person_comms_flat
> for people that do have some URLs, though, such that we don't
> carry a zillion completely empty rows in that view.
this is a little better I hope,

SELECT DISTINCT ON (v1.pk_identity) v1.pk_identity, v1.url AS email, 
v2.url AS fax, v3.url AS homephone, v4.url AS workphone, v5.url AS 
mobile FROM v_person_comms v1, v_person_comms v2, v_person_comms v3, 
v_person_comms v4, v_person_comms v5 WHERE (((((((((v1.pk_identity = 
v2.pk_identity) AND (v2.pk_identity = v3.pk_identity)) AND 
(v3.pk_identity = v4.pk_identity)) AND (v4.pk_identity = 
v5.pk_identity)) AND (v1.pk_comm_type = 1)) AND (v2.pk_comm_type = 2)) 
AND (v3.pk_comm_type = 3)) AND (v4.pk_comm_type = 4)) AND 
(v5.pk_comm_type = 5))

and exists ( select id from lnk_identity2comm_chan l where 
v1.pk_identity = l.id_identity)

ORDER BY v1.pk_identity ;

BTW, I can't find crosstab function , even though I've got 7.3 
postgresql-contrib loaded on Mandrake 9.2

This is how I think the crosstab function would work, except that the  
first  column is an id column instead of rowname

the script test_sql_func1.sql is run, then make_crosstab2.sql is run,
then below is an example of how crosstab might work:

select make_crosstab2(
'select id_identity as rowid , description, url from 
lnk_identity2comm_chan l, comm_channel c, enum_comm_types e where 
l.id_comm = c.id and c.id_type = e.id', 'select description from 
enum_comm_types'
);

select *  from crosstab_enum_comm_types;







drop function make_crosstab1(text);
create function make_crosstab1( text) returns text as '
declare
        curs1 refcursor;
        src_qry alias for $1;
        query text;
        first boolean;
        fields TEXT;
        values TEXT;
        row RECORD;
        name TEXT;
        tablename TEXT;
        
BEGIN
        tablename :=  btrim( split_part(split_part(src_qry, '' from '', 2), '' 
'', 1) );
        first := true;
        for row in execute ''select tablename from pg_tables where tablename = 
''''crosstab_'' || tablename  || '''''''' LOOP
                first := false;
        end loop;
        
        if not first then
                execute ''drop table crosstab_'' || tablename;
        end if;

        first := true;
        query := ''create temp table crosstab_'' || tablename;
        fields := '' rowid integer, '';
        values := '''';
        
        open curs1 for execute src_qry;

        loop
                fetch curs1 into name;
                exit when  not FOUND ;
                if not first then
                        fields := fields || '','';
                end if;

                fields := fields || name || '' text default null '';
                first := false;

        end loop;

        query := query || '' ('' || fields || '')'' ;

        execute query;

        return query;   
end;' LANGUAGE 'plpgsql';

drop function make_crosstab2(text, text);

create function make_crosstab2( text, text) returns text as '
declare

        value_qry alias for $1;
        category_qry alias for $2;
        create_val_qry text;
        crosstab text;
        curs1   refcursor;
        curs2   refcursor;
        row     record;
        category text;
        value   text;
        a_rowid integer;
        dummy_id integer;

begin
        create_val_qry := make_crosstab1(category_qry);

        crosstab = split_part( create_val_qry, '' '', 4);

        open curs1 for execute value_qry;
        loop
                fetch curs1 into a_rowid, category, value;
                exit when not found;

                open curs2 for execute ''select rowid from '' || crosstab || '' 
c where c.rowid = '' || text(a_rowid);
                fetch curs2 into dummy_id;
        
                if found then
                        execute ''update '' || crosstab||'' set '' || category 
|| ''= '''''' || value || '''''' where rowid = '' || a_rowid ;
                else
                        execute ''insert into ''||crosstab||'' ( rowid, '' || 
category ||'') values ( '' || a_rowid || '', ''''''|| value || '''''' ) '';

                end if;
                close curs2;
        end loop;
        return crosstab;
end;
' language 'plpgsql';