https://www.postgresql.org/list/pgsql-jdbc/

Le site indique que "The current version of the driver should be compatible with PostgreSQL 8.2 and higher". Du coup, j'utiliserais plutôt la version la plus récente du driver (donc la 42.2.18). De plus, cette version est sortie après la version 13 de PostgreSQL, donc elle a bien plus de chance d'être compatible qu'une version qui date de 2018 (votre 42.2.5).

Déjà, merci beaucoup pour avoir fait l'effort de ce long texte et de ces exemples. Mais il y a un gros problème. Dès le départ, le premier script ne pourra pas s'exécuter correctement :

CREATE TABLE ouvrage_v3.communes (insee text, geom);  -- ce CREATE ne peut pas fonctionner, il manque à priori le nom de la colonne de type geom
INSERT INTO ouvrage_v3.communes (insee) VALUES ('83044');
INSERT INTO ouvrage_v3.communes (insee) VALUES ('83013');

CREATE TABLE ouvrage_v3.hydro_zone (bv text, geom); -- pareil pour ce CREATE
INSERT INTO ouvrage_v3.hydro_zone (insee) VALUES ('X230');
INSERT INTO ouvrage_v3.hydro_zones (insee) VALUES ('X240'); -- la table s'appelle hydrozone, sans s

Ces deux petites fautes ne sont pas graves, mais je ne vais pas perdre du temps à vérifier tout le reste du code. Merci de vérifier votre code et de corriger tout ça avant qu'on puisse vous aider réellement. Merci.

Ça manque d'un exemple complet montrant le problème parce que, pour moi, ça marche :

INSERT INTO ouvrage_v3.ouv_protec_epci_ca_dracenoise VALUES ('X241', '83044', 'prout');
INSERT INTO ouvrage_v3.ouv_protec_epci_ca_dracenoise VALUES ('X230', '83044', 'prout2');
TABLE ouvrage_v3.ouv_protec_epci_ca_dracenoise ;
┌──────┬───────┬──────────────┐
│  bv  │ insee │  id_ouvrage  │
├──────┼───────┼──────────────┤
│ X230 │ 83044 │ X230_83044_1 │
│ X230 │ 83044 │ X230_83044_2 │
│ X240 │ 83013 │ X240_83013_1 │
│ X241 │ 83044 │ X241_83044_1 │
│ X241 │ 83044 │ X241_83044_2 │
│ X241 │ 83044 │ X241_83044_3 │
│ X241 │ 83044 │ X241_83044_4 │
│ X230 │ 83044 │ X230_83044_3 │
└──────┴───────┴──────────────┘
(8 rows)

Toutes les requêtes permettant de rejouer votre exemple de trigger, de manière à tester nous même et corriger, en s'assurant que la correction convienne. Voilà un exemple :

CREATE SCHEMA ouvrage_v3;
CREATE TABLE ouvrage_v3.ouv_protec_epci_ca_dracenoise (bv text, insee text, id_ouvrage text);
CREATE FUNCTION ouvrage_v3.generate_id_ouvrage() RETURNS TRIGGER AS
$BODY$
BEGIN
NEW.id_ouvrage = NEW.bv||'_'||NEW.insee||'_'||(SELECT(count(*)+1)::text FROM ouvrage_v3.ouv_protec_epci_ca_dracenoise WHERE bv=NEW.bv and insee=NEW.insee);
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER tr1
  BEFORE INSERT OR UPDATE ON ouvrage_v3.ouv_protec_epci_ca_dracenoise
  FOR EACH ROW EXECUTE FUNCTION ouvrage_v3.generate_id_ouvrage();
INSERT INTO ouvrage_v3.ouv_protec_epci_ca_dracenoise (bv, insee) VALUES ('X230', '83044');
INSERT INTO ouvrage_v3.ouv_protec_epci_ca_dracenoise (bv, insee) VALUES ('X230', '83044');
INSERT INTO ouvrage_v3.ouv_protec_epci_ca_dracenoise (bv, insee) VALUES ('X240', '83013');
INSERT INTO ouvrage_v3.ouv_protec_epci_ca_dracenoise (bv, insee) VALUES ('X241', '83044');
INSERT INTO ouvrage_v3.ouv_protec_epci_ca_dracenoise (bv, insee) VALUES ('X241', '83044');
INSERT INTO ouvrage_v3.ouv_protec_epci_ca_dracenoise (bv, insee) VALUES ('X241', '83044');
SELECT * FROM ouvrage_v3.ouv_protec_epci_ca_dracenoise;

Ce qui donne comme résultat :

CREATE SCHEMA
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
┌──────┬───────┬──────────────┐
│  bv  │ insee │  id_ouvrage  │
├──────┼───────┼──────────────┤
│ X230 │ 83044 │ X230_83044_1 │
│ X230 │ 83044 │ X230_83044_2 │
│ X240 │ 83013 │ X240_83013_1 │
│ X241 │ 83044 │ X241_83044_1 │
│ X241 │ 83044 │ X241_83044_2 │
│ X241 │ 83044 │ X241_83044_3 │
└──────┴───────┴──────────────┘
(6 rows)

Ce qui m'a permis de remarquer que je m'étais trompé dans le code de la fonction. Et qu'après correction, que ma nouvelle proposition fonctionnait.

C'est pour stocket temporairement la valeur du résultat du count+1, pour ensuite le concaténer à l'identifiant. J'aurais aussi pu dire :

SELECT bv||'_'||insee||'_'||(count(*)+1)::text FROM ouv_protec_epci_ca_dracenoise WHERE bv=NEW.bv et insee=NEW.insee;

la valeur renvoyée par le SELECT étant maintenant l'identifiant complet.

C'est très clair et c'est donc bien ce que j'avais compris. Je maintiens donc ma solution si on parle bien d'une fonction trigger qui s'exécutera à chaque insertion.

À l'insertion, le code que je propose va compter le nombre d'éléments contenant déjà le même bv et code insee, ce qui permettra de trouver la séquence suivante pour la numérotation de l'ouvrage. Dans l'exemple donné, pour un bv X230 et un code insee 83044, le SELECT reverra 4 (le nombre d'éléments + 1), ce qui permettra par concaténation d'obtenir X230-83044-4 pour la ligne en cours d'insertion.

Apparemment, je n'ai pas compris ce que vous cherchiez à faire, donc il serait mieux que vous repreniez votre explication, votre demande à la base pour qu'on vous aide au mieux.

En gros, là, vous demandez à la fonction de mettre à jour la colonne id_ouvrage pour toutes les lignes de la table ouv_protec_epci_ca_dracenoise. Or, si j'ai bien compris, vous voulez seulement mettre à jour cette colonne pour la ligne qui déclenche le trigger. Donc il faudra plutôt faire un "NEW.id_ouvrage := une_certaine_valeur". Et "une_certaine_valeur" sera le résultat d'un calcul provenant de la recherche du nombre de lignes partageant le même bassin versant et code insee que la ligne déclenchant le trigger. Ce qui ferait plutôt ce type de code :

SELECT count(*)+1 INTO valeur FROM ouv_protec_epci_ca_dracenoise WHERE bv=NEW.bv et insee=NEW.insee;
NEW.id_ouvrage = bv||'_'||insee||'_'||valeur;

Tout le code n'est pas là, et je peux m'être trompé sur le nom des colonnes. À corriger donc.

En cas de problème, merci de fournir un cas de test complet.

Encore une fois, ce n'est pas très clair. Déjà, vous ne pourrez pas tout restaurer en une seule commande. Vous aurez besoin de dix commandes pg_restore. De plus, ça ne sera pas forcément suffisant. Je suppose qu'il s'agit des mêmes tables, mais avec des données partiellement différentes. Certaines contraintes risquent de poser problème. Bref, à moins que chaque sauvegarde contient des objets nommés différemment entre les sauvegardes, cela va être un gros chantier que de les assembler.

L'extension pg_trgm peut vous aider dans ce cas. C'est au moins à tester.

Avec une requête SQL seule, ce n'est pas possible, mais avec une fonction, par exemple en PL/pgsql, c'est tout à fait possible. La documentation de ce langage se trouve sur https://docs.postgresql.fr/13/plpgsql.html