EnderUNIX Team.


EnderUNIX İpucu

Arkadaşıma gönder , Ana Sayfa

[ PostgreSQL ]

"Oracle daki Next_day fonsiyonunun pgsql uyarlamasý" - Bülent Kaptan - (2008-02-02 05:54:44)   [2211]

Next_day fonksiyonunu örnekle aciklamak daha uygun:
02 subat 2008(2008-02-02) den sonraki ilk sali günü 5 Subat 2008 (2008-02-05) oldugunu hesaplayan fonksiyondur.

Iki ayri fonsiyon göstercegim. Ilki verilen gune gore ikinici ise gunun integer degerine göre

ONEMLI NOT : Ipucunu eklerken tirnaklar slash tirnak olarak cikti. Kullanýrken dikkat edin

/**********1. Fonksiyon *******************/

CREATE OR REPLACE FUNCTION next_day1(IN _d date, IN _day varchar) RETURNS date AS $$
DECLARE _id integer; _dow integer;
BEGIN
_dow := EXTRACT(dow FROM _d);
_id := CASE lower(_day)
WHEN \\\'sunday\\\' THEN 0
WHEN \\\'monday\\\' THEN 1
WHEN \\\'tuesday\\\' THEN 2
WHEN \\\'wednesday\\\' THEN 3
WHEN \\\'thursday\\\' THEN 4
WHEN \\\'friday\\\' THEN 5
WHEN \\\'saturday\\\' THEN 6
ELSE
CAST(ERROR(E\\\'Wrong identifier for day \\\\\\\'\\\'||_day||E\\\'\\\\\\\'\\\') AS integer)
END;
RETURN CASE _id <= _dow
WHEN true THEN _d + (_id - _dow + 7)
WHEN false THEN _d + (_id - _dow)
END;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;


/**********2. Fonksiyon *******************/

CREATE OR REPLACE FUNCTION next_day2(IN _d date, IN _day integer) RETURNS date AS $$
DECLARE _id integer; _dow integer;
BEGIN
_dow := EXTRACT(dow FROM _d);
RETURN CASE _day <= _dow
WHEN true THEN _d + (_day - _dow + 7)
WHEN false THEN _d + (_day - _dow)
END;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;


/**********Ornek Kullanýmý*******************/

Ornek sql 1 :
select next_day1(cast(\\\'2008-02-02\\\' as date),\\\'tuesday\\\');
cevap :
2008-02-05

Ornek sql 2 :
select next_day2(now,2);
cevap :
2008-02-05

Not: Alintidir

Arkadaşıma gönder , Ana Sayfa