Re: type cast/validation functions

From: "Robert Wimmer" <seppwimmer(at)hotmail(dot)com>
To: mike(at)fuhr(dot)org
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: type cast/validation functions
Date: 2004-12-29 00:12:53
Message-ID: BAY10-F57958D6BCA3313F07B0C10D09B0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces


>
>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/

In response to

Responses

Browse pgsql-interfaces by date

  From Date Subject
Next Message Michael Fuhr 2004-12-29 01:31:41 Re: type cast/validation functions
Previous Message Michael Fuhr 2004-12-28 22:50:28 Re: type cast/validation functions