Comment puis-je créer des sous-séquences personnalisées dans PostgreSQL basées sur les valeurs de clés étrangères ?

Génération de sous-séquences personnalisées PostgreSQL : méthode de création de sous-séquences basée sur les valeurs de clés étrangères

Cet article explique comment créer des sous-séquences personnalisées basées sur des valeurs de clé étrangère dans PostgreSQL. Les séquences de PostgreSQL sont généralement utilisées pour générer des identifiants uniques pour les lignes d'une table, mais dans certains cas, vous devrez peut-être générer une séquence pour qu'elle dépende de la valeur d'une autre colonne, créant ainsi une sous-séquence.

L'image suivante montre le cœur du problème :

Génération de séquence basée sur une corrélation personnalisée

Considérez la structure de tableau suivante :

列名	数据类型	说明
id	integer	外键，指向另一张表
seq	integer	每个id的序列号
data	text	无关信息

L'objectif est de construire un système d'identifiant où chaque combinaison unique d'id et de seq correspond à une ligne du tableau.

PostgreSQL y parvient grâce à une combinaison intelligente de conception de table et de déclencheurs. Nous pouvons créer deux tables : things et stuff, où things représente les identifiants uniques et stuff stocke les données réelles associées à chaque identifiant.

Créer des tables et des déclencheurs

thingsLa table peut créer un identifiant comme clé primaire :

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

Pour la table stuff, nous définissons une combinaison de id et seq comme clé primaire :

<code class="language-sql">CREATE TABLE stuff (
    id integer references things,
    seq integer NOT NULL,
    notes text,
    primary key (id, seq)
);</code>

Ensuite, nous implémentons une fonction de déclenchement nommée things sur la table make_thing_seq. Ce déclencheur est appelé après chaque insertion de ligne et crée une nouvelle séquence avec l'identifiant inséré.

<code class="language-sql">CREATE FUNCTION make_thing_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  execute format('create sequence thing_seq_%s', NEW.id);
  return NEW;
end
$$;</code>

Ensuite, on crée un trigger nommé things sur la table make_thing_seq :

<code class="language-sql">CREATE TRIGGER make_thing_seq AFTER INSERT ON things FOR EACH ROW EXECUTE PROCEDURE make_thing_seq();</code>

Cela garantit que chaque fois qu'une nouvelle ligne est insérée dans la table things, une séquence correspondante est créée.

Enfin, nous créons une fonction de déclenchement nommée fill_in_stuff_seq qui est appelée avant chaque insertion de ligne et remplit la colonne seq avec la valeur suivante appropriée de la séquence.

<code class="language-sql">CREATE FUNCTION fill_in_stuff_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  NEW.seq := nextval('thing_seq_' || NEW.id);
  RETURN NEW;
end
$$;</code>

Créez un déclencheur nommé stuff sur la table fill_in_stuff_seq :

<code class="language-sql">CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW EXECUTE PROCEDURE fill_in_stuff_seq();</code>

Cela garantit que lorsqu'une ligne de la table stuff est insérée, la valeur seq est automatiquement définie en fonction de la valeur d'identification associée.

Résultats

Avec ces déclencheurs, la génération de séquence souhaitée peut être obtenue. L'insertion de données dans la table things créera une nouvelle séquence, et l'insertion de données dans la table stuff attribuera automatiquement le numéro de séquence correct en fonction de la référence d'identification.

En combinant ces techniques, nous pouvons créer des structures de données complexes et flexibles qui répondent à des exigences de tri spécifiques.

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!