RE: VIEW Materializada

Ya que estan en eso podrian explicarme el concepto de lo que es una vista materializada. Creo que lo he usado pero ese nombre la verdad no lo manejo.Exactamente que es y cual es el objetivo ?
*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas

*-Soporte PostgreSQL

*-www.jqmicrosistemas.com
*-809-849-8087
*-------------------------------------------------------*

Date: Fri, 25 Nov 2011 14:25:42 -0500
Subject: Re: [pgsql-es-ayuda] VIEW Materializada
From: ruben2218(at)gmail(dot)com
To: mario(dot)soto(dot)cordones(at)gmail(dot)com
CC: pgsql-es-ayuda(at)postgresql(dot)org

Excelente Mario por tu ayuda voy a probar lo que me has mandado y cualquier cosa te consulto.
Saludos
Ruben Avila G.Peru

2011/11/25 Mario Soto Cordones <mario(dot)soto(dot)cordones(at)gmail(dot)com>

Hola.
1. Debes crear las funciones que más abajo incluyo.
/*
* PostgreSQL Materialized Views *
* Author: Jonathan Gardner <jgardner(at)jonathangardner(dot)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 DEFINERLANGUAGE 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 DEFINERLANGUAGE 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 DEFINERLANGUAGE 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
Por favor antes de Imprimir éste correo, piense en los árboles de nuestro planeta.


De: pgsql-es-ayuda-owner(at)postgresql(dot)org [mailto:pgsql-es-ayuda-owner(at)postgresql(dot)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 <jaime(at)2ndquadrant(dot)com> escribió:
2011/11/25 ruben avila galindo <ruben2218(at)gmail(dot)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