pl/pgsql string combining

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

Dear Mr. Stehule

Thankyou for your super prompt (came to my mailbox less then 2 minutes
since my post) enlightment.

I'll try it

Sincerely
-bino-

Pavel Stehule wrote: