Probleme mit FOR Schleife

Hallo ihr lieben,

ich versuche mich gerade an einer FOR-Schleife aber es will mir nicht gelingen. Kann mir jemand weiterhelfen?

CREATE OR REPLACE FUNCTION sk_test_zeit()
  RETURNS TABLE(test integer) AS

  
$BODY$
DECLARE 
	testvar INTEGER := 0;
	 
BEGIN

	LOOP
	EXIT WHEN testvar = 3;

	SELECT 1 as test;
	testvar := testvar +1;



	END LOOP

	--SELECT 1 as test
END;

$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION sk_test_zeit()
  OWNER TO postgres;

Bin für jeder Hilfe dankbar! Ziel ist es eine spätere SQL Abfrage mehrfach ausführen zu lassen.

So ein paar Fehler habe ich nach einem Heißgetränk selber gefunden:

CREATE OR REPLACE FUNCTION sk_test_zeit()
  RETURNS TABLE(test integer) AS

  
$BODY$
DECLARE 
	testvar INTEGER; --:= 0;
	 
BEGIN
	testvar := 0;
	LOOP
	EXIT WHEN testvar = 3;

	SELECT 1 as test;
	testvar := testvar +1;



	END LOOP;

	--SELECT 1 as test
	
END;

$BODY$
  LANGUAGE plpgsql
  COST 100
  ROWS 1000;
ALTER FUNCTION sk_test_zeit()
  OWNER TO postgres;

Nun meckert mich aber pgadmin an das ich gefälligst return nutzen soll. Ja das würde ich auch gern tuen. aber return test; ist ja sicher der falsche Ansatz. Was gebe ich denn nun zurück?

ändere das in :

       return query SELECT 1 as test;

warum willst Du eine Abfrage in einer Schleife ausführen?

Hallo akretschmer,

bis jetzt mache ich jede postgresql abfrage ohne Funktionen und Schleifen. Das beruht auf Zeitgründen. Nun hat sich zu mindestens der Zeitgrund etwas geändert und ich will nun die Abfragen auf Funktionen abändern.

Ziel ist es eine SQL Abfrage zu erschaffen die x-mal durchlaufen werden soll und mit Union all am Ende die Ergebnisse zusammengefügt werden sollen. In der Abfrage werden noch ein paar Parameter von Nöten sein. Bei dem Vorgehen habe ich mir folgendes Gedacht. Ich habe ein SQL Select welches Parameter beinhaltet diese Abfrage ist stets gleich nur die Parameter ändern sich immer (hochzählen) das ganze (dachte ich mir)
lasse ich eine variable schreiben und lasse die dann über execute ausführen. Somit sollte dann sowas rauskommen wie

Select 1
Union all
select 2
usw.

Aber pgadmin meckert an meiner Loop rum.
Ist der Weg der richtige oder völlig falscher Ansatz? Könntest du mir zeigen wo mein Fehler liegt?

CREATE OR REPLACE FUNCTION test_zeit3()
  RETURNS SETOF timestamp with time zone AS
$BODY$
DECLARE
 _test varchar(250);
 zaehler integer;
 text_anfang varchar(250);
 text_union varchar(250);
BEGIN

 zaehler := 1;
 text_anfang :='Select ';
 text_union := ' Union all ';


 LOOP
 EXIT WHEN zaehler = 3;

 if zaehler <= 3 
 then text_anfang = text_anfang || zaehler || text_union;
 else 
 text_anfang = text_anfang || zaehler;
 zaehler= zaehler+1;
 
 END LOOP;

 execute text_anfang;

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION test_zeit3()
  OWNER TO postgres;
Select 1
Union all
select 2
usw.

Angenommen, Du willst so die Zahlen von 1 bis 10 haben, dann ginge das einfacher:

 test=*# select s from generate_series(1,10) s;
 s  
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 Zeilen)

hallo an alle,

danke erstmal das ihr alle so nett seit und geantwortet habt.

Aber es ist noch nicht das was ich suche.
Ich habe eine Funktion die mir Werte liefert. Ich möchte die nicht umschreiben! deswegen würde ich gern versuche diese aufzurufen zb. 5mal und die Ergebnisse zu Union.

Das Ziel also folgendes

SELECT * FROM Wertebestimmung(‘2017-04-08’)
UNION
SELECT * FROM Wertebestimmung(‘2017-04-07’)
usw.

das Datum hätte ich stets wie folgt bestimmt. Select date_trunc(‘day’,current_date)+’-6day’
und dann eben -5day oder -4day usw. formuliert. Der Vorteil ist muss mir keine Sorgen machen wie lange ein Monat ist (30 oder 31 Tage).
Deswegen war eben mein Ansatz wie im letzten Post geschrieben.
Ziel ist es also ein bestehende Funktion mit einen stets immer neuen Zeitstempel zu versehen. und das Ergebnis pro Ausführung der Abfrage zu Union. Oder es gibt noch einen besseren Weg. :sunglasses:

Es wäre aber sinnvoller, die Funktion umzuschreiben. So richtest Du N gleiche Abfragen an die DB, diese muß N mal die Abfrage parsen, einen Plan finden und den Plan ausführen. Bei N = 5 und einer kleinn Tabelle mag das noch angehen, aber das skaliert nicht micht wachsenden Anforderungen.

Übrigens: UNION prüft, ob es gleiche Datensätze gibt, und eliminiert diese. Auch das ist zusätzlicher Aufwand. Evtl. suchst Du UNION ALL daher.

tl;dr

Schreibe Deine Funktion um, daß diese ein Start- und ein Enddatum bekommt.

Ich habe die Funktion zwar gerade nicht da aber ich bin ja wissbegierig. Also habe ich mal eine Funktion geschrieben die nichts anders macht als ein übergebenen Zeichenstring zu “Selecten”.

CREATE OR REPLACE FUNCTION Wertebestimmung (  
	IN datum varchar(25)
	)
  RETURNS Table (test varchar(250)) AS
$BODY$
	SELECT datum as test;
$BODY$
  LANGUAGE sql VOLATILE

Nun müsste doch bei Aufruf mit Parameter, das Datum vom 2.4.2017 bis 8.4.2017, als Tabelle angezeigt werden.

SELECT * 
FROM Wertebestimmung(l.dt::date)
  cross join generate_series(current_date - 6, current_date, interval '1' day) as l(dt);

jedoch bekomme ich dann die Fehlermeldung:

FEHLER: fehlender Eintrag in FROM-Klausel für Tabelle „l“
LINE 2: FROM Wertebestimmung(l.dt::date)

Ich muss aber auch gestehen das ich das mit dem as l(dt) noch nie gesehen habe und auch überhaupt nichts darüber finde wie dies funktioniert. Könnte bitte jemand Licht ins dunkel bringen?

Ah, sorry, das muss natürlich anders rum sein:

SELECT w.*
FROM generate_series(current_date - 6, current_date, interval '1' day) as l(dt)
  cross join Wertebestimmung(l.dt::date) w;

Das as l(dt) is ein Alias für die Funktion der auch den Namen der Spalte definiert: https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-TABLE-ALIASES

Unabhängig davon: verwenden niemals VARCHAR für ein Datum (oder Timestamp).

Für ein Datum immer DATE verwenden. Wenn auch die Zeit auch benötigt wird ein TIMESTAMP.

Komplett sieht das dann so aus:

psql (9.6.2)

postgres=> CREATE OR REPLACE FUNCTION Wertebestimmung (IN datum date)
postgres->   RETURNS Table (test date) AS
postgres-> $BODY$
postgres$> SELECT datum as test;
postgres$> $BODY$
postgres-> LANGUAGE sql;
CREATE FUNCTION
postgres=>

postgres=> SELECT w.*
postgres-> FROM generate_series(current_date - 6, current_date, interval '1' day) as l(dt)
postgres->   cross join Wertebestimmung(l.dt::date) w;
    test
------------
 2017-04-02
 2017-04-03
 2017-04-04
 2017-04-05
 2017-04-06
 2017-04-07
 2017-04-08
(7 rows)

postgres=>

das sieht schon mal richtig klasse aus! megadanke für alles!

@castorp kann es sein das es unter postgresql 9.2.4 nicht funktioniert? Habe das gerade auf dem Testsystem auf Arbeit ausprobiert und siehe da hier geht es leider nicht

Fehlermeldung:

FEHLER: ungültiger Verweis auf FROM-Klausel-Eintrag für Tabelle »l«
LINE 3: cross join Wertebestimmung (l.dt::date) w;
^
HINT: Es gibt einen Eintrag für Tabelle »l«, aber auf ihn kann aus diesem Teil der Anfrage kein Bezug genommen werden.

********** Fehler **********

FEHLER: ungültiger Verweis auf FROM-Klausel-Eintrag für Tabelle »l«
SQL Status:42P01
Hinweis:Es gibt einen Eintrag für Tabelle »l«, aber auf ihn kann aus diesem Teil der Anfrage kein Bezug genommen werden.
Zeichen:120

was genau geht denn nicht? Möglicherweise das generate_series() mit Datumsangaben, oder? Kann sein, daß das erst später kam…

@akretschmer du warst zu schnell g siehe oben Fehlermeldung

Du hättest sagen sollen, dass Du eine veraltete Version einsetzt. Der für mein Beispiel notwendige “lateral join” wurde mit der Version 9.3 implementiert.

In Deiner Version musst Du den Funktionsaufruf in die SELECT Liste setzen:

SELECT Wertebestimmung(l.dt::date)
FROM generate_series(current_date - 6, current_date, interval '1' day) as l(dt)

Sinnvoll wäre es aber möglichst schnell auf die aktuelle Version (9.6) umzusteigen
Die 9.2 Linie wird nur noch bis September diesen Jahres unterstützt (und wenn ihr bei 9.2 bleiben müsst, dann aktualisiert das wenigstens auf 9.2.20)

@castorp sorry daran denke ich stets nicht da ich auf meiner privaten Testumgebung auf 9.4… arbeitet. Ein Update von 9.2 auf 9.6 wurde schon angesprochen aber bis jetzt leider nicht umgesetzt! Aber danke noch mal deine Abfrage funktioniert.

oh das ist interessant.
wenn ich nun die Datumsbestimmung von Castorp nutze mit meiner Funktion zusammen erhalte ich folgendes Ergebnis.
“(“2017-04-04 00:00:00+02”,“2017-04-05 00:00:00+02”,6131.63,24,17.85,273.334,3479.44,24,144.977,1684.16,697.75,1480.31)” << das sieht so aus als ob das nun ein einzig großes Array ist?!
Das ist aber nicht das was ich suche. Ich möchte jeden Wert in einer einzelnen Spalte.

Rufe ich meine Funktion mit einen Date auf erhalte ich folgendes:
“2017-04-04 00:00:00+02”;“2017-04-05 00:00:00+02”;6131.63;24;17.85;273.334;3479.44;24;144.977;1684.16;697.75;1480.31(Das Semikolon steht für eine neue Spalte)
Warum werden nun alle Ergebnisse in eine “Spalte” eingebunden?

Ja soweit stimmt das. ich habe Wertebestimmung nun um ein paar weitere Details erweitert. Wenn man dann die Abfrage aufruft sieht das Ergebnis aus als ob die Abfrage als Array zurückgegeben wird.

CREATE OR REPLACE FUNCTION Wertebestimmung (  
	IN datum date
	)
  RETURNS Table (test date, test2 numeric, test3 numeric) AS
$BODY$
	SELECT datum as test, 1.1, 2.2;
$BODY$
  LANGUAGE sql VOLATILE

Das liegt an der Art wie in der alten Version set-returning function in der SELECT Liste verwendet werden (und unter anderem ein Grund warum man diese Syntax heute eben nicht mehr verwenden sollte).

Du musst die Spalten der Funktion expandieren:

SELECT (Wertebestimmung(l.dt::date)).*
FROM generate_series(current_date - 6, current_date, interval '1' day) as l(dt);

Wertebestimmung(l.dt::date) liefert für jede Zeile des Ergebnisses einen anonymen “record” mit drei Elementen in einer einzigen Spalte zurück.

Wenn man aber (Wertebestimmung(l.dt::date)).* verwendet, dann werden die Attribute des record in Spalten “expandiert”.
Bei der Syntax mit einem lateral join wird das automatisch gemacht.