Forums PostgreSQL.fr

Sadewizz

Membre

Fonction permettant de créer de générer des tables

Bonjour,

je suis novice en ce qui concerne postgres/gis, et dans le cadre d'un stage je suis amené à créer une base donnée spatiale.
Ma question est la suivante: Une fonction plp/gsql ou autre peut elle générer automatiquement une table ?

Je m'explique. j'ai déjà une vue me permettant de lister toutes me tables de manière dynamique. Mais je voudrait qu'a l'insertion de chaque table, une fonction, ou plusieurs, me récupère le nom de la nouvelle table dans la liste en question,  et crée une autre nouvelle table de style st_convexhull à partir de cette nouvelle table en récupérant juste les informations souhaitées.
Cela est il possible ?

Merci pour vos réponses,
Sadewizz.

rjuju

Administrateur

Re : Fonction permettant de créer de générer des tables

Bonjour,

vous pouvez écrire un trigger qui se déclenchera sur insertion dans la table concernée, et pourra exécuter des requêtes dynamiques : http://docs.postgresql.fr/9.3/sql-createtrigger.html

---

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

Sadewizz

Membre

Re : Fonction permettant de créer de générer des tables

Bonjour,

Merci pour votre réponse!
J'avais déjà lu la doc que vous m'avez conseillé, cependant les fonction triggers, il me semble, n'effectue pas de "create table" si?
Plus simplement je voudrait créer une fonction me permettant un "create table T as select st_convexhull(the_geom)" (la syntaxe est à titre indicatif), dés l'insertion d'une nouvelle table.
Je ne sais pas si ce que je souhaite est possible, qu'en pensé vous?

Sadewizz

Dernière modification par Sadewizz (02/06/2014 09:41:38)

rjuju

Administrateur

Re : Fonction permettant de créer de générer des tables

Si votre trigger est écrit en pl/pgsql, vous pouvez exécuter une requête dynamique, à l'aide du mot clé "PERFORM", par exemple « PERFORM 'CREATE TABLE t AS SELECT ... '; », voir la documentation : http://docs.postgresql.fr/9.2/plpgsql-statements.html

---

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

Sadewizz

Membre

Re : Fonction permettant de créer de générer des tables

Merci pour votre réponse, et l'intérêt porté à mon post.
J'ai suivi vos conseils et j'ai commencé par réaliser une fonction que j’appellerais ensuite dans le trigger de la table en question.
Seulement pg_admin me retourne erreur que je ne comprend pas lorsque j'appel ma fonction avec un select * from mafonction().

Voici ma fonction:

 CREATE OR REPLACE FUNCTION france.emprise_table() RETURNS void AS $$
DECLARE
    liste RECORD;
    rec RECORD;
    curs refcursor;
BEGIN
    FOR liste in select nom_donnee from france.table_liste
    LOOP
    perform 'CREATE table france.'||quote_ident(liste.nom_donnee)||'_emp AS 
SELECT nom_donnee, st_convexhull( st_collect (the_geom_wgs84))
   FROM '||quote_ident(liste.nom_donnee)||' group by nom_donnee;';
    OPEN curs FOR SELECT nom_donnee FROM table_liste;   
        FETCH curs INTO rec;
    END LOOP;
    CLOSE curs;
END;
$$ LANGUAGE plpgsql;

Et le message d'erreur qui en découle :

ERREUR:  curseur « <unnamed portal 149> » déjà en cours d'utilisation
CONTEXT:  fonction PL/pgsql fct(), ligne 12 à OPEN
********** Erreur **********

ERREUR: curseur « <unnamed portal 149> » déjà en cours d'utilisation
État SQL :42P03
Contexte : fonction PL/pgsql fct(), ligne 12 à OPEN

D'où pourrait venir le problème d'après vous?
Enfin, tout d'abord ma fonction n'est peut-être pas exact...

Dernière modification par Sadewizz (02/06/2014 11:28:51)

gleu

Administrateur

Re : Fonction permettant de créer de générer des tables

Le nom de la fonction dans le message d'erreur ne correspond pas au nom de la fonction pour le code proposé.

---

Guillaume.

Sadewizz

Membre

Re : Fonction permettant de créer de générer des tables

Bonjour,

Oui effectivement, j'ai entre temps changé le nom de la fonction désolé. Mais l'erreur reste la même.
J'ai néanmoins réussi à résoudre mon problème.

CREATE OR REPLACE FUNCTION france.emprise_table()
  RETURNS void AS
$BODY$
DECLARE
    liste RECORD;
    rec RECORD;
    curs refcursor;
BEGIN
   FOR liste in select nom_donnee FROM france.table_liste
   LOOP
    EXECUTE 'CREATE or replace view france.'||quote_ident(liste.tablename)||'_emp AS 
    SELECT nom_donnee, st_convexhull( st_collect (the_geom_wgs84)) as the_geom_wgs84
    FROM '||quote_ident(liste.tablename)||' group by nom_donnee;';
    END LOOP; 
  OPEN curs FOR SELECT nom_donnee FROM table_liste;   
    LOOP
        FETCH curs INTO rec;
        exit when not found;
    END LOOP;
   CLOSE curs;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION france.emprise_table()
  OWNER TO postgres; 

A priori il manquait des LOOP et certains étaient mal placés.
Merci pour vos aides.

Sadewizz

gleu

Administrateur

Re : Fonction permettant de créer de générer des tables

Ah oui, en effet. Quand le "OPEN curs" était dans la boucle, la première itération ouvrait le curseur et la deuxième itération ne pouvait que renvoyer une erreur comme quoi le curseur était déjà ouvert.

---

Guillaume.