pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
create 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