Funktion mit mehrfachem return query execute

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
esser
Neuer Benutzer
Beiträge: 1
Registriert: Di 15. Jul 2014, 14:53

Funktion mit mehrfachem return query execute

Beitrag von esser »

Hallo zusammen,

ich hab mir gerade ein Performance-Problem gebaut. meine Funktion soll eine Tabelle mit Zeitpunkten und dazugehörigen Werten zurück liefern. Die Funktion bekommt als einen Parameter die gewünschte zeitliche Auflösung übergeben, was bis zur Stundenebene kein Problem ist, da diese Werte in einer zusätzliche Tabelle abgelegt werden. Problematisch wird es bei der online Aggregierung zu Tagen, Wochen oder Monaten.
Meine "Lösung" bis jetzt ist eine Funktion, die mir jeweils die Wechsel zwischen den Zeiträumen (z.B. Tagen) liefert, und dann mit for timestamp aus dieser Funktion mein SQL-Statement als storded-function mit Start- und endzeitpunkt aufzurufen.
etwas zusammengekürzt sieht meine Funktion so aus:

Code: Alles auswählen

CREATE OR REPLACE FUNCTION valueitem_result(itemid integer, unit integer, select_type integer, starttime timestamp without time zone, endtime timestamp without time zone, windows_timezone text)
  RETURNS table ("Mittelwert" double precision, "TimeStamp" timestamp without time zone) AS
$BODY$
   declare
      sql text;
      result record;
      select_string text;
      replace_text text;
      timespan text;
      selected_timestamp timestamp without time zone;
      first_timestamp timestamp without time zone;
      last_timestamp timestamp without time zone;
      function_name text;
   begin
      if unit > 4 then
         sql := 'SELECT kennzahlsql from get_valueitem_sqlstring('||itemid||',4,'||select_type||');';
         execute sql into result;
         sql := result.kennzahlsql;
         sql := replace(sql, ':from', '$1');
         sql := replace(sql, ':to', '$2');
         function_name := 'body_'||itemid;
         sql:='prepare '||function_name||' (timestamp without time zone,timestamp without time zone) as '||sql;
         execute sql;
         sql := 'SELECT * FROM create_day_series('||unit||','||quote_literal(starttime)||','||quote_literal(endtime)||','||quote_literal(windows_timezone)||');';
         for selected_timestamp in execute sql LOOP
            last_timestamp := selected_timestamp;
            if first_timestamp is null Or first_timestamp = last_timestamp then
               --erster oder letzter Wert der Serien
            else
               sql:='execute '||function_name||'('||quote_literal(first_timestamp)||','||quote_literal(last_timestamp)||')';
               return query execute sql;
            end if;
            first_timestamp := selected_timestamp;
         end loop;
         sql:='deallocate '||function_name||';';
         Execute sql;
         return ;
      else
         sql := 'SELECT * from get_valueitem_sqlstring('||itemid||','||unit||','||select_type||');';
         execute sql into result;
         select_string := result.kennzahlsql;
         replace_text := quote_literal(starttime);
         select_string := replace(select_string, ':from', replace_text);
         replace_text := quote_literal(endtime);
         select_string := replace(select_string, ':to', replace_text);
         return query execute select_string;
      end if;
   end;
   $BODY$
  LANGUAGE plpgsql VOLATILE;


Das Ergebnis von create_day_series ist eine Liste von timestamp without timzone
(... ich weiß, keine gute Idee, aber da kann ich jetzt nichts dran ändern, da alle Zeitstempel in der Datenbank ohne Zeitzone gespeichert werden, aber sie werden explizit als UTC gespeichert)
Das Ergebnis von get_valueitem_sqlstring ist ein select, das zum Rückgabetyp der Funktion passt, hier mal zwei Beispiele:

Code: Alles auswählen

SELECT sum ( "Item"."Mittelwert" ) AS "Mittelwert", LAST("Item"."TimeStamp") AS "TimeStamp" FROM ( SELECT Distinct cast ( "AGG_Hour_SID_400073"."Mittelwert"-lag("AGG_Hour_SID_400073"."Mittelwert") OVER ( ORDER BY "AGG_Hour_SID_400073"."TimeStamp") AS DOUBLE PRECISION) AS "Mittelwert", ("AGG_Hour_SID_400073"."TimeStamp")::TIMESTAMP WITHOUT TIME ZONE AS "TimeStamp" FROM "AGG_Hour_SID_400073" WHERE ("AGG_Hour_SID_400073"."TimeStamp" >= :from AND "AGG_Hour_SID_400073"."TimeStamp" <= :to) ORDER BY "TimeStamp" ) AS "Item";


SELECT sum ( "Item"."Mittelwert" ) AS "Mittelwert", LAST("Item"."TimeStamp") AS "TimeStamp" FROM ( SELECT Distinct cast ( ("AGG_Hour_SID_10067"."Mittelwert" - "AGG_Hour_SID_10066"."Mittelwert") AS DOUBLE PRECISION) AS "Mittelwert", ("AGG_Hour_SID_10067"."TimeStamp")::TIMESTAMP WITHOUT TIME ZONE AS "TimeStamp" FROM "AGG_Hour_SID_10067", "AGG_Hour_SID_10066" WHERE ("AGG_Hour_SID_10067"."TimeStamp" > :from AND "AGG_Hour_SID_10067"."TimeStamp" <= :to AND "AGG_Hour_SID_10067"."TimeStamp" = "AGG_Hour_SID_10066"."TimeStamp") ORDER BY "TimeStamp" ) AS "Item";


jetzt zu der Frage: kann man den Loop mit "for selected_timestamp in execute sql LOOP" und seinem "return query execute sql;
" irgendwie geschickter bauen, so dass das nicht so extrem lange dauert?

vielen Dank schon mal für die mühen ;-)
Marco

Amerika_befreie_uns
Neuer Benutzer
Beiträge: 8
Registriert: Di 10. Jan 2017, 11:49

Re: Funktion mit mehrfachem return query execute

Beitrag von Amerika_befreie_uns »

Hallo Marco,

achte mal bitte auf deine Rechtschreibung(Wir sind zwar nur eine amerikanische Kolonie aber man kann sich ja wenigstens anstrengen: Link wegen zweifelhaften Inhalt entfernt ).

Aber nun zu deiner Frage:
Du kannst das ganze optimieren, indem Du Indexierungen erstellst, allerdings wirst Du auch damit irgendwann an die Grenzen stoßen.
Um komplexe Probleme zu lösen musst Du schon mit eine Programmiersprache wie C++ oder Python arbeiten.

Amerika_befreie_uns
Neuer Benutzer
Beiträge: 8
Registriert: Di 10. Jan 2017, 11:49

Re: Funktion mit mehrfachem return query execute

Beitrag von Amerika_befreie_uns »

Entschuldigung ich wusste nicht, dass hier im Forum zensiert wird.

Hier etwas genauer ausgedrückt:
... Du mußt schon mit einer Programmiersprache arbeiten die kontinuierlich weiterläuft, wie z.B. C++ oder Python ...

Xandrian
Erfahrener Benutzer
Beiträge: 1695
Registriert: Mi 23. Nov 2005, 09:17
Wohnort: Bayreuth
Kontaktdaten:

Re: Funktion mit mehrfachem return query execute

Beitrag von Xandrian »

Zwar Off-Topic aber Klarstellung für die Zukunft: Es geht hier nicht um Zensur sondern darum, dass es sich hier um ein Fachforum handelt. Andere Themen, vor allem mit zweifelhaften Inhalt haben hier nichts verloren. Des weiteren geht es hier um die Haftung mit der ich einstehen muss wenn es um Links geht (siehe hierzu die Entscheidungen des LG HH sowie des EUGH). Meinungsfreiheit ja aber was solche Aussagen angeht nicht hier im Forum. Hier gehts um PostgreSQL. Alles andere kannst du am Stammtisch machen. So und jetzt zurück zum Thema. Was hier sonst noch zu diesem Thema kommt werde ich ohne Kommentar löschen mit dem Verweis auf das Hausrecht.

cu tb.
pg-forum.de unterstützen? Meine Wunschliste

Bild

Antworten