Postgres URI generation with sequences?

Question

So I'd like to create a column in a table in a postgres databse that auto increments a number and places it after some data. AKA

protocols table:

    name     |      uri      | 
_____________________________
someProtocol | /protocol/1
otherProt    | /protocol/2

Is there a way to create a sequence of some kind with other data? My knowledge of postgres column creation is fairly limited

score 0 · Accepted Answer · answered Jul 17 '15 at 20:47

create table protocols (
  id serial primary key, -- creates a sequence int
  name text not null,
  uri text 
);

create or replace function protocols_set_uri() returns trigger as $$
begin
    new.uri = '/' || new.name || '/' || new.id; 
    return new;
end $$
language plpgsql;

create trigger protocols_set_uri 
  before insert or update on protocols 
  for each row execute procedure protocols_set_uri();

Example:

insert into protocols (name) values ('someProtocol');
select * from protocols;

Result:

id name          uri
--------------------------------
1  someProtocol  /someProtocol/1

score 0 · Answer 2 · answered Jul 18 '15 at 18:56

You don't need to update any redundant fields; you can just compute them when needed, like in the following VIEW:

CREATE TABLE protocols (
  id SERIAL PRIMARY KEY -- creates a sequence int
  , name text not null
  );

INSERT INTO protocols(name) VALUES( 'DNS') ,( 'SMTP') ,( 'HTTP') ;

create VIEW fake_protocols AS
  SELECT id, name
  , '/protocol/' || id::text AS fake_uri
FROM protocols
        ;

SELECT * FROM fake_protocols;

Output:

CREATE TABLE
INSERT 0 3
CREATE VIEW
 id | name |  fake_uri   
----+------+-------------
  1 | DNS  | /protocol/1
  2 | SMTP | /protocol/2
  3 | HTTP | /protocol/3
(3 rows)

Postgres URI generation with sequences?

2 Answers2