Skip site navigation (1) Skip section navigation (2)

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

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Greg Sabino Mullane <greg(at)turnstep(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Using nextval(seq) in more than one column
Date: 2007-10-10 16:06:54
Message-ID: 470CF89E.1080201@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Brian Oki (boki)Date: 2007-10-10 23:12:32
Subject: support for distributed transactions
Previous:From: Greg Sabino MullaneDate: 2007-10-10 16:01:47
Subject: Re: Using nextval(seq) in more than one column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group