Обсуждение: Help with "empty()"

Hi!

In doing table validation, I have often used the
foxpro function "empty()" which returns .t. if the
enclosed entity is empty. i.e., if boolean .f. then
empty = .t.. If number 0, then empty = .t., and if a
blank string, then empty = .t.

I didn't see anyother way to mimic this behavior then
what I wrote below, is there a better way????

Please help, suggestions are appreciated!
Cheers,
ajw

    CREATE FUNCTION zzipcode_validate_fields() RETURNS
opaque AS '
        BEGIN
            IF NEW.izipcode ISNULL THEN
                RAISE EXCEPTION ''zipcode is null.'' ;
            ELSE
                IF char_length( trim(both from NEW.izipcode)) < 9
THEN
                    RAISE EXCEPTION ''zipcode less-then 9-digits.'' ;
                END IF;
            END IF;

            IF NEW.cstate ISNULL THEN
                RAISE EXCEPTION ''state is null.'' ;
            ELSE
                IF char_length( trim(both from NEW.cstate))=0 THEN
                    RAISE EXCEPTION ''state is empty string.'';
                END IF;
            END IF;

            IF NEW.ccity ISNULL THEN
                RAISE EXCEPTION ''city is null.'';
            ELSE
                IF char_length( trim(both from NEW.ccity)) = 0
THEN
                    RAISE EXCEPTION ''city is empty string.'' ;
                END IF;
            END IF;

            NEW.cstate := upper(NEW.cstate);
            NEW.ccity := upper(NEW.ccity);
            RETURN NEW;
            END;
    'LANGUAGE 'plpgsql';

    CREATE TRIGGER zzipcode_validate BEFORE INSERT OR
UPDATE ON zzipcode
        FOR EACH ROW EXECUTE PROCEDURE
zzipcode_validate_fields();


__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com

> In doing table validation, I have often used the
> foxpro function "empty()" which returns .t. if the
> enclosed entity is empty. i.e., if boolean .f. then
> empty = .t.. If number 0, then empty = .t., and if a
> blank string, then empty = .t.
>
> I didn't see anyother way to mimic this behavior then
> what I wrote below, is there a better way????

I'd have suggested writing empty functions for various
types, empty(boolean), empty(int4), empty(float8),
empty(text) and using those.  You could probably even
just do them as sql functions.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Stephan Szabo
> Sent: Friday, May 24, 2002 12:15 AM
> To: Alan Wayne
> Cc: Jean-Michel POURE; pgsql-general
> Subject: Re: [GENERAL] Help with "empty()"
>
> I'd have suggested writing empty functions for various
> types, empty(boolean), empty(int4), empty(float8),
> empty(text) and using those.  You could probably even
> just do them as sql functions.

Yep. Though, since they're compiled, plpgsql functions are faster than SQL
functions. Don't forget to mark them as isCachable, too.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant