pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate default
3+ messages / 2 participants
[nested] [flat]
* create default
@ 2001-01-07 08:10 guard <guard@ficnet.net>
2001-01-08 07:49 ` Re: create default Jens Hartwig <jhartwig@debis.com>
0 siblings, 1 reply; 3+ messages in thread
From: guard @ 2001-01-07 08:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: create default
2001-01-07 08:10 create default guard <guard@ficnet.net>
@ 2001-01-08 07:49 ` Jens Hartwig <jhartwig@debis.com>
2001-01-08 08:56 ` Re: create default Jens Hartwig <jhartwig@debis.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jens Hartwig @ 2001-01-08 07:49 UTC (permalink / raw)
To: guard <guard@ficnet.net>; +Cc: pgsql-sql
> 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
=============================================
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: create default
2001-01-07 08:10 create default guard <guard@ficnet.net>
2001-01-08 07:49 ` Re: create default Jens Hartwig <jhartwig@debis.com>
@ 2001-01-08 08:56 ` Jens Hartwig <jhartwig@debis.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jens Hartwig @ 2001-01-08 08:56 UTC (permalink / raw)
To: guard <guard@ficnet.net>; pgsql-sql
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
=============================================
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-01-08 08:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-07 08:10 create default guard <guard@ficnet.net>
2001-01-08 07:49 ` Jens Hartwig <jhartwig@debis.com>
2001-01-08 08:56 ` Jens Hartwig <jhartwig@debis.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox