Probleme mit FOR Schleife

Fragen und Probleme zu PL/pgSQL.
tutter
Neuer Benutzer
Beiträge: 23
Registriert: Mo 28. Nov 2016, 22:31

Probleme mit FOR Schleife

Beitrag von tutter » Mi 5. Apr 2017, 09:51

Hallo ihr lieben,

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

Code: Alles auswählen


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.

tutter
Neuer Benutzer
Beiträge: 23
Registriert: Mo 28. Nov 2016, 22:31

Re: Probleme mit FOR Schleife

Beitrag von tutter » Mi 5. Apr 2017, 10:34

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

Code: Alles auswählen


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?

akretschmer
Erfahrener Benutzer
Beiträge: 7677
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Probleme mit FOR Schleife

Beitrag von akretschmer » Sa 8. Apr 2017, 11:14

ändere das in :

Code: Alles auswählen

       return query SELECT 1 as test;
warum willst Du eine Abfrage in einer Schleife ausführen?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

tutter
Neuer Benutzer
Beiträge: 23
Registriert: Mo 28. Nov 2016, 22:31

Re: Probleme mit FOR Schleife

Beitrag von tutter » Sa 8. Apr 2017, 13:20

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?

Code: Alles auswählen


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;



akretschmer
Erfahrener Benutzer
Beiträge: 7677
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Probleme mit FOR Schleife

Beitrag von akretschmer » Sa 8. Apr 2017, 13:50

Code: Alles auswählen

Select 1
Union all
select 2
usw.
Angenommen, Du willst so die Zahlen von 1 bis 10 haben, dann ginge das einfacher:

Code: Alles auswählen

 test=*# select s from generate_series(1,10) s;
 s  
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 Zeilen)
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

castorp
Erfahrener Benutzer
Beiträge: 1174
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Probleme mit FOR Schleife

Beitrag von castorp » Sa 8. Apr 2017, 14:01

tutter hat geschrieben:
Sa 8. Apr 2017, 13:20
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.
Dafür brauchst Du kein UNION:

Code: Alles auswählen

CREATE OR REPLACE FUNCTION test_zeit3()
  RETURNS SETOF timestamp with time zone AS
$BODY$
DECLARE
 zaehler integer;
BEGIN
 for zaehler in 1..3 loop LOOP
   return query 
      select timestamp_column
      from some_table 
      where come_column = zahler;
 end loop;
END;
$BODY$
  LANGUAGE plpgsql;
Aber das ist auch ein falscher Ansatz.

So wie der Parameter verwendet wird, kann man das Ganze noch weiter vereinfachen und kann eine (effizientere) SQL Funktion verwenden (anstatt PL/pgSQL):

Code: Alles auswählen

CREATE OR REPLACE FUNCTION test_zeit3()
  RETURNS SETOF timestamp with time zone AS
$BODY$
   select timestamp_column
   from some_table 
   where come_column in (1,2,3);
$BODY$
  LANGUAGE sql;
Wenn die obere Grenze der Schleife nicht fest ist, dann kann man auch sowas machen:

Code: Alles auswählen

CREATE OR REPLACE FUNCTION test_zeit3()
  RETURNS SETOF timestamp with time zone AS
$BODY$
   select timestamp_column
   from some_table 
   where come_column in (select i from generate_series(1,3) i);
$BODY$
  LANGUAGE sql;
Die folgende Logik:
EXIT WHEN zaehler = 3;

if zaehler <= 3
ist übrigens falsch. Da das EXIT WHEN vor dem IF steht, wird die Schleife bevor der Wert 3 zu verwendet wird.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

tutter
Neuer Benutzer
Beiträge: 23
Registriert: Mo 28. Nov 2016, 22:31

Re: Probleme mit FOR Schleife

Beitrag von tutter » Sa 8. Apr 2017, 16:57

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. :cool:

akretschmer
Erfahrener Benutzer
Beiträge: 7677
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Probleme mit FOR Schleife

Beitrag von akretschmer » Sa 8. Apr 2017, 17:09

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.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

castorp
Erfahrener Benutzer
Beiträge: 1174
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Probleme mit FOR Schleife

Beitrag von castorp » Sa 8. Apr 2017, 17:24

tutter hat geschrieben:
Sa 8. Apr 2017, 16:57
Oder es gibt noch einen besseren Weg
Ja, den gibt es:

Code: Alles auswählen

SELECT * 
FROM Wertebestimmung(l.dt::date)
  cross join generate_series(current_date - 6, current_date, interval '1' day) as l(dt);
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

tutter
Neuer Benutzer
Beiträge: 23
Registriert: Mo 28. Nov 2016, 22:31

Re: Probleme mit FOR Schleife

Beitrag von tutter » Sa 8. Apr 2017, 20:43

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".

Code: Alles auswählen

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.

Code: Alles auswählen

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?

Antworten