Thread: pl/pgsql string combining
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 ? Kindly please give me your enlightment on how to fix it Sincerely -bino-
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 >
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: > Hello > > NULL and any is NULL. So you have to use "coalesce" function. > > like > > NEW.prefix = ctrcode || coalesce(NEW.code, ''); > > Regards > Pavel Stehule > >