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

Greg Sabino Mullane wrote:
>
> -- 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;
>

Thanks, Greg.  These are great and answer my question in much more
detail than I had hoped.

Sean