View mit abhängigen Views bearbeiten

[Ludwigmller]

Hallo,
ich möchte eine View (A), die durch andere Views (B,C) benutzt wird verändern. PgAdmin lässt dies nicht zu, sondern möchte A löschen und neu erstellen und somit auch B und C löschen.
Gibt es eine Möglichkeit A dennoch zu ändern? Es soll nur eine weitere Spalte angezeigt werden.
Oder sollte ich statt View A lieber eine Stored Procedure erstellen?

Gruß Ludwig

 

[castorp]

Nein, das geht nicht.

Die beiden anderen Views müssen auch neu erzeugt werden. Deswegen bietet es sich an, solche Dingen eben nicht über die GUI zu erledigen, sondern alles als SQL Skripte anzulegen (und über git zu versionieren). Dann änderst Du was Du ändern musst, und lässt einfach die Skripte laufen.

Tools wie Liquibase, Sqitch oder Flyway helfen dabei diese Skripte zu organisieren und in den verschiedenen Umgebungen (Test, Produktion etc) laufen zu lassen.

 

[Ludwigmller]

Scheint ja sehr kompliziert zu sein, im Gegensatz zu MySQL.
Was ist denn generell das beste Vorgehen um häufig verwendete Abfragen nicht jedes Mal wieder verwenden zu müssen? Das hatte ich bisher als View realisiert. Nach etwas Recherche scheinen Procedures unter postgres auch nicht so einfach wie bspw. hier erklärt zu sein :/ Das wäre doch sonst genau das was ich brauche, oder nicht?
Was genau meinst du denn mit "SQL Skripte"?

 

[castorp]

Nein ist überhaupt nicht kompliziert wenn man seinen Source Code richtig organisiert.

SQL Skripte sind Textdateien in denen SQL Befehle drin stehen.

Vereinfacht ausgedrückt:

view_1.sql

drop view if exists v_1 cascade;
create view v_1 as select * from ....;

view_2.sql

drop view if exists v_2 cascade;
create view v_2 as select * from ....;

view_3.sql

drop view if exists v_3 cascade;
create view v_3 as select * from v_1 join v_2 ....;

Dann macht man sich ein "Master Skript" welches alle in der richtigen Reihenfolge aufruft, z.B. für psql:

create_all_views.sql

\i view_1.sql
\i view_2.sql
\i view_3.sql

Unabhängig davon was Du änderst, musst Du nur "create_all_views.sql" laufen lassen:

psql -U ... -d my_db -f create_all_views.sql

Eine andere Einschränkung von Postgres mit Views ist, dass Du Tabellen die in Views verwendet werden, nicht so ohne weiteres ändern kannst. Deswegen sehen alle Skripte zur Schema Migration bei mir so aus, dass am Anfang einfach alle Views ge-dropped werden, dann laufen die Migrationen (egal was) und zum Schluss wird dann "create_all_views.sql" aufgerufen. Wenn man das alles automatisiert dann ist das auch wirklich keine echte Einschränkung mehr. Wir verwenden Liquibase, aber letztendlich ist es egal welches Tool man verwendet - eine durchdachte Verzeichnis- und Dateistruktur ist der erste Schritt dahin. Wenn man kein Tool verwendet, muss man sich immer merken, welche "Version" die Zieldatenbank hat und welche Migrationsschritte (=Skripte) dort ausgeführt werden müssen. Dann kommt es auch leicht zu Fehlern. Deswegen: automatisieren wo es nur geht.

Das geht alles aber nur wenn man diese vermaledeite GUI zur Erstellung des Datenbankschema weg lässt und alles(!) sauber über SQL Befehle in Skripten macht.

Stored procedures sind nicht dafür gedacht Resultate zurück zu liefern, dafür müsstest Du eine Funktion schreiben:

create function f_1() 
  returns table (....)
as
$$
select ...
from v_1
  join v_2 ...
;
$$
language sql
stable

Verwenden kannst Du das mit

select * from f_1();

Aber unterm Strich ändert das nicht viel.

 

[Ludwigmller]

Danke für die ausführliche Antwort.
Wie entscheide ich mich zwischen function und view?
Letztendlich können beide ja für häufig verwendete SELECT Befehle verwendet werden. Ist vielleicht mit functions etwas schwieriger mit einer anderen darauf zuzugreifen...

Was ist eure Empfehlung bzw. die gute fachliche Praxis?

 

[akretschmer]

was als VIEW geht, solltest Du auch als VIEW machen. Function ist extra Aufwand, auch bei der Ausführung.

 

[Ludwigmller]

Wo lege ich die SQL-Skriptdateien für die Views am besten an? Erstellt man dafür z.B. ein Verzeichnis "skript" im Hauptverzeichnis der PostgrSQL Installation?

 

[Ludwigmller]

Und wie macht man es am besten, wenn man häufig genutzte Abfragen hat, wo allerdings ein Parameter verwendet werden soll. Beispielsweise eine Jahresübersicht, wo nur das Jahr angegeben werden muss und die Abfrage an sich gleich bleibt?
Dann eine function?

 

[akretschmer]

Du kannst bei einer Abfrage auf eine VIEW eine WHERE-Condition angeben.

 

[castorp]

Wo lege ich die SQL-Skriptdateien für die Views am besten an? Erstellt man dafür z.B. ein Verzeichnis "skript" im Hauptverzeichnis der PostgrSQL Installation?

Da wo auch der Rest Deines Anwendungscode liegt (PHP, Java, VisualBasic - womit auch immer Du programmierst) . Ansonsten einfach in irgendeinem Verzeichnis welches Dir gefällt, aber im PostgreSQL Verzeichnis (oder c:\Programme) hat das gar nichts verloren. Ich habe das typischerweise alles in einem Verzeichnis c:\Projects (und ein Unterverzeichnis für jedes Projekt). Du kannst das natürlich auch unter "Dokumente" ablegen oder jedes andere Verzeichnis welches Dir gefällt. Sinnvollerweise hat man dann auch eine Batchdatei die psql startet und alle Skripte in der richtigen Reihenfolge ausführt.

Und wie macht man es am besten, wenn man häufig genutzte Abfragen hat, wo allerdings ein Parameter verwendet werden soll. Beispielsweise eine Jahresübersicht, wo nur das Jahr angegeben werden muss und die Abfrage an sich gleich bleibt?

Das würde ich eher mit einer WHERE Bedingung lösen,

select * from the_view where jahr = 2020

Es gibt seltene Fälle wo es effizienter ist, dies im SQL Befehl als Parameter zu haben z.B. bei tief-verschachtelten Aggregationen bei denen der Optimizer nicht erkennen kann, dass die Bedingungen nach unten "ge-pushed" werden können.

In so einem Fall kann man dann auf Funktionen ausweichen. Würde ich aber erst machen, wenn es wirklich ein Performanceproblem gibt.

 

[Ludwigmller]

Du kannst bei einer Abfrage auf eine VIEW eine WHERE-Condition angeben.

Das würde ich eher mit einer WHERE Bedingung lösen,

Aber ja nicht wenn nach der Filterung mit Where noch eine Aggregatfunktion angewand werden muss. Dann eine Function?

Das geht alles aber nur wenn man diese vermaledeite GUI zur Erstellung des Datenbankschema weg lässt und alles(!) sauber über SQL Befehle in Skripten macht.

Bei pgAdmin gibt es aber immer einen Reiter "SQL" der den über das GUI erzeugten Code anzeigt, bevor man bestätigt. Da kann man dann nochmal überprüfen und ggf. anpassen. Also so ganz schlecht ist das glaub ich nicht immer. Was nicht heißt, dass ich deinen Rat nicht beachten möchte.

 

[akretschmer]

Aber ja nicht wenn nach der Filterung mit Where noch eine Aggregatfunktion angewand werden muss.

Hast Du es probiert?

Andreas

 

[Ludwigmller]

Hast Du es probiert?

Ich weiß ehrlich gesagt nicht wie. Hier mal der vereinfachte Code. "2020" müsste dann ersetzt werden.

 SELECT
    s.id,
    s.bezeichnung,
    sum(p.anzahl::numeric * p.abstand * p.laenge::numeric) / 10000::numeric AS flaeche
   FROM test.tabelle1 s,
    test.tabelle2 p,
  WHERE s.id = p.s_id
  AND p.column1 <= 2020 AND (func_1(p.column2) >= 2020 OR p.column2 IS NULL)
  GROUP BY s.id;

 

[akretschmer]

das kann so nicht gehen, aber nicht, weil es ein VIEW ist (also vermutlich ist tabelle1 und/oder tabelle2 bei Dir ein VIEW). Es geht nicht, weil Du hast 3 Spalten, davon eine Aggregation, aber nur eine der zwei anderen im GROUP BY.

 

[Ludwigmller]

Achso sorry, ich hatte die Abfrage wie gesagt gekürzt (um es übersichtlicher zu machen) und im Group by zu viele Spalten gelöscht, die sind da sonst alle drin, Abfrage klappt ansonsten