Date: Tue, 28 Dec 2004 15:50:28 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Robert Wimmer <seppwimmer@hotmail.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: type cast/validation functions
Message-ID: <20041228225028.GA68003@winnie.fuhr.org>
References: <BAY10-F46B987C29ECABD921F27E0D09A0@phx.gbl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <BAY10-F46B987C29ECABD921F27E0D09A0@phx.gbl>

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/