Forums PostgreSQL.fr

ramirez22

Membre

[PG10] Requête préparée, INSERT nombre variable de ligne par tableau

Bonjour,

Malgré mon expérience grandissante dans la compréhension du SQL et de PostgreSQL (bon OK, j'en rajoute un peu ), je n'arrive pas à trouver un début de piste de réflexion.

Je voudrais faire une requête préparée et lui passer quelques valeurs text et un tableau (array) de x bigint.
Ma fonction actuelle (sans le tableau) a cette tête :

PREPARE ajout_nouveau_document_inconnu(text,text,text,text,bigint) AS 
WITH nouveau_doc AS (
    INSERT INTO documents (
    code, 
    titre, 
    commentaires, 
    creation_par,
    date_creation) 
    VALUES ($1, $2, $3, $4, CURRENT_DATE)
    RETURNING id_document)
INSERT INTO documents_types (id_document, id_type) 
    SELECT nouveau_doc.id_document, $5 
    FROM nouveau_doc 
RETURNING id_document;

La table documents_types est une table de liaison et il peut y avoir plusieurs types pour un document.
Or avec cette requête, je ne peut que saisir le premier type ($5).
Je suis ensuite obligé de faire un INSERT INTO documents_types pour chaque types et ça envoi un nombre conséquent de requêtes.

J'aurais voulu, à la place du dernier bigint de la fonction préparée, transmettre un tableau de bigint avec les ID de chaque types de doc, et que la fonction gère d'elle-même les INSERT INTO en fonction:
- du id_document récupéré lors de la création du-dit document dans la table documents
- du ou des bigint contenu(s) dans le tableau transmis

Mais perso, je sèche...

Un peu d'aide serait la bienvenue (même si c'est pour me dire "pô possible !")

Merci à vous, bonne fin de journée

gleu

Administrateur

Re : [PG10] Requête préparée, INSERT nombre variable de ligne par tableau

J'ai déjà du mal à comprendre la première requête, alors difficile de conseiller la construction d'une deuxième requête de même type

Ceci étant dit, je crois que j'ai compris ce que vous vouliez faire mais il ne me semble pas que ce soit possible en SQL pur. C'est certainement le cas où il vaut mieux passer par une procédure stockée.

---

Guillaume.

ramirez22

Membre

Re : [PG10] Requête préparée, INSERT nombre variable de ligne par tableau

Je suis en train de me demander si je ne cherche pas la complexité là où il n'y en a pas...

En gros, j'ai créé une table pour test avec 2 colonne bigint, puis j'ai créé cette fonction:

CREATE OR REPLACE FUNCTION insertion(
	idcol bigint,
	tableau bigint[])
    RETURNS void
    LANGUAGE 'plpgsql'
    AS $$
        BEGIN
	FOR i IN 1..array_upper(tableau,1)
	    LOOP
		INSERT INTO test (colonne1, colonne2) VALUES (idcol, tableau[i]);
	    END LOOP;
        END;
$$;

Puis je l'ai lancée

SELECT insertion (1, '{1,2,4,5}');

Et j'obtiens bien ma table

colonne 1  |  colonne 2
     1     |     1
     1     |     2
     1     |     4
     1     |     5

Mais je me demande quel est le gain réel et si ça vaut vraiment le coup de se creuser la tête pour adapter cette fonction à mon besoin ci-dessus.

En gros, avec cette fonction, j'envoie une fois pour toute les infos du document avec un array contenant les ID du type de documents, et la fonction créé autant de ligne dans la table documents_types que nécessaire.
Mais quel est réellement le gain par rapport à : créer mon document et récupérer son ID, puis envoyer autant de requête que nécessaire pour créer les lignes de la table documents_types ? La charge réseau ?

De plus, l'avantage des requêtes préparées, c'est qu'elles étaient supprimées à la fermeture de la session utilisateur. Ce qui n'est pas le cas des fonctions...
Je ne sais pas si cela aura des conséquences à terme ...

Merci de vos éclaircissements.

Dernière modification par ramirez22 (09/03/2019 09:09:35)

dverite

Membre

Re : [PG10] Requête préparée, INSERT nombre variable de ligne par tableau

Il n'y a pas besoin de boucle FOR, il faut utiliser plutôt unnest() pour générer une ligne par élément du tableau.
Concrètement, par rapport à la requête de départ, si $5 est de type bigint[] (donc un tableau), le second
INSERT devrait ressembler ça:

INSERT INTO documents_types (id_document, id_type) 
    SELECT nouveau_doc.id_document, liste.type_doc
    FROM nouveau_doc CROSS JOIN unnest($5) AS liste(type_doc)

---

@DanielVerite
http://blog-postgresql.verite.pro/

ramirez22

Membre

Re : [PG10] Requête préparée, INSERT nombre variable de ligne par tableau

Je ne suis pas sur que l'on se comprenne bien

Aussi, histoire d'être plus compréhensible (j'avoue que parfois ...) un petit exemple ne sera pas de trop.
Je viens de faire une base de test avec 2 tables.

CREATE TABLE public.test_document (
    id_doc bigint NOT NULL DEFAULT nextval('test_id_doc_seq'::regclass),
    code text COLLATE pg_catalog."default" NOT NULL,
    titre text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "Test_document_pkey" PRIMARY KEY (id_doc))

CREATE TABLE public.test_liaison(
    id_doc bigint,
    id_type bigint)

Et je voudrais réaliser cette action :

DO $$
	DECLARE
		tableau bigint[] := '{2,9,5}';
	BEGIN
		INSERT INTO test_document (code,titre) VALUES ('A', '') RETURNING id_doc AS id_nouveau_doc;
		FOR i IN 1..array_upper(tableau,1) LOOP
			INSERT INTO test_liaison (id_doc, id_type) VALUES (id_nouveau_doc, tableau[i]);
		END LOOP;
	END;
$$ LANGUAGE plpgsql;

Mais cela ne fonctionne pas :

ERROR:  ERREUR:  la requête n'a pas de destination pour les données résultantes
CONTEXT:  fonction PL/pgsql inline_code_block, ligne 5 à instruction SQL
SQL state: 42601

En gros, je voudrais que ce groupe de commande :
- crée un enregistrement sur la première table avec les valeurs passées "VALUES ('A', '')" que j'adapterai depuis mon code appelant
- créé un nombre d'enregistrement dans la deuxième table correspondant au nombre de valeurs dans la variable de type tableau 'tableau' et comme valeurs, l'ID créé précédemment et la valeur correspondante de la variable 'tableau' (suis-je clair ?)

J'ai tenté d'ajouter

id_nouveau_doc bigint;

dans les déclarations, mais aucun changement.

Si je supprime le RETURNING, plus d'erreur (j'aurais tendance à penser que c'est normal puisque DO est censé renvoyer VOID), mais forcément, la table test_liaison n'a pas la valeur d'ID_doc souhaitée. Du coup, je ne vois pas comment faire.

J'avoue que cela ne vaut pas vraiment le coup de se creuser la tête pour si peu.
Je vais envoyer la première requête :

INSERT INTO test_document (code,titre) VALUES ('A', '') RETURNING id_doc AS id_nouveau_doc;

Puis récupérer dans mon code la valeur de l'ID renvoyée et lancer cette fois-ci :

DO $$
	DECLARE
		tableau bigint[] := '{2,9,5}';
	BEGIN
		FOR i IN 1..array_upper(tableau,1) LOOP
			INSERT INTO test_liaison (id_doc, id_type) VALUES (id_nouveau_doc, tableau[i]);
		END LOOP;
	END;
$$ LANGUAGE plpgsql;

Avec comme paramètres le tableau et l'id_nouveau_doc.

Certes, cela fait 2 requêtes, mais cela ne va pas surcharger le réseau

C'est juste que j'aurais voulu savoir et comprendre.

Merci en tout cas pour l'intervention. Et si tu vois des "solutions" à ma problématique, je suis preneur à titre de curiosité malgré tout.

Cordialement,
Ramirez22

rjuju

Administrateur

Re : [PG10] Requête préparée, INSERT nombre variable de ligne par tableau

Il manque une clause INTO dans la première requête pour récupérer la valeur de retour, sinon effectivement votre requête renvoie une valeur que rien n'intercepte.  Vous pouvez faire les deux opérations en une seule requête, en reprenant votre exemple :

WITH src(id_doc) AS (
    INSERT INTO test_document (code,titre) VALUES ('A', '') RETURNING id_doc
)
INSERT INTO test_liaison (id_doc, id_type)
    SELECT src.id_doc, arr.nb
    FROM src
    CROSS JOIN unnest('{2,9,5}'::bigint[]) AS arr(nb);

---

Julien.
https://rjuju.github.io/

ramirez22

Membre

Re : [PG10] Requête préparée, INSERT nombre variable de ligne par tableau

Bonjour,

C'est à ce moment que je réalise l'immensité de l'abime de ma méconnaissance de PostgreSQL

Merci beaucoup. Je prends 2 aspirines et j'essaye de comprendre comme ça marche
Pour l'instant, j'ai décomposé ma requête en 2 (de plus, cela me permet de récupérer l'ID pour un autre traitement ...), mais pour ma culture, je regarderai quand même votre syntaxe.

Merci encore.