Received: from neptun.sns-felb.debis.de (neptun.sns-felb.debis.de [53.122.101.2]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f088uuB85125 for ; Mon, 8 Jan 2001 03:56:56 -0500 (EST) (envelope-from jhartwig@debis.com) Received: by neptun.sns-felb.debis.de; id KAA12671; Mon, 8 Jan 2001 10:01:32 +0100 Received: from unknown(53.113.82.10) by neptun.sns-felb.debis.de via smap (V5.0) id xma012654; Mon, 8 Jan 01 10:01:24 +0100 Received: from mail1.b3.dsh.de (localhost [127.0.0.1]) by dshmail1.dsh.de (8.9.1/8.8.7) with ESMTP id JAA28230; Mon, 8 Jan 2001 09:59:37 +0100 (MET) Received: from debis.com ([138.201.143.121]) by mail1.b3.dsh.de (8.10.0/8.10.0) with ESMTP id f088uN500492; Mon, 8 Jan 2001 09:56:24 +0100 Message-ID: <3A5980BD.9302B849@debis.com> Date: Mon, 08 Jan 2001 09:56:29 +0100 From: Jens Hartwig Organization: debis Systemhaus GEI X-Mailer: Mozilla 4.7 [de] (WinNT; I) X-Accept-Language: de MIME-Version: 1.0 To: guard , pgsql-sql@postgresql.org Subject: Re: create default References: <9397o9$qlf$1@news.tht.net> <3A5970F6.95DA47AD@debis.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/76 X-Sequence-Number: 737 Sorry, there is a "bug" in my statement (according to debug issues), the correct statement should be: CREATE FUNCTION test() RETURNS opaque AS 'BEGIN IF new.cno1 IS NULL THEN IF new.cno2 IS NULL THEN new.cno1 := trim(new.cno3); ELSIF new.cno3 IS NULL THEN new.cno1 := trim(new.cno2); ELSE new.cno1 := trim(new.cno2)||trim(new.cno3); END IF; END IF; RETURN new; END;' LANGUAGE 'plpgsql'; BTW, for getting correct results, you should define cno1 as long as the maximum length of cno2 and cno3 together (20). Regards, Jens Jens Hartwig schrieb: > > > hi,how to set default filed+field > > > > create table "table1"( > > "cno1" char(10) default NEW.cno2+NEW.cno3, > > "cno2" char(10) , > > "cno3" char(10) > > ); > > > > I try pl/pgsql and pl/tcl NOT RUN, > > > > thanks > > Try the following: > > CREATE FUNCTION test() RETURNS opaque AS > 'BEGIN > IF new.cno1 IS NULL THEN > IF new.cno2 IS NULL THEN > new.cno1 := substr(new.cno3,1,3); > ELSIF new.cno3 IS NULL THEN > new.cno1 := substr(new.cno2,1,3); > ELSE > new.cno1 := substr(new.cno2,1,3)||substr(new.cno3,1,3); > END IF; > END IF; > RETURN new; > END;' > LANGUAGE 'plpgsql'; > > CREATE TRIGGER test_trg > BEFORE INSERT OR UPDATE ON table1 > FOR EACH ROW > EXECUTE PROCEDURE test(); > > INSERT INTO table1 (cno2, cno3) values ('abc', 'def'); > > SELECT * FROM table1; > > Best regards, Jens Hartwig ============================================= Jens Hartwig --------------------------------------------- debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil : +49 (0)170 167-2648 E-Mail : jhartwig@debis.com =============================================