Re: Converting empty input strings to Nulls

Hello

2008/5/31 Ken Winter <ken@sunward.org>:
> Applications accessing my PostgreSQL 8.0 database like to submit no-value
> date column values as empty strings rather than as Nulls.  This, of course,
> causes this PG error:
>
> SQL State: 22007
> ERROR: invalid input syntax for type date: ""
>
> I'm looking for a way to trap this bad input at the database level, quietly
> convert the input empty strings to Null, and store the Null in the date
> column.  I tried a BEFORE INSERT OR UPDATE trigger evoking this function ...
>
> CREATE OR REPLACE FUNCTION "public"."empty_string_to_null"()
> RETURNS trigger AS
> $BODY$
> BEGIN
>    IF CAST(NEW.birth_date AS text) = '' THEN
>        NEW.birth_date = Null;
>    END IF;
>    RETURN NEW;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
> ... but an empty string still evokes the error even before this function is
> triggered.
>
> Is there a way to convert empty strings to Nulls before the error is evoked?
>
no - it's not possible. And some "magic" fix in triggers is bad style.

you can write own custom type (not in plpgsql) that allows this
behave. You can copy and modify postgre's DateADT implementation.
pgsql/src/backend/utils/adt/date.c

Datum
date_in(PG_FUNCTION_ARGS)
{
        char       *str = PG_GETARG_CSTRING(0);
        DateADT         date;
        fsec_t          fsec;
        struct pg_tm tt,
                           *tm = &tt;
        int                     tzp;
        int                     dtype;
        int                     nf;
        int                     dterr;
        char       *field[MAXDATEFIELDS];
        int                     ftype[MAXDATEFIELDS];
        char            workbuf[MAXDATELEN + 1];

// your hack
if (strlen(str) == 0)
   PG_RETURN_NULL();

        dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
                                                  field, ftype,
MAXDATEFIELDS, &n
        if (dterr == 0)
                dterr = DecodeDateTime(field, ftype, nf, &dtype, tm,
&fsec, &tzp)
        if (dterr != 0)
                DateTimeParseError(dterr, str, "date");

        switch (dtype)




more: http://www.postgresql.org/docs/8.3/interactive/xtypes.html

I am not sure if in function can return NULL.You should to test it

regards
Pavel Stehule



> ~ TIA
> ~ Ken
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>