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° ;-)