Comment générer des sous-séquences dans PostgreSQL à l'aide de séquences et de déclencheurs ?

Cet article explique comment générer des sous-séquences dans PostgreSQL à l'aide de séquences et de déclencheurs. Le scénario implique une table avec une clé primaire composite (id, seq), nécessitant une solution personnalisée pour créer une séquence unique pour chaque id.

La solution consiste à créer deux tables et à implémenter des déclencheurs :

Structure du tableau :

• choses : Cette table stocke les entités principales, avec id comme clé primaire.
• trucs : Cette table stocke les sous-séquences, contenant id (référence de clé étrangère things), seq (le numéro de sous-séquence) et notes. La clé primaire est la combinaison de id et seq.

Déclencheurs et fonctions :

1. things déclencheur de table (make_thing_seq) : Ce déclencheur est exécuté après chaque insertion dans la things table. Il crée une nouvelle séquence nommée thing_seq_{id} pour chaque nouveau id.

2. stuff déclencheur de table (fill_in_stuff_seq) : Ce déclencheur est exécuté avant chaque insertion dans la stuff table. Il récupère la valeur suivante de la séquence appropriée (thing_seq_{id}) en fonction du id inséré et l'assigne à la colonne seq.

Exemple de mise en œuvre :

Le code suivant illustre la création de table et l'implémentation du déclencheur :

<code class="language-sql">-- Create the tables
CREATE TABLE things (
    id serial primary key,
    name text
);

CREATE TABLE stuff (
    id integer references things,
    seq integer NOT NULL,
    notes text,
    primary key (id, seq)
);

-- Trigger in things table to create new sequences
CREATE OR REPLACE FUNCTION make_thing_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  EXECUTE format('CREATE SEQUENCE IF NOT EXISTS thing_seq_%s', NEW.id); -- Added IF NOT EXISTS
  RETURN NEW;
end;
$$;

CREATE TRIGGER make_thing_seq AFTER INSERT ON things FOR EACH ROW EXECUTE PROCEDURE make_thing_seq();

-- Trigger in stuff table to use correct sequence
CREATE OR REPLACE FUNCTION fill_in_stuff_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  NEW.seq := nextval(format('thing_seq_%s', NEW.id));
  RETURN NEW;
end;
$$;

CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW EXECUTE PROCEDURE fill_in_stuff_seq();</code>

Cette configuration garantit que l'insertion de données dans things et stuff génère automatiquement des sous-séquences uniques pour chaque id, en maintenant l'intégrité de la clé primaire composite. La clause IF NOT EXISTS a été ajoutée à la fonction make_thing_seq pour éviter les erreurs si une séquence pour un identifiant donné existe déjà.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!