db_renfor

Function: ps_inventaire

Schema

public

Owner

albenard

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 LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100 ROWS 1000
AS
$body$
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
 BEGIN
  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 || '.' || quote_ident(v_table) ||';' into nb_enreg;

       return next;
ELSE
    EXIT;  
   end if;
   EXCEPTION  
   WHEN OTHERS THEN
    raise notice 'Erreur boucle : schema : %, table : %',  v_schema,v_table;
 END;
 end loop;
 
   
EXCEPTION
WHEN OTHERS THEN
raise notice 'Erreur generale : % - schema : %, table : %',  sqlerrm,v_schema,v_table;
END;
$body$;

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)