Обсуждение: type cast/validation functions
as i posted a few weeks ago, it is a handicap for me writing client side appliciations with postgres, that you cant catch errorcodes - especially after type casts. so you have to rewrite type check functions for every client system. programers life would become a lot easier if one would be able to let the db-system do this work. to remove this drawback i wrote a c-module for parsing/casting input data and integrated it into postgres. if someone is interested in my solution there is a demo and a short description available at http://www.wiro.co.at/postgres/test/demo_en.php please let me know, what you think about this solution. regards sepp wimmer _________________________________________________________________ Match.com - ein Ort, an dem der Liebe keine Grenzen gesetzt sind! http://match.msn.at
On Tue, Dec 28, 2004 at 09:50:09PM +0000, Robert Wimmer wrote: > > as i posted a few weeks ago, it is a handicap for me writing client side > appliciations with postgres, that you cant catch errorcodes - especially > after type casts. so you have to rewrite type check functions for every > client system. 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; > if someone is interested in my solution there is a demo and a short > description available at > > http://www.wiro.co.at/postgres/test/demo_en.php This link returns 404 Not Found -- the correct link appears to be: http://www.wiro.co.at/postgres/demo_en.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/
> >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/
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/
am Wed, dem 29.12.2004, um 0:12:53 +0000 mailte Robert Wimmer folgendes: > - i use postgresql 7.2 (debian stable (how can i update ?)) deb http://mirror.xaranet.de/debian-backports/debian stable all Regards, Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)