Re: type cast/validation functions

On Wed, Dec 29, 2004 at 12:12:53AM +0000, Robert Wimmer wrote:
> >
> >Would 8.0's subtransactions, in particular PL/pgSQL's new error
> >trapping construct, solve your problem?
>
> that is what i have been looking for - as i posted my problem the first
> time. but i was told there is absolutly no way to catch any errors in
> plpgsql - so i wrote my parser extensions.

Prior to 8.0 that was true. There might also have been some
misunderstanding about what you were looking for.

> besides you only can produce very modest error codes this way.

What would you like to be able to do?

> another problem is
>
> - i use postgresql 7.2 (debian stable (how can i update ?))

See the "Installation Instructions" chapter of the PostgreSQL
documentation.

> - couldnt find any documentation about this new features in postgresql 8.0

The 8.0 Release Notes don't mention PL/pgSQL's error trapping but
they do mention Savepoints. The PL/pgSQL chapter in the documentation
describes error trapping in the "Control Structures" section.

> - another drawback using postgres type cast functions (or the most standard
> type cast functions) is, that they try to be some sort of 'intelligent',
> but i want strict validation . so a date like '04-30-02' wil be casted to
> 30th Jan 2004.

Eh? Are you sure you wrote that correctly? Could you copy and
paste the exact SQL statement you executed and the exact output?
With my system's settings, '04-30-02' becomes '2002-04-30', or
30 Apr 2002 (but see the discussion of DateStyle below).

> why 2004 ? and not 1904 my grandfather was born this year.

Why 1904 and not 2004, the year lots of other people were born?
Any time you deal with two-digit years you're going to run into
this ambiguity. You're also going to have trouble with date format
conventions that vary from country to country -- some write DD-MM-YY,
while others write MM-DD-YY. See PostgreSQL's DateStyle configuration
variable (introduced in 7.3) for a way to tell PostgreSQL which
style it should prefer.

> and '04-02-03' will be casted to 3rd Feb 2004 so the second date field is
> the month, in the first example the 3rd field is the month field. this is
> in conflict to ISO 8061 and pseudo intelligent. this is that sort of
> incosistent behavior i dont like and at least is userUNfriendly.

I think you mean ISO 8601. PostgreSQL 7.3 introduced the DateStyle
configuration variable so you can tell PostgreSQL how to interpret
dates:

SET DateStyle TO ISO, DMY;
SELECT '01-02-03'::DATE;
date
------------
2003-02-01
(1 row)

SET DateStyle TO ISO, MDY;
SELECT '01-02-03'::DATE;
date
------------
2003-01-02
(1 row)

SET DateStyle TO ISO, YMD;
SELECT '01-02-03'::DATE;
date
------------
2001-02-03
(1 row)

You could also use the to_date() function:

SELECT to_date('01-02-03', 'MM-DD-YY');
to_date
------------
2003-01-02
(1 row)

> if you can give me a hint where i find more about postgre 8.0 i really
> would be pleased

See the Release Notes appendix in the PostgreSQL 8.0 documentation.
You can get it via FTP or BitTorrent by clicking "Downloads" on the
PostgreSQL web site (http://www.postgresql.org/). You can also
view the 8.0 documentation online by clicking the "Developers" link.

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