Geek Answers Handbook

Postgresql company identifier sequence

I have a database with companies and their products, I want for each company to have a separate sequence of product identifiers.

I know that postgresql cannot do this, the only way is to have a separate sequence for each company, but this is cumbersome.

I was thinking of deciding to have a separate table for storing sequences

CREATE TABLE "sequence"
(
  "table" character varying(25),
  company_id integer DEFAULT 0,
  "value" integer
)

the “table” will contain the table name for the sequence, for example, products, categories, etc.

and the value will contain the actual sequence data to be used for product_id for inserts

I will use UPDATE ... RETURNING value; to get the product id

I was wondering if this solution is effective?

, , , , , .

?

, , , .

+4
2

:

CREATE TABLE companies (
    id          SERIAL PRIMARY KEY,
    name        TEXT,
    product_id  INT DEFAULT 0
);

CREATE TABLE products (
    company     INT REFERENCES companies(id),
    product_id  INT,
    PRIMARY KEY (company, product_id),

    name        TEXT
);

INSERT INTO companies (id, name) VALUES (1, 'Acme Corporation');
INSERT INTO companies (id, name) VALUES (2, 'Umbrella Corporation');

UPDATE... RETURNING, :

> INSERT INTO products VALUES (1, (UPDATE companies SET product_id = product_id+1 WHERE id=$1 RETURNING product_id), 'Anvil');
ERROR:  syntax error at or near "companies"
LINE 1: INSERT INTO products VALUES (1, (UPDATE companies SET produc...
                                            ^

! , ( PostgreSQL 9.1devel) UPDATE... RETURNING .

- ! , /:

CREATE FUNCTION next_product_id(company INT) RETURNS INT
AS $$
    UPDATE companies SET product_id = product_id+1 WHERE id=$1 RETURNING product_id
$$ LANGUAGE 'sql';

:

INSERT INTO products VALUES (1, next_product_id(1), 'Anvil');
INSERT INTO products VALUES (1, next_product_id(1), 'Dynamite');
INSERT INTO products VALUES (2, next_product_id(2), 'Umbrella');
INSERT INTO products VALUES (1, next_product_id(1), 'Explosive tennis balls');

, next_product_id(company INT).

+3

, , . , product_id.

SELECT FOR UPDATE . , .

0

More articles:


All Articles