Re: VIEW Materializada

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 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****
>
> ** **
>
> [image: medio ambiente] 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****
>
> ** **
>