From: | "Greg Sabino Mullane" <greg(at)turnstep(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Using nextval(seq) in more than one column |
Date: | 2007-10-10 16:01:47 |
Message-ID: | 73cff0cd220e50df5b5ba526fb4caba7@biglumber.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
- -- A few ideas:
CREATE DATABASE sean;
\c sean
BEGIN;
SET client_min_messages = 'error';
CREATE SEQUENCE asdf1_seq;
CREATE TABLE asdf1 (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf1_seq'),
foo TEXT
);
CREATE VIEW asdf AS SELECT *, 'ASDF-'||id AS asdf FROM asdf1;
INSERT INTO asdf1(foo) VALUES ('a view');
SELECT * FROM asdf;
CREATE SEQUENCE asdf2_seq;
CREATE TABLE asdf2 (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf2_seq'),
asdf TEXT,
foo TEXT
);
CREATE FUNCTION update_asdf()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $_$ BEGIN NEW.asdf = 'ASDF-'||NEW.id; RETURN NEW; END $_$;
CREATE TRIGGER asdf_trigger BEFORE INSERT OR UPDATE ON asdf2
FOR EACH ROW EXECUTE PROCEDURE update_asdf();
INSERT INTO asdf2(foo) VALUES ('a trigger');
SELECT * FROM asdf2;
CREATE SEQUENCE asdf3_seq;
CREATE TABLE asdf3 (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf3_seq'),
asdf TEXT NOT NULL DEFAULT 'ASDF-' || currval('asdf3_seq'),
foo TEXT
);
INSERT INTO asdf3(foo) VALUES ('currval hack');
SELECT * FROM asdf3;
CREATE SEQUENCE asdf4_seq;
CREATE TABLE asdf4 (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf4_seq'),
asdf TEXT,
foo TEXT
);
CREATE RULE asdf_rule AS ON INSERT TO asdf4
DO ALSO UPDATE asdf4 SET asdf='ASDF-'||id
WHERE id=currval('asdf4_seq');
INSERT INTO asdf4(foo) VALUES ('a rule');
SELECT * FROM asdf4;
ROLLBACK;
- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200710101159
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFHDPbivJuQZxSWSsgRA//fAJ9N91CgJJuMJJcxvyIbgiXYHPfCnQCg5Ahi
OdQTM77u4ZZuWDD6I3TqyIE=
=yCf4
-----END PGP SIGNATURE-----
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2007-10-10 16:06:54 | Re: Using nextval(seq) in more than one column |
Previous Message | Nis Jørgensen | 2007-10-10 15:28:07 | Re: Using nextval(seq) in more than one column |