[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Gnumed-devel] re: comm views
From: |
sjtan |
Subject: |
[Gnumed-devel] re: comm views |
Date: |
Sun, 27 Jun 2004 22:11:48 +1000 |
User-agent: |
Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040113 |
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';
- [Gnumed-devel] re: comm views,
sjtan <=