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

Function: droprasterconstraints

Schema

public

Owner

albenard

Descriptions

args: rastschema, rasttable, rastcolumn, constraints - Drops PostGIS raster constraints that refer to a raster table column. Useful if you need to reload data or update your raster column data.

Options

Definition

CREATE OR REPLACE FUNCTION public.droprasterconstraints (
 rastschema name,
 rasttable name,
 rastcolumn name,
 variadic constraints text []
)
RETURNS boolean AS
$span$
DECLARE
max int;
x int;
schema name;
sql text;
kw text;
rtn boolean;
cnt int;
BEGIN
cnt := 0;
max := array_length(constraints, 1);
IF max < 1 THEN
RAISE NOTICE 'No constraints indicated to be dropped.  Doing nothing';
RETURN TRUE;
END IF;

-- validate schema
schema := NULL;
IF length($1) > 0 THEN
sql := 'SELECT nspname FROM pg_namespace '
|| 'WHERE nspname = ' || quote_literal($1)
|| 'LIMIT 1';
EXECUTE sql INTO schema;

IF schema IS NULL THEN
RAISE EXCEPTION 'The value provided for schema is invalid';
RETURN FALSE;
END IF;
END IF;

IF schema IS NULL THEN
sql := 'SELECT n.nspname AS schemaname '
|| 'FROM pg_catalog.pg_class c '
|| 'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace '
|| 'WHERE c.relkind = ' || quote_literal('r')
|| ' AND n.nspname NOT IN (' || quote_literal('pg_catalog')
|| ', ' || quote_literal('pg_toast')
|| ') AND pg_catalog.pg_table_is_visible(c.oid)'
|| ' AND c.relname = ' || quote_literal($2);
EXECUTE sql INTO schema;

IF schema IS NULL THEN
RAISE EXCEPTION 'The table % does not occur in the search_path', quote_literal($2);
RETURN FALSE;
END IF;
END IF;

<<kwloop>>
FOR x in 1..max LOOP
kw := trim(both from lower(constraints[x]));

BEGIN
CASE
WHEN kw = 'srid' THEN
RAISE NOTICE 'Dropping SRID constraint';
rtn :=  public._drop_raster_constraint_srid(schema, $2, $3);
WHEN kw IN ('scale_x', 'scalex') THEN
RAISE NOTICE 'Dropping scale-X constraint';
rtn :=  public._drop_raster_constraint_scale(schema, $2, $3, 'x');
WHEN kw IN ('scale_y', 'scaley') THEN
RAISE NOTICE 'Dropping scale-Y constraint';
rtn :=  public._drop_raster_constraint_scale(schema, $2, $3, 'y');
WHEN kw = 'scale' THEN
RAISE NOTICE 'Dropping scale-X constraint';
rtn :=  public._drop_raster_constraint_scale(schema, $2, $3, 'x');
RAISE NOTICE 'Dropping scale-Y constraint';
rtn :=  public._drop_raster_constraint_scale(schema, $2, $3, 'y');
WHEN kw IN ('blocksize_x', 'blocksizex', 'width') THEN
RAISE NOTICE 'Dropping blocksize-X constraint';
rtn :=  public._drop_raster_constraint_blocksize(schema, $2, $3, 'width');
WHEN kw IN ('blocksize_y', 'blocksizey', 'height') THEN
RAISE NOTICE 'Dropping blocksize-Y constraint';
rtn :=  public._drop_raster_constraint_blocksize(schema, $2, $3, 'height');
WHEN kw = 'blocksize' THEN
RAISE NOTICE 'Dropping blocksize-X constraint';
rtn :=  public._drop_raster_constraint_blocksize(schema, $2, $3, 'width');
RAISE NOTICE 'Dropping blocksize-Y constraint';
rtn :=  public._drop_raster_constraint_blocksize(schema, $2, $3, 'height');
WHEN kw IN ('same_alignment', 'samealignment', 'alignment') THEN
RAISE NOTICE 'Dropping alignment constraint';
rtn :=  public._drop_raster_constraint_alignment(schema, $2, $3);
WHEN kw IN ('regular_blocking', 'regularblocking') THEN
rtn :=  public._drop_raster_constraint_regular_blocking(schema, $2, $3);

RAISE NOTICE 'Dropping coverage tile constraint required for regular blocking';
rtn :=  public._drop_raster_constraint_coverage_tile(schema, $2, $3);

IF rtn IS NOT FALSE THEN
RAISE NOTICE 'Dropping spatially unique constraint required for regular blocking';
rtn :=  public._drop_raster_constraint_spatially_unique(schema, $2, $3);
END IF;
WHEN kw IN ('num_bands', 'numbands') THEN
RAISE NOTICE 'Dropping number of bands constraint';
rtn :=  public._drop_raster_constraint_num_bands(schema, $2, $3);
WHEN kw IN ('pixel_types', 'pixeltypes') THEN
RAISE NOTICE 'Dropping pixel type constraint';
rtn :=  public._drop_raster_constraint_pixel_types(schema, $2, $3);
WHEN kw IN ('nodata_values', 'nodatavalues', 'nodata') THEN
RAISE NOTICE 'Dropping nodata value constraint';
rtn :=  public._drop_raster_constraint_nodata_values(schema, $2, $3);
WHEN kw IN ('out_db', 'outdb') THEN
RAISE NOTICE 'Dropping out-of-database constraint';
rtn :=  public._drop_raster_constraint_out_db(schema, $2, $3);
WHEN kw = 'extent' THEN
RAISE NOTICE 'Dropping maximum extent constraint';
rtn :=  public._drop_raster_constraint_extent(schema, $2, $3);
ELSE
RAISE NOTICE 'Unknown constraint: %.  Skipping', quote_literal(constraints[x]);
CONTINUE kwloop;
END CASE;
END;

IF rtn IS FALSE THEN
cnt := cnt + 1;
RAISE WARNING 'Unable to drop constraint: %.  Skipping', quote_literal(constraints[x]);
END IF;

END LOOP kwloop;

IF cnt = max THEN
RAISE EXCEPTION 'None of the constraints specified could be dropped.  Is the schema name, table name or column name incorrect?';
RETURN FALSE;
END IF;

RETURN TRUE;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION public.droprasterconstraints(rastschema name, rasttable name, rastcolumn name, variadic constraints text [])
IS 'args: rastschema, rasttable, rastcolumn, constraints - Drops PostGIS raster constraints that refer to a raster table column. Useful if you need to reload data or update your raster column data.';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 26/02/2014 11:51