db_biljou_carto on db.silva.inrae.fr

Function: ps_creation_index_data_rel

Schema

safran

Owner

postgres

Descriptions

There is no description for function ps_creation_index_data_rel

Options

Definition

CREATE OR REPLACE FUNCTION safran.ps_creation_index_data_rel (
)
RETURNS integer AS
$span$
DECLARE
r1 record;
schema text DEFAULT 'safran';
idx1 text;
idx2 text;
idx3 text;

BEGIN
FOR r1 IN
SELECT DISTINCT caind_nom_table
FROM t_carte_indicateur_caind
WHERE caind_casind_id IS NOT NULL
LOOP
idx1 := r1.caind_nom_table||'_idx_defrel_cartid';
idx2 := r1.caind_nom_table||'_idx_debrel_cartid';
idx3 := r1.caind_nom_table||'_idx_durrel_cartid';

IF NOT EXISTS (
SELECT 1
   FROM   pg_class c
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE  c.relname = idx1
   AND    n.nspname = schema
) THEN
EXECUTE 'CREATE INDEX '|| idx1 ||' ON '|| schema ||'.'|| r1.caind_nom_table ||' USING btree (defrel_cartid);';
END IF;

IF NOT EXISTS (
SELECT 1
   FROM   pg_class c
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE  c.relname = idx2
   AND    n.nspname = schema
) THEN
EXECUTE 'CREATE INDEX '|| idx2 ||' ON '|| schema ||'.'|| r1.caind_nom_table ||' USING btree (debrel_cartid);';
END IF;

IF NOT EXISTS (
SELECT 1
   FROM   pg_class c
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE  c.relname = idx3
   AND    n.nspname = schema
) THEN
EXECUTE 'CREATE INDEX '|| idx3 ||' ON '|| schema ||'.'|| r1.caind_nom_table ||' USING btree (durrel_cartid);';
END IF;

END LOOP;

RETURN 0;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100;

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