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

Function: addoverviewconstraints

Schema

public

Owner

postgres

Descriptions

args: ovschema, ovtable, ovcolumn, refschema, reftable, refcolumn, ovfactor - Tag a raster column as being an overview of another.

Options

Definition

CREATE OR REPLACE FUNCTION public.addoverviewconstraints (
 ovschema name,
 ovtable name,
 ovcolumn name,
 refschema name,
 reftable name,
 refcolumn name,
 ovfactor integer
)
RETURNS boolean AS
$span$
DECLARE
x int;
s name;
t name;
oschema name;
rschema name;
sql text;
rtn boolean;
BEGIN
FOR x IN 1..2 LOOP
s := '';

IF x = 1 THEN
s := $1;
t := $2;
ELSE
s := $4;
t := $5;
END IF;

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

IF s IS NULL THEN
RAISE EXCEPTION 'The value % is not a valid schema', quote_literal(s);
RETURN FALSE;
END IF;
END IF;

-- no schema, determine what it could be using the table
IF length(s) < 1 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(t);
EXECUTE sql INTO s;

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

IF x = 1 THEN
oschema := s;
ELSE
rschema := s;
END IF;
END LOOP;

-- reference raster
rtn := _add_overview_constraint(oschema, $2, $3, rschema, $5, $6, $7);
IF rtn IS FALSE THEN
RAISE EXCEPTION 'Unable to add the overview constraint.  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.addoverviewconstraints(ovschema name, ovtable name, ovcolumn name, refschema name, reftable name, refcolumn name, ovfactor integer)
IS 'args: ovschema, ovtable, ovcolumn, refschema, reftable, refcolumn, ovfactor - Tag a raster column as being an overview of another.';

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