Message-ID: <BAY10-F57958D6BCA3313F07B0C10D09B0@phx.gbl>
In-Reply-To: <20041228225028.GA68003@winnie.fuhr.org>
From: "Robert Wimmer" <seppwimmer@hotmail.com>
To: mike@fuhr.org
Cc: pgsql-interfaces@postgresql.org
Subject: Re: type cast/validation functions
Date: Wed, 29 Dec 2004 00:12:53 +0000
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed


>
>Would 8.0's subtransactions, in particular PL/pgSQL's new error
>trapping construct, solve your problem?
>
>CREATE OR REPLACE FUNCTION text2date(TEXT) RETURNS DATE AS $$
>DECLARE
>     dat  DATE;
>BEGIN
>     BEGIN
>         dat := CAST($1 AS DATE);
>     EXCEPTION
>         WHEN invalid_datetime_format OR datetime_field_overflow THEN
>             RAISE INFO 'Bogus date ''%'', returning NULL', $1;
>             dat := NULL;
>     END;
>
>     RETURN dat;
>END;
>$$ LANGUAGE plpgsql IMMUTABLE STRICT;
>

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. besides you only can produce very 
modest  error codes this way.  another problem is

- i use postgresql 7.2 (debian stable (how can i update ?))
- couldnt find any documentation about this new features in postgresql 8.0
- as i wrote my solution my only intention was to solve the problem in 
postgres but working on it, i recognized that one can use this c-functions 
in any enviroment. so you can build - if you want - some sort of type cast 
server, that can be used by  nearly every application.
- 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.  why 2004 ? and not 1904 my grandfather was born this year. 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.

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

regards sepp wimmer

_________________________________________________________________
Hotmails und Messenger-Kommunikation am Handy? Für MSN Mobile kein Problem! 
http://www.msn.at/msnmobile/