EXTRACT in pl/pgsql mit Variable

Fragen und Probleme zu PL/pgSQL.
Antworten
achilles_69
Benutzer
Beiträge: 51
Registriert: Mi 5. Jul 2006, 10:56
Wohnort: Bielefeld

EXTRACT in pl/pgsql mit Variable

Beitrag von achilles_69 »

Hallo,

ich entwickel gerade in pl/pgsql eine Funktion, die mir zu einer gegebenen KW das Datum des Montags der Woche liefern soll.
Bislang sieht die Funktion so aus (es steht noch 'Müll' drin, den ich später brauchen werde):

Code: Alles auswählen

CREATE OR REPLACE FUNCTION monday_kw(
    integer,    -- KW
    integer     -- Jahr
)
RETURNS INTEGER
AS '
    DECLARE
        kw ALIAS FOR $1;
        jahr ALIAS FOR $2;
        neujahr TEXT;
        wtag_neujahr INTEGER;
        erster_montag DATE;
        montag_kw DATE;

    BEGIN
        -- Neujahr als Datum im Textformat:
        neujahr := trim(to_char(jahr, ''9999'')||''-01-01'');
        --neujahr := ''2006-01-01'';

        -- Wochentag des Neujahrstages bestimmen
        wtag_neujahr := EXTRACT(DOW FROM DATE neujahr);

        RETURN wtag_neujahr;
    END;
'
LANGUAGE plpgsql;
Zur Zeit versuche ich einfach nur, den Wochentag des Neujahrstages zu bestimmen. Wenn ich für neujahr in der Zuweisung wtag_neujahr... direkt das Datum eintrage, funktioniert alles wunderbar. Aber mit der Variablen neujahr klappt es einfach nicht - ich erhalte einfach nur einen Syntax-Error.
Ich habe schon versucht, die Zeile als Text zusammenzubauen und mit EXECUTE auszuführen. Dies geht aber nicht, da EXECUTE das Ergebnis verwirft. Bliebe nur "FOR wtag_neujahr IN EXECUTE ... LOOP", was mir aber unnötig kompliziert erscheint - ich habe auch noch nicht ausprobiert, ob es überhaupt funktioniert.

Es muss doch möglich sein, "EXTRACT ..." mit einer Variable aufzurufen?

Ich verwende noch PostgreSQL 7.4.7, da auf dem Produktivserver zur Zeit noch nichts Neueres zur Verfügung steht.

Vielen Dank für die Hilfe.

Wenn die Funktion fertig ist, werde ich sie auch ganz bestimmt veröffentlichen, da sicher mehr Leute interesse daran hätten...

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

Beitrag von akretschmer »

achilles_69 hat geschrieben:Hallo,

ich entwickel gerade in pl/pgsql eine Funktion, die mir zu einer gegebenen KW das Datum des Montags der Woche liefern soll.
Bislang sieht die Funktion so aus (es steht noch 'Müll' drin, den ich später brauchen werde):

Code: Alles auswählen

CREATE OR REPLACE FUNCTION monday_kw(
    integer,    -- KW
    integer     -- Jahr
)
RETURNS INTEGER
AS '
    DECLARE
        kw ALIAS FOR $1;
        jahr ALIAS FOR $2;
        neujahr TEXT;
        wtag_neujahr INTEGER;
        erster_montag DATE;
        montag_kw DATE;

    BEGIN
        -- Neujahr als Datum im Textformat:
        neujahr := trim(to_char(jahr, ''9999'')||''-01-01'');
        --neujahr := ''2006-01-01'';

        -- Wochentag des Neujahrstages bestimmen
        wtag_neujahr := EXTRACT(DOW FROM DATE neujahr);

        RETURN wtag_neujahr;
    END;
'
LANGUAGE plpgsql;
Zur Zeit versuche ich einfach nur, den Wochentag des Neujahrstages zu bestimmen. Wenn ich für neujahr in der Zuweisung wtag_neujahr... direkt das Datum eintrage, funktioniert alles wunderbar. Aber mit der Variablen neujahr klappt es einfach nicht - ich erhalte einfach nur einen Syntax-Error.
Ich habe schon versucht, die Zeile als Text zusammenzubauen und mit EXECUTE auszuführen. Dies geht aber nicht, da EXECUTE das Ergebnis verwirft. Bliebe nur "FOR wtag_neujahr IN EXECUTE ... LOOP", was mir aber unnötig kompliziert erscheint - ich habe auch noch nicht ausprobiert, ob es überhaupt funktioniert.

Es muss doch möglich sein, "EXTRACT ..." mit einer Variable aufzurufen?

Ich verwende noch PostgreSQL 7.4.7, da auf dem Produktivserver zur Zeit noch nichts Neueres zur Verfügung steht.

Vielen Dank für die Hilfe.

Wenn die Funktion fertig ist, werde ich sie auch ganz bestimmt veröffentlichen, da sicher mehr Leute interesse daran hätten...




Der CAST in "wtag_neujahr := EXTRACT(DOW FROM DATE neujahr);" schlägt wahrscheinlich fehl, ich bevorzuge
da ja die Form foo::DATE, SQL-konform wäre cast('foo' as date).

Du hast noch 7.4, das kennt keine Funktion generate_series. Damit ließe sich Dein Problem so lösen:

select tag::date from (select '2006-01-01'::date +
(generate_Series(0,366)||'days')::interval as tag) foo
where extract(dow from foo.tag)=1 and extract(week from foo.tag)=4;


Also, für das Jahr 2006 und der KW 4 wird der Wochentag = 1 ausgegeben.

achilles_69
Benutzer
Beiträge: 51
Registriert: Mi 5. Jul 2006, 10:56
Wohnort: Bielefeld

Beitrag von achilles_69 »

Jau, der Hinweis mit dem Cast war gut!
So funktionierts: wtag_neujahr := extract(dow from neujahr::date);
Ich war mir eigentlich sicher, diese Variante ausprobiert zu haben - war wohl doch nicht der Fall.

Danke!

achilles_69
Benutzer
Beiträge: 51
Registriert: Mi 5. Jul 2006, 10:56
Wohnort: Bielefeld

Beitrag von achilles_69 »

Zu früh gefreut:
Den Wochentag des Neujahrstages kann ich jetzt problemlos berechnen. Aber um das Datum des ersten Montags zu kriegen, muss ich einen Korrekturwert berechnen und diesen als Intervall auf den Neujahrstag hinzurechnen.
Wie dieser Korrekturwert berechnet wird, ist jetzt erstmal uninteressant (das habe ich schon), aber ich muss folgendes haben:

erster_montag := neujahr: :D ATE + INTERVAL ''korrektur DAYS'';


Wie kann ich für korrektur den richtigen Wert einsetzen? Ich habe zig Varianten durchprobiert, das ganze zu quoten, habe den Zusatz ''korrektur DAYS'' einer Text-Variablen zugewiesen und die hinter INTERVAL eingesetzt. Hat alles nicht geklappt.
So langsam bin ich am Verzweifeln. Es muss doch möglich sein, in PL/PGSQL vernünftig mit Daten zu rechnen?

Vielen Dank schon mal.

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

Beitrag von akretschmer »

achilles_69 hat geschrieben:Zu früh gefreut:
Den Wochentag des Neujahrstages kann ich jetzt problemlos berechnen. Aber um das Datum des ersten Montags zu kriegen, muss ich einen Korrekturwert berechnen und diesen als Intervall auf den Neujahrstag hinzurechnen.
Wie dieser Korrekturwert berechnet wird, ist jetzt erstmal uninteressant (das habe ich schon), aber ich muss folgendes haben:



Wie kann ich für korrektur den richtigen Wert einsetzen? Ich habe zig Varianten durchprobiert, das ganze zu quoten, habe den Zusatz ''korrektur DAYS'' einer Text-Variablen zugewiesen und die hinter INTERVAL eingesetzt. Hat alles nicht geklappt.
So langsam bin ich am Verzweifeln. Es muss doch möglich sein, in PL/PGSQL vernünftig mit Daten zu rechnen?

Vielen Dank schon mal.



Geh zurück und lies noch mal, was ich zum korrekten CASTing schrieb.

Hint: select current_date + '5days'::interval;

Und ja: man kann in plpgsql vernünftig arbeiten. ;-)

achilles_69
Benutzer
Beiträge: 51
Registriert: Mi 5. Jul 2006, 10:56
Wohnort: Bielefeld

Beitrag von achilles_69 »

akretschmer hat geschrieben:Geh zurück und lies noch mal, was ich zum korrekten CASTing schrieb.

Hint: select current_date + '5days'::interval;

Und ja: man kann in plpgsql vernünftig arbeiten. ;-)


Super, jetzt geht's! Ich bin einfach nicht auf die Idee gekommen, dass interval ein eigener Datentyp ist.
Ich habe auch nie wirklich gezweifelt, dass man in plpgsql nicht vernünftig arbeiten könne. Bisher habe ich eigentlich auch immer alles damit hingekriegt...

Nochmal vielen vielen Dank!

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

Beitrag von akretschmer »

achilles_69 hat geschrieben:
Ich habe auch nie wirklich gezweifelt, dass man in plpgsql nicht vernünftig arbeiten könne. Bisher habe ich eigentlich auch immer alles damit hingekriegt...

Nochmal vielen vielen Dank!


Yes! ;-)

achilles_69
Benutzer
Beiträge: 51
Registriert: Mi 5. Jul 2006, 10:56
Wohnort: Bielefeld

Beitrag von achilles_69 »

So, nachdem ich fast den ganzen Tag an der Funktion herumgebastelt habe, habe ich endlich eine funktionierende Version erstellt, hier ist sie:

Code: Alles auswählen

CREATE OR REPLACE FUNCTION monday_kw(
    integer,    -- KW
    integer     -- Jahr
)
RETURNS DATE
AS '
    DECLARE
        kw ALIAS FOR $1;
        jahr ALIAS FOR $2;
        neujahr TEXT;
        wtag_neujahr INTEGER;
        korrektur TEXT;
        tage_bis_kw TEXT;
        erster_montag DATE;

    BEGIN
        -- Neujahr als Datum im Textformat:
        neujahr := trim(to_char(jahr, ''9999'')||''-01-01'');

        -- Wochentag des Neujahrstages bestimmen
        wtag_neujahr := EXTRACT(DOW FROM neujahr: :D ATE);

        -- DOW liefert den Tag der Woche nach amerikanischer Zaehlweise,
        -- also 1 fuer Sonntag und 7 fuer Samstag.
        -- Wir brauchen aber den Montag als ersten Tag der Woche und
        -- den Sonntag als siebten Tag der Woche:
        wtag_neujahr := ((wtag_neujahr + 12) % 7) + 1;

        -- Datum des ersten Montags des Jahres bestimmen
        -- Dazu einen Korrekturwert berechnen - entweder auf
        -- den letzten Montag zurueckrechnen oder zum naechsten
        -- Montag hinzurechnen:
        IF (wtag_neujahr <= 3) THEN

            korrektur := trim((0-wtag_neujahr)::TEXT)||'' DAYS'';
        ELSE

            korrektur := trim((7-wtag_neujahr)::TEXT)||'' DAYS'';
        END IF;

        -- Datum des Montags der KW 1 bestimmen.
        -- Achtung: kann auch im Vorjahr liegen!
        erster_montag := neujahr: :D ATE + korrektur::INTERVAL;

        -- Die Anzahl der Tage bis zur gesuchten KW bestimmen:
        tage_bis_kw := trim(((kw - 1) * 7)::TEXT)||'' DAYS'';

        -- Intervall zum ersten Montag hinzurechnen und Ergebnis liefern
        RETURN erster_montag: :D ATE + tage_bis_kw::INTERVAL;
    END;
'
LANGUAGE plpgsql
ich habe sie mit hoffentlich verständlichen Kommentaren versehen.
Wahrscheinlich findet irgendjemand noch eine Super-Einfach-Version, die nur eine Zeile benötigt...

Und noch einmal vielen Dank an den netten Helfer!

Antworten