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

Function: addrasterconstraints

Schema

public

Owner

albenard

Descriptions

args: rastschema, rasttable, rastcolumn, VARIADIC constraints - Adds raster constraints to a loaded raster table for a specific column that constrains spatial ref, scaling, blocksize, alignment, bands, band type and a flag to denote if raster column is regularly blocked. The table must be loaded with data for the constraints to be inferred. Returns true of the constraint setting was accomplished and if issues a notice.

Options

Definition

CREATE OR REPLACE FUNCTION public.addrasterconstraints (
 rastschema name,
 rasttable name,
 rastcolumn name,
 variadic constraints text []
)
RETURNS boolean AS
$span$
DECLARE
max int;
cnt int;
sql text;
schema name;
x int;
kw text;
rtn boolean;
BEGIN
cnt := 0;
max := array_length(constraints, 1);
IF max < 1 THEN
RAISE NOTICE 'No constraints indicated to be added.  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 'Adding SRID constraint';
rtn :=  public._add_raster_constraint_srid(schema, $2, $3);
WHEN kw IN ('scale_x', 'scalex') THEN
RAISE NOTICE 'Adding scale-X constraint';
rtn :=  public._add_raster_constraint_scale(schema, $2, $3, 'x');
WHEN kw IN ('scale_y', 'scaley') THEN
RAISE NOTICE 'Adding scale-Y constraint';
rtn :=  public._add_raster_constraint_scale(schema, $2, $3, 'y');
WHEN kw = 'scale' THEN
RAISE NOTICE 'Adding scale-X constraint';
rtn :=  public._add_raster_constraint_scale(schema, $2, $3, 'x');
RAISE NOTICE 'Adding scale-Y constraint';
rtn :=  public._add_raster_constraint_scale(schema, $2, $3, 'y');
WHEN kw IN ('blocksize_x', 'blocksizex', 'width') THEN
RAISE NOTICE 'Adding blocksize-X constraint';
rtn :=  public._add_raster_constraint_blocksize(schema, $2, $3, 'width');
WHEN kw IN ('blocksize_y', 'blocksizey', 'height') THEN
RAISE NOTICE 'Adding blocksize-Y constraint';
rtn :=  public._add_raster_constraint_blocksize(schema, $2, $3, 'height');
WHEN kw = 'blocksize' THEN
RAISE NOTICE 'Adding blocksize-X constraint';
rtn :=  public._add_raster_constraint_blocksize(schema, $2, $3, 'width');
RAISE NOTICE 'Adding blocksize-Y constraint';
rtn :=  public._add_raster_constraint_blocksize(schema, $2, $3, 'height');
WHEN kw IN ('same_alignment', 'samealignment', 'alignment') THEN
RAISE NOTICE 'Adding alignment constraint';
rtn :=  public._add_raster_constraint_alignment(schema, $2, $3);
WHEN kw IN ('regular_blocking', 'regularblocking') THEN
RAISE NOTICE 'Adding coverage tile constraint required for regular blocking';
rtn :=  public._add_raster_constraint_coverage_tile(schema, $2, $3);
IF rtn IS NOT FALSE THEN
RAISE NOTICE 'Adding spatially unique constraint required for regular blocking';
rtn :=  public._add_raster_constraint_spatially_unique(schema, $2, $3);
END IF;
WHEN kw IN ('num_bands', 'numbands') THEN
RAISE NOTICE 'Adding number of bands constraint';
rtn :=  public._add_raster_constraint_num_bands(schema, $2, $3);
WHEN kw IN ('pixel_types', 'pixeltypes') THEN
RAISE NOTICE 'Adding pixel type constraint';
rtn :=  public._add_raster_constraint_pixel_types(schema, $2, $3);
WHEN kw IN ('nodata_values', 'nodatavalues', 'nodata') THEN
RAISE NOTICE 'Adding nodata value constraint';
rtn :=  public._add_raster_constraint_nodata_values(schema, $2, $3);
WHEN kw IN ('out_db', 'outdb') THEN
RAISE NOTICE 'Adding out-of-database constraint';
rtn :=  public._add_raster_constraint_out_db(schema, $2, $3);
WHEN kw = 'extent' THEN
RAISE NOTICE 'Adding maximum extent constraint';
rtn :=  public._add_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 add constraint: %.  Skipping', quote_literal(constraints[x]);
END IF;

END LOOP kwloop;

IF cnt = max THEN
RAISE EXCEPTION 'None of the constraints specified could be added.  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.addrasterconstraints(rastschema name, rasttable name, rastcolumn name, variadic constraints text [])
IS 'args: rastschema, rasttable, rastcolumn, VARIADIC constraints - Adds raster constraints to a loaded raster table for a specific column that constrains spatial ref, scaling, blocksize, alignment, bands, band type and a flag to denote if raster column is regularly blocked. The table must be loaded with data for the constraints to be inferred. Returns true of the constraint setting was accomplished and if issues a notice.';

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