Dear All,

How to create Calendar Function or Query. I would like to display date form yyyy-mm-dd to yyyy-mm-dd or display date in one Month

e.g:

date
------------
2005-01-01
2005-01-02
2005-01-03
2005-01-04
2005-01-05
2005-01-06
2005-01-07
2005-01-08
2005-01-09
2005-01-10
2005-01-11
2005-01-12
2005-01-13
2005-01-14
2005-01-15
2005-01-16
2005-01-17
2005-01-18
2005-01-19
2005-01-20
2005-01-21
2005-01-22
2005-01-23
2005-01-24
2005-01-25
2005-01-26
2005-01-27
2005-01-28
2005-01-29
2005-01-30
2005-01-31

maybe somthing like this:

CREATE OR REPLACE FUNCTION calendar (DATE, DATE) RETURNS SETOF DATE
LANGUAGE 'plpgsql' AS '
DECLARE
v_from ALIAS FOR $1;
v_to ALIAS FOR $2;
v_current DATE DEFAULT v_from;
BEGIN
WHILE (v_current<=v_to) LOOP
RETURN NEXT v_current;
v_current:=v_current+1;
END LOOP;

RETURN;
END;
';

test it:
SELECT * FROM calendar('2005-01-01', '2005-01-31');


Muhyiddin A.M Hayat wrote:

> Dear All,
>
> How to create Calendar Function or Query. I would like to display date
> form yyyy-mm-dd to yyyy-mm-dd or display date in one Month
>
> e.g:
>
> date
> ------------
> 2005-01-01
> 2005-01-02
> 2005-01-03
> 2005-01-04
> 2005-01-05
> 2005-01-06
> 2005-01-07
> 2005-01-08
> 2005-01-09
> 2005-01-10
> 2005-01-11
> 2005-01-12
> 2005-01-13
> 2005-01-14
> 2005-01-15
> 2005-01-16
> 2005-01-17
> 2005-01-18
> 2005-01-19
> 2005-01-20
> 2005-01-21
> 2005-01-22
> 2005-01-23
> 2005-01-24
> 2005-01-25
> 2005-01-26
> 2005-01-27
> 2005-01-28
> 2005-01-29
> 2005-01-30
> 2005-01-31
>
>

Ok, thanks

But if i would like to display date in one Month,

e.g :
date in feb 2005

calendar
------------
2005-02-01
2005-02-02
2005-02-03
2005-02-04
2005-02-05
2005-02-06
2005-02-07
2005-02-08
2005-02-09
2005-02-10
2005-02-11
2005-02-12
2005-02-13
2005-02-14
2005-02-15
2005-02-16
2005-02-17
2005-02-18
2005-02-19
2005-02-20
2005-02-21
2005-02-22
2005-02-23
2005-02-24
2005-02-25
2005-02-26
2005-02-27
2005-02-28

date in feb 2004

calendar
------------
2004-02-01
2004-02-02
2004-02-03
2004-02-04
2004-02-05
2004-02-06
2004-02-07
2004-02-08
2004-02-09
2004-02-10
2004-02-11
2004-02-12
2004-02-13
2004-02-14
2004-02-15
2004-02-16
2004-02-17
2004-02-18
2004-02-19
2004-02-20
2004-02-21
2004-02-22
2004-02-23
2004-02-24
2004-02-25
2004-02-26
2004-02-27
2004-02-28
2004-02-29

----- Original Message -----
From: Franco Bruno Borghesi
To: Muhyiddin A.M Hayat
Cc: pgsql-sql@postgresql.org
Sent: Friday, January 28, 2005 11:46 PM
Subject: Re: [SQL] Calendar Function


maybe somthing like this:

CREATE OR REPLACE FUNCTION calendar (DATE, DATE) RETURNS SETOF DATE LANGUAGE 'plpgsql' AS '
DECLARE
v_from ALIAS FOR $1;
v_to ALIAS FOR $2;
v_current DATE DEFAULT v_from;
BEGIN
WHILE (v_current<=v_to) LOOP
RETURN NEXT v_current;
v_current:=v_current+1;
END LOOP;

RETURN;
END;
';

test it:
SELECT * FROM calendar('2005-01-01', '2005-01-31');


Muhyiddin A.M Hayat wrote:
Dear All,

How to create Calendar Function or Query. I would like to display date form yyyy-mm-dd to yyyy-mm-dd or display date in one Month

e.g:

date
------------
2005-01-01
2005-01-02
2005-01-03
2005-01-04
2005-01-05
2005-01-06
2005-01-07
2005-01-08
2005-01-09
2005-01-10
2005-01-11
2005-01-12
2005-01-13
2005-01-14
2005-01-15
2005-01-16
2005-01-17
2005-01-18
2005-01-19
2005-01-20
2005-01-21
2005-01-22
2005-01-23
2005-01-24
2005-01-25
2005-01-26
2005-01-27
2005-01-28
2005-01-29
2005-01-30
2005-01-31

Muhyiddin A.M Hayat wrote:
> Ok, thanks
>
> But if i would like to display date in one Month,
>
> e.g :
> date in feb 2005

You can do that in Pg date arithmetic:

# select '1 oct 2004'::date + '1 month'::interval - '1 day'::interval;
?column?
---------------------
2004-10-31 00:00:00
(1 row)

# select '1 nov 2004'::date + '1 month'::interval - '1 day'::interval;
?column?
---------------------
2004-11-30 00:00:00
(1 row)

# select '1 feb 2004'::date + '1 month'::interval - '1 day'::interval;
?column?
---------------------
2004-02-29 00:00:00
(1 row)


Thus, given the original response to your question:

select * from calendar('1 feb 2004', ( '1 feb 2004'::date +
'1 month'::interval - '1 day'::interval )::date);


--
Jeff Boes vox 269.226.9550 ext 24
http://www.nexcerpt.com fax 269.349.9076

...Nexcerpt... Extend your Expertise

On Wed, Feb 02, 2005 at 09:33:22AM -0600, Bradley Miller wrote:

> Is there a way to dump everything in a particular schema?

See the documentation for pg_dump. In PostgreSQL 7.4 and later,
pg_dump has a --schema (-n) option.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

am Wed, dem 02.02.2005, um 9:33:22 -0600 mailte Bradley Miller folgendes:
> Is there a way to dump everything in a particular schema?

RTFM.

man pg_dump, search for --schema


Regards, Andreas
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org