Re: Using nextval(seq) in more than one column

-----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-----