Hola.
1. Debes crear las funciones que más abajo incluyo.
/*
* PostgreSQL Materialized Views
*
* Author: Jonathan Gardner <jgard...@jonathangardner.net>
*
* Copyright (c) 2003
*
* Licensed under BSD license.
*
*/
/*
* Please provide feedback on these features. If you want to extend it,
please
* join the discussion on the pgsql-hackers mailing list.
*
*/
/*
* INSTALLATION:
*
* - Create the plpgsql language in the target database.
* - Execute all of these commands as a superuser.
*
*/
/*
* TABLE materialized_views
*
* This table stores the current materialized views, their tables, and the
* corresponding view. It also records what kind of view it is.
*
*/
-- DROP TABLE matviews;
CREATE TABLE matviews (
mv_name NAME NOT NULL
, mv_view NAME NOT NULL
, mv_auto_update BOOLEAN DEFAULT FALSE NOT NULL
);
REVOKE ALL ON matviews FROM PUBLIC;
GRANT SELECT ON matviews TO PUBLIC;
/*
* FUNCTION create_matview(name, name, bool)
*
* name: Name of the materialized view you wish to create.
* name: Name of an existing view that you wish to materialize.
* bool: Is Auto-Updated (not supported)
*
* This function will create a materialized view from a regular view.
*
* Example:
* CREATE TABLE test (id serial primary key, test boolean not null);
* CREATE VIEW test_v AS SELECT * FROM test;
* SELECT create_matview('test_mv', 'test_v', false);
*/
CREATE OR REPLACE FUNCTION create_matview(name, name, boolean) RETURNS
boolean
SECURITY DEFINER
LANGUAGE plpgsql AS '
DECLARE
matview ALIAS FOR $1;
view_name ALIAS FOR $2;
auto_update ALIAS FOR $3;
query ALIAS FOR $3;
entry matviews%ROWTYPE;
BEGIN
SELECT INTO entry * FROM matviews WHERE mv_name = matview;
IF FOUND THEN
RAISE EXCEPTION ''Materialized view % exists.'', matview;
END IF;
IF auto_update THEN
RAISE EXCEPTION ''Auto-Updated Materialized Views are not
supported.'';
END IF;
EXECUTE ''REVOKE ALL ON '' || view_name || '' FROM PUBLIC'';
EXECUTE ''GRANT SELECT ON '' || view_name || '' TO PUBLIC'';
EXECUTE ''CREATE TABLE '' || matview
|| '' AS SELECT * FROM '' || view_name;
EXECUTE ''REVOKE ALL ON '' || matview || '' FROM PUBLIC'';
EXECUTE ''GRANT SELECT ON '' || matview || '' TO PUBLIC'';
INSERT INTO matviews (mv_name, mv_view, mv_auto_update)
VALUES (matview, view_name, auto_update);
RETURN true;
END
';
/*
* FUNCTION drop_matview(name)
*
* name: Name of the materialized view you wish to drop.
*
* This function will drop an existing materialized view.
*
* Example:
* SELECT drop_matview('test_mv');
*
*/
CREATE OR REPLACE FUNCTION drop_matview(name) RETURNS void
SECURITY DEFINER
LANGUAGE plpgsql AS '
DECLARE
matview ALIAS FOR $1;
entry matviews%ROWTYPE;
BEGIN
SELECT INTO entry * FROM matviews WHERE mv_name = matview;
IF NOT FOUND THEN
RAISE EXCEPTION ''Materialized view % does not exist.'', matview;
END IF;
EXECUTE ''DROP TABLE '' || matview;
EXECUTE ''DROP VIEW '' || entry.mv_view;
DELETE FROM matviews WHERE mv_name=matview;
RETURN true;
END
';
/*
* FUNCTION refresh_matview(name)
*
* name: Name of the materialized view you wish to refresh.
*
* This function will refresh a non-auto-updating materialized view.
*
* Example:
* SELECT refresh_matview('test_mv');
*
*/
CREATE OR REPLACE FUNCTION refresh_matview(name) RETURNS void
SECURITY DEFINER
LANGUAGE plpgsql AS '
DECLARE
matview ALIAS FOR $1;
entry matviews%ROWTYPE;
BEGIN
SELECT INTO entry * FROM matviews WHERE mv_name = matview;
IF NOT FOUND THEN
RAISE EXCEPTION ''Materialized view % does not exist.'', matview;
END IF;
IF entry.mv_auto_update THEN
RAISE EXCEPTION ''Refreshing auto-updating materialized views
illegal.'';
END IF;
EXECUTE ''DELETE FROM '' || matview;
EXECUTE ''INSERT INTO '' || matview
|| '' SELECT * FROM '' || entry.mv_view;
RETURN true;
END
';
2.- debes crear la vista que deseas materializar
3.- Creas la vista materializada:
select create_matview('vista_materializada','vista_normal')
4.- Cada vez que actualices datos en la table que corresponde a la vista,
debes hacer:
SELECT refresh_matview(''vista_materializada');
5.- para eliminar la vista materializada
SELECT drop_matview(''vista_materializada'');
Espero te sirva
Mario Soto Cordones| Ingeniero Consultor
medio ambiente Por favor antes de Imprimir éste correo, piense en los
árboles de nuestro planeta.
De: pgsql-es-ayuda-ow...@postgresql.org
[mailto:pgsql-es-ayuda-ow...@postgresql.org] En nombre de ruben avila
galindo
Enviado el: viernes, 25 de noviembre de 2011 15:07
Para: Jaime Casanova
Asunto: Re: [pgsql-es-ayuda] VIEW Materializada
Hola jaime me podria dar un ejemplo chico para de acuerdo a eso armarlo para
mi caso y iendo refrescar para probar.
Saludos
Ruben Avila G
Perú
El 25 de noviembre de 2011 13:02, Jaime Casanova <ja...@2ndquadrant.com>
escribió:
2011/11/25 ruben avila galindo <ruben2...@gmail.com>:
> Hola doc alguien sabe si postgresql soporta vistas materializadas.
>
si, pero no automaticas... las puedes armar usando reglas o triggers o
una funcion que la refresque...
aunque hay planes para lograr vistas materializadas automaticas para
la version 9.3 (sin embargo eso sera para el 2013)
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
<<image001.jpg>>
