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
Message-ID: <BAY10-F57958D6BCA3313F07B0C10D09B0@phx.gbl> (raw)
In-Reply-To: <20041228225028.GA68003@winnie.fuhr.org>
>
>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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: seppwimmer@hotmail.com, mike@fuhr.org
Subject: Re: type cast/validation functions
In-Reply-To: <BAY10-F57958D6BCA3313F07B0C10D09B0@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox