Re: pl/pgsql string combining

2009/12/16 Bino Oetomo <bino(at)indoakses-online(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>