RE: VIEW Materializada

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

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