pggeodb.nancy.inra.fr/db_toff - db_toff on pggeodb.nancy.inra.fr

Function: ps_transpose

Schema

support

Owner

albenard

Descriptions

Fonction permettant de dénormaliser (transposer) un contenu sous la forme rowid, category, values.

Options

Definition

CREATE OR REPLACE FUNCTION support.ps_transpose (
 requetesql text,
 groupeecriture varchar,
 groupelecture varchar,
 table_sortie varchar = 'support.sortie_normalise'::character varying,
 typerowid varchar = 'integer'::character varying,
 typevalues varchar = 'text'::character varying
)
RETURNS boolean AS
$span$
DECLARE
tabletmp refcursor;
tableau_origine refcursor;
   req_premierligne text;
   req_liste_categories text;
   req_crosstab text;
   resultat record;
   entete_categories text;
   nb_categ integer;
BEGIN
-- L'extension tablefuncs doit être préalablement installée sur la base :
-- CREATE extension tablefunc;
-- Le paramètre requetesql doit contenir une chaîne sql correspondant à une requête qui extrait 3 colonnes nommées (et dans l'ordre) :
-- rowid (colonne qui contiendra les identifiants de chaque ligne
-- category (données catégorielles qui représenteront les entêtes de colonne après transposition
-- values (données qui figureront dans le tableau après transposition.
-- Cette requête ne doit pas contenir de clause ORDER BY.
-- Le paramètre groupeecriture représente un role postgresql qui disposera du privilège ALL sur la table de sortie
-- Le paramètre groupelecture représente un role postgresql qui disposera du privilège SELECT sur la table de sortie
-- Le paramètre optionnel table_sortie contient le nom de la table qui sera générée pour stocker le résultat de la normalisation
-- Le paramètre optionnel typerowid contient le type de données qui seront présents dans la colonne rowid
-- Le paramètre optionnel typevalues contient le type de données qui seront présentes dans la colonne values

-- A noter que si vous avez besoin d'autres informations il faudra effectuer des jointures avec la table de résultat alimentée
-- par cette fonction pour obtenir ces informations.

 -- Force l'ordre de tri de la requete sur rowid,category (colonnes 1 et 2)
 requetesql = requetesql || ' order by 1,2';

 -- Supprime la table de sortie
 execute 'drop table if exists ' || table_sortie;
 
 -- Vérifie présence des colonnes nécessaires (rowid, category et values)
 req_premierligne = requetesql || ' limit 1';  
 OPEN tabletmp FOR EXECUTE req_premierligne;
 CLOSE tabletmp;
 -- La vérification sera implémentée ultérieurement
 
 -- Récupère la liste des catégories pour créer la liste des colonnes en sortie de la fonction crosstab
 nb_categ = 0;
 entete_categories = '';
 req_liste_categories = 'Select distinct category from (' || requetesql || ')ssreq';
 OPEN tabletmp FOR EXECUTE req_liste_categories;
 LOOP
  FETCH tabletmp INTO resultat;
  IF found THEN
    IF nb_categ = 0 THEN
entete_categories = entete_categories || quote_ident(resultat.category) || ' ' || typevalues;
         ELSE
          entete_categories = entete_categories || ',' || quote_ident(resultat.category) || ' ' || typevalues;
       END IF;
       nb_categ = nb_categ + 1;
       
    ELSE
    EXIT;  
   END IF;
 END LOOP;
 CLOSE tabletmp;
 IF nb_categ > 0 THEN
  RAISE NOTICE 'Plusieurs catégories existent';
   entete_categories = '(rowid ' || typerowid || ',' || entete_categories || ')';
  ELSE
   return true; -- La table de sortie sera toutefois inexistante ; à rediscuter peut-être
 END IF;

 req_crosstab = 'CREATE TABLE ' || table_sortie ||' AS select * from crosstab(' || quote_literal(requetesql)  || ',' || quote_literal(req_liste_categories) || ') AS ' || entete_categories ;
 execute req_crosstab;
 execute 'GRANT ALL ON ' || table_sortie  || ' TO ' || groupeecriture;
 execute 'GRANT SELECT ON ' || table_sortie  || ' TO ' || groupelecture;
 return true;
EXCEPTION
WHEN OTHERS THEN
  RAISE NOTICE 'ps_transpose : % %', sqlstate, sqlerrm;
return false;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION support.ps_transpose(requetesql text, groupeecriture varchar, groupelecture varchar, table_sortie varchar, typerowid varchar, typevalues varchar)
IS 'Fonction permettant de dénormaliser (transposer) un contenu sous la forme rowid, category, values.';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 13/03/2014 13:23