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

Function: ps_inventaire

Schema

public

Owner

postgres

Descriptions

Fonction permettant de renvoyer le nombre de lignes de chaque table excepté pour les schémas pg*, 'information_schema' et 'topology'. La table spatial_ref_sys est également exclue de l'inventare.

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_inventaire (
 out v_schema varchar,
 out v_table varchar,
 out nb_enreg bigint
)
RETURNS SETOF record AS
$span$
DECLARE
 curs_table refcursor; -- cuirseur recevant la liste des tables
 rec_table   record; -- Enregistrement contenant la ligne en cours de traitement
 query_liste_table text;
BEGIN
 --query_liste_table= "SELECT table_schema,table_name , table_type FROM information_schema.tables where table_schema in (SELECT schema_name FROM information_schema.schemata where schema_name not like 'pg%' and schema_name not IN('information_schema','topology')) and table_type in ('BASE TABLE') and table_name not in ('spatial_ref_sys') order by table_schema, table_name;";
 open curs_table FOR SELECT table_schema,table_name , table_type FROM information_schema.tables where table_schema in (SELECT schema_name FROM information_schema.schemata where schema_name not like 'pg%' and schema_name not IN('information_schema','topology')) and table_type in ('BASE TABLE') and table_name not in ('spatial_ref_sys') order by table_schema, table_name;
 LOOP
  FETCH curs_table into rec_table;
  if found then
v_schema = rec_table.table_schema;
       v_table = rec_table.table_name;
       execute 'select count(*) from ' || v_schema || '.' || v_table ||';' into nb_enreg;

       return next;
ELSE
    EXIT;  
   end if;
     
 end loop;
 
   
EXCEPTION
WHEN OTHERS THEN
raise notice '% ',  sqlerrm;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100 ROWS 1000;

COMMENT ON FUNCTION public.ps_inventaire(out v_schema varchar, out v_table varchar, out nb_enreg bigint)
IS 'Fonction permettant de renvoyer le nombre de lignes de chaque table excepté pour les schémas pg*, ''information_schema'' et ''topology''. La table spatial_ref_sys est également exclue de l''inventare.';

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