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: ruben2...@gmail.com
To: mario.soto.cordo...@gmail.com
CC: pgsql-es-ayuda@postgresql.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.soto.cordo...@gmail.com>
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 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-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
<<attachment: image001.jpg>>
