2009/12/16 Bino Oetomo <bino@indoakses-online.com>:
> Dear All
>
> I have 2 table :
> 1. hotel_pbx_country
> 2. hotel_pbx_area
>
> Country is one2many to area
> Area have a field called "prefx"
>
> The "prefx" field is auto filled by country.code and area.code
> and for that purpose, i created trigger and function
> --------Trigger-------------
> CREATE TRIGGER prefx_xtrigger
> BEFORE INSERT OR UPDATE
> ON hotel_pbx_area
> FOR EACH ROW
> EXECUTE PROCEDURE prefx_xupdate();
> --------function----------------
> CREATE OR REPLACE FUNCTION prefx_xupdate()
> RETURNS trigger AS $$
> DECLARE ctrcode VARCHAR ;
> BEGIN
> select code into ctrcode from hotel_pbx_country where "id" =
> NEW.country_id ;
> NEW.prefx = ctrcode || NEW.code;
> RETURN NEW;
> END;
> $$ LANGUAGE 'plpgsql';
> ---------------EOF-------------------
>
> I make a bulk entry ,
> Not all area have area.code .. it's not mandatory
>
> In normal condition where both country.code and area.code is not null ....
> the trigger is work great.
> But the problem come when area.code is null, it cause area.prefx to be NULL
> also.
>
>
> Is it normal behavior ? or is it my fault ?
Hello
NULL and any is NULL. So you have to use "coalesce" function.
like
NEW.prefix = ctrcode || coalesce(NEW.code, '');
Regards
Pavel Stehule
>
> Kindly please give me your enlightment on how to fix it
>
> Sincerely
> -bino-
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
