Re: PL/PGSQL

Hi,

Welcome aboard :)

Jordi Martínez <jmartinez(at)uic(dot)es> writes:
> 1.- Exception throwed by cast('yyyymmdd' as timestamp)
>
> I do a cast conversion from a string to a timestamp. When I put a bad
> date on the string, the cast throw an error on my PL/PGSQL but I can't
> capture (I'm new). Which type of exception launch the cast function?

The exhaustive list of error codes is there:

http://www.postgresql.org/docs/8.3/static/errcodes-appendix.html

It should probably be in Class 22 — Data Exception, and I'd bet one of
those, depending on the faulty input:

22008 DATETIME FIELD OVERFLOW datetime_field_overflow
22007 INVALID DATETIME FORMAT invalid_datetime_format

> 2.- Number of days on February
>
> I'm parsing a string with a date with format 'yyyymmdd' for testing if it's a valid date.
>
> I need to know if the day is correct for the month on the string, but
> February is different for every year. How can I know if February have
> 28 or 29 days depending on the year?

The usual trick is to ask for the day number of 03/01 preceeding day:

dim=# select x::date, extract(day from x::date - interval '1 day')
from (values('20090301'), ('20080301'),
('20040301'), ('20000301')) as t(x);
x | date_part
------------+-----------
03-01-2009 | 28
03-01-2008 | 29
03-01-2004 | 29
03-01-2000 | 29
(4 lignes)

Regards,
--
dim