edit
Updatable / Materialized Views
Views
- Externe Sichten für Applikationen
- Verringern die Komplexität von Queries
- Sind "updatable", wenn gewisse Einschränkungen erfüllt sind
- Einen Eintrag in der FROM Klausel
- Keine WITH, GROUP BY, LIMIT, OFFSET, etc
- keine UNION, INTERSECT, EXCEPT
- Keine Aggregation oder Window-Funktion
- Es müssen nicht alle Felder updatable sein, die View kann dann immer noch mit UPDATE bearbeitt werden, aber logischwerweise nicht die "nicht-updatable" Felder
Beispiel Updatable
create view ourview1 as
select name, abtnr, salaer
from angestellter
order by abtnr, salaer desc;
update ourview1 set salaer=salaer+10;
Beispiel NICHT updatable
create view ourview2 as
select abtnr, avg(salaer) as salaer
from angestellter
group by abtnr;
update ourview2 set salaer=salaer+10;
-- Fehler, view enthält groupby
Instead-of-Triggers
- Werden anstelle der ursprünglichen SQL-Operation ausgeführt
- Können für Modifikationen auf Tables und Views ausgeführt werden
- Leiten insert, update, delete auf Views auf die darunterliegenden Tabellen weiter
Beispiel
CREATE OR REPLACE VIEW abtleiterinfo (abtnr, abtname, abtchef)
AS
SELECT abt.abtnr, abt.name, al.abtchef
FROM abteilung abt
INNER JOIN abtleitung al
ON abt.abtnr=al.abtnr
INNER JOIN angestellter ang
ON ang.persnr=al.abtchef;
CREATE OR REPLACE FUNCTION abtleiterinfo_update_abtchef_fn()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
UPDATE abtleitung SET abtchef=NEW.abtchef
WHERE abtnr=OLD.abtnr;
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER abtleiterinfo_update_abtchef
INSTEAD OF UPDATE ON abtleiterinfo
FOR EACH ROW
EXECUTE PROCEDURE abtleiterinfo_update_abtchef_fn();
UPDATE abtleiterinfo SET abtchef=1019 WHERE abtnr=2;
Materialized View
- Eine normale View wird bei jedem Aufruf (Query) neu berechnet
- Eine Materialized View wird gecached und nur periodisch neu berechnet
CREATE MATERIALIZED VIEW view_name
AS
<query>
WITH [NO] DATA;
- Mit
NO DATA
wird die View initial nicht geladen und kann erst gelesen werden, wenn sie das erste Mal refreshed wird
- View neu laden:
REFRESH MATERIALIZED VIEW view_name;
Zugriffsschutz mit Views
- Auf Views können eigene Berechtigungen vergeben werden
- Somit kann z.B. einem user Zugriff auf eine unterliegende Tabelle verwehrt bleiben, er kann aber über eine View bestimmte Attribute der Tabelle sehen
CREATE VIEW AngPublic (Persnr, Name, Tel, Wohnort)
AS
SELECT Persnr, Name, Tel, Wohnort
FROM Angestellter;
GRANT SELECT ON AngPublic TO PUBLIC;
- In der View kann z.B.
user
verwendet werden, um Daten spezifisch für den angemeldeten Db-User anzuzeigen
CREATE OR REPLACE VIEW Angestellter_V AS
SELECT a.*
FROM Angestellter a
WHERE a.login = user
- Zugrifsschutz einer Tabelle kann auch über Stored Procedure realisiert werden. Dazu wird vom Table-Owner eine Stored Procedure erstellt, die eine Tabelle verändert, und anschliessend Ausführungsrechte an andere User erteilt. Die Stored Procedure werden immer mit den Rechten des Erstellers ausgeführt
GRANT EXEC ON SalaerErhoehung TO PersonalChef_R;
Temporäre Tabellen
- Erstellt eine Tabelle, die am Ende der Session oder der Transaktion gelöscht wird
CREATE TEMPORARY TABLE tmp AS
SELECT generate_series(1,100000) AS a;