SitePoint Sponsor |
|
User Tag List
Results 1 to 1 of 1
-
Jan 12, 2005, 10:09 #1
- Join Date
- May 2004
- Location
- belgium
- Posts
- 3
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
plpgsql - simulating materialized view
Hi,
I have a db with +100.000 records. two fields (type and lang) are indexed and content only a set of value (type: from 1 to 3, lang: FR, EN or ES)
I want to know how much records have type=1, type=2, type=3, lang=FR, lang=EN, lang=ES
I create two views:
Code:SELECT type, COUNT(*) FROM obj GROUP BY type; SELECT lang, COUNT(*) FROM obj GROUP BY lang; **view1** **view2** type cpt lang cpt 1 10000 FR 20000 2 60000 EN 30000 3 30000 ES 50000
Code:**matview** type value cpt TYPE 1 10000 TYPE 2 60000 TYPE 3 30000 LANG FR 20000 LANG EN 30000 LANG ES 50000
Here's the code of my function :
Code:CREATE FUNCTION refresh_mview() RETURNS integer AS ' DECLARE rec RECORD; BEGIN DELETE FROM matview; FOR rec IN SELECT * FROM view1 LOOP INSERT INTO matview VALUES(''TYPE'','' || rec2.type || '','' || rec2.cpt || '')''; END LOOP; FOR rec IN SELECT * FROM view2 LOOP INSERT INTO matview VALUES(''LANG'','' || rec2.lang || '','' || rec2.cpt || '')''; END LOOP; RETURN 1; END; ' LANGUAGE plpgsql;
Bookmarks