-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- $Id$
--
-- WKTRaster - Raster Type for PostGIS
-- http://postgis.refractions.net/support/wiki/index.php?WKTRasterHomePage
--
-- Copyright (c) 2009 Sandro Santilli <strk@keybit.net>
--
-- This is free software; you can redistribute and/or modify it under
-- the terms of the GNU General Public Licence. See the COPYING file.
--
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- TODO:
-- 1) Add regression tests
-- 2) Complete the warning and notes below.
--
-- WARNING: Any change in this file must be evaluated for compatibility.
--
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

#define CREATEFUNCTION CREATE OR REPLACE FUNCTION
#define _IMMUTABLE_STRICT IMMUTABLE STRICT
#define _IMMUTABLE IMMUTABLE
#define _STABLE_STRICT STABLE STRICT
#define _STABLE STABLE
#define _VOLATILE_STRICT VOLATILE STRICT
#define _VOLATILE VOLATILE
#define _STRICT STRICT
#define _SECURITY_DEFINER SECURITY DEFINER

-- BEGIN;

------------------------------------------------------------------------------
--  RASTER TYPE
------------------------------------------------------------------------------

CREATEFUNCTION rt_raster_in(cstring)
    RETURNS raster
    AS 'MODULE_PATHNAME','RASTER_in'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_raster_out(raster)
    RETURNS cstring
    AS 'MODULE_PATHNAME','RASTER_out'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATE TYPE raster (
    alignment = double,
    internallength = variable,
    input = rt_raster_in,
    output = rt_raster_out,
    storage = extended
);

------------------------------------------------------------------------------
--  FUNCTIONS
------------------------------------------------------------------------------

CREATEFUNCTION rt_lib_version()
    RETURNS text
    AS 'MODULE_PATHNAME', 'RASTER_lib_version'
    LANGUAGE 'C' IMMUTABLE; -- a new lib will require a new session

CREATEFUNCTION rt_lib_build_date()
    RETURNS text
    AS 'MODULE_PATHNAME', 'RASTER_lib_build_date'
    LANGUAGE 'C' IMMUTABLE; -- a new lib will require a new session

CREATEFUNCTION rt_bytea(raster)
    RETURNS bytea
    AS 'MODULE_PATHNAME', 'RASTER_to_bytea'
    LANGUAGE 'C' IMMUTABLE;

CREATEFUNCTION rt_raster_to_box2d(raster)
    RETURNS BOX2D
    AS 'MODULE_PATHNAME','RASTER_to_BOX2DFLOAT4'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_raster_envelope(raster)
    RETURNS GEOMETRY
    AS 'MODULE_PATHNAME','RASTER_envelope'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_srid(raster)
    RETURNS integer
    AS 'MODULE_PATHNAME','RASTER_getSRID'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_width(raster)
    RETURNS integer
    AS 'MODULE_PATHNAME','RASTER_getWidth'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_height(raster)
    RETURNS integer
    AS 'MODULE_PATHNAME','RASTER_getHeight'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_pixelsizex(raster)
    RETURNS float8
    AS 'MODULE_PATHNAME','RASTER_getXPixelSize'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_pixelsizey(raster)
    RETURNS float8
    AS 'MODULE_PATHNAME','RASTER_getYPixelSize'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_rotationx(raster)
    RETURNS float8
    AS 'MODULE_PATHNAME','RASTER_getXRotation'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_rotationy(raster)
    RETURNS float8
    AS 'MODULE_PATHNAME','RASTER_getYRotation'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_upperleftx(raster)
    RETURNS float8
    AS 'MODULE_PATHNAME','RASTER_getXUpperLeft'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_upperlefty(raster)
    RETURNS float8
    AS 'MODULE_PATHNAME','RASTER_getYUpperLeft'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_georeference(raster)
    RETURNS TEXT
    AS 'MODULE_PATHNAME','RASTER_getGeoReference'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_numbands(raster)
    RETURNS integer
    AS 'MODULE_PATHNAME','RASTER_getNumBands'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_bandpixeltype(raster, integer)
    RETURNS text
    AS 'MODULE_PATHNAME','RASTER_getBandPixelTypeName'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_bandnodatavalue(raster, integer)
    RETURNS float4
    AS 'MODULE_PATHNAME','RASTER_getBandNoDataValue'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_bandpath(raster, integer)
    RETURNS text
    AS 'MODULE_PATHNAME','RASTER_getBandPath'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_value(raster, integer, integer, integer)
    RETURNS float8
    AS 'MODULE_PATHNAME','RASTER_getPixelValue'
    LANGUAGE 'C' _IMMUTABLE_STRICT;

-- TODO: To be implemented
CREATEFUNCTION rt_setvalue(raster, integer, integer, integer, float8)
    RETURNS float8
    AS 'MODULE_PATHNAME','RASTER_setPixelValue'
    LANGUAGE 'C' _IMMUTABLE_STRICT;
--
-- rt_MakeEmptyRaster( <width>, <height>, <ipx>, <ipy>,
--                                        <scalex>, <scaley>,
--                                        <skewx>, <skewy>,
--                                        <srid>)
--
CREATEFUNCTION rt_MakeEmptyRaster(int, int, float8, float8, float8, float8, float8, float8, int4)
    RETURNS RASTER
    AS 'MODULE_PATHNAME', 'RASTER_makeEmpty'
    LANGUAGE 'C' IMMUTABLE; -- a new lib will require a new session

------------------------------------------------------------------------------
--  CASTS
------------------------------------------------------------------------------

CREATE CAST (raster AS box2d)
    WITH FUNCTION rt_raster_to_box2d(raster) AS IMPLICIT;

CREATE CAST (raster AS geometry)
    WITH FUNCTION rt_raster_envelope(raster) AS IMPLICIT;

CREATE CAST (raster AS bytea)
    WITH FUNCTION rt_bytea(raster) AS IMPLICIT;

------------------------------------------------------------------------------
--  GiST index OPERATOR support functions
------------------------------------------------------------------------------

CREATEFUNCTION rt_overleft(raster, raster)
    RETURNS bool
    AS 'select geometry_overleft($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_overright(raster, raster)
    RETURNS bool
    AS 'select geometry_overright($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_left(raster, raster)
    RETURNS bool
    AS 'select geometry_left($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_right(raster, raster)
    RETURNS bool
    AS 'select geometry_right($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_overabove(raster, raster)
    RETURNS bool
    AS 'select geometry_overabove($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_overbelow(raster, raster)
    RETURNS bool
    AS 'select geometry_overbelow($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_above(raster, raster)
    RETURNS bool
    AS 'select geometry_above($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_below(raster, raster)
    RETURNS bool
    AS 'select geometry_below($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_same(raster, raster)
    RETURNS bool
    AS 'select geometry_same($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_contained(raster, raster)
    RETURNS bool
    AS 'select geometry_contained($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_contain(raster, raster)
    RETURNS bool
    AS 'select geometry_contain($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

CREATEFUNCTION rt_overlap(raster, raster)
    RETURNS bool
    AS 'select geometry_overlap($1::geometry, $2::geometry)'
    LANGUAGE 'SQL' _IMMUTABLE_STRICT;

------------------------------------------------------------------------------
--  GiST index OPERATORs
------------------------------------------------------------------------------

CREATE OPERATOR << (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_left,
   COMMUTATOR = '>>',
   RESTRICT = positionsel, JOIN = positionjoinsel
);

CREATE OPERATOR &< (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_overleft,
   COMMUTATOR = '&>',
   RESTRICT = positionsel, JOIN = positionjoinsel
);

CREATE OPERATOR <<| (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_below,
   COMMUTATOR = '|>>',
   RESTRICT = positionsel, JOIN = positionjoinsel
);

CREATE OPERATOR &<| (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_overbelow,
   COMMUTATOR = '|&>',
   RESTRICT = positionsel, JOIN = positionjoinsel
);

CREATE OPERATOR && (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_overlap,
   COMMUTATOR = '&&',
   RESTRICT = postgis_gist_sel, JOIN = postgis_gist_joinsel
);

CREATE OPERATOR &> (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_overright,
   COMMUTATOR = '&<',
   RESTRICT = positionsel, JOIN = positionjoinsel
);

CREATE OPERATOR >> (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_right,
   COMMUTATOR = '<<',
   RESTRICT = positionsel, JOIN = positionjoinsel
);

CREATE OPERATOR |&> (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_overabove,
   COMMUTATOR = '&<|',
   RESTRICT = positionsel, JOIN = positionjoinsel
);

CREATE OPERATOR |>> (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_above,
   COMMUTATOR = '<<|',
   RESTRICT = positionsel, JOIN = positionjoinsel
);

CREATE OPERATOR ~= (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_same,
   COMMUTATOR = '~=',
   RESTRICT = eqsel, JOIN = eqjoinsel
);

CREATE OPERATOR @ (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_contained,
   COMMUTATOR = '~',
   RESTRICT = contsel, JOIN = contjoinsel
);

CREATE OPERATOR ~ (
   LEFTARG = raster, RIGHTARG = raster, PROCEDURE = rt_contain,
   COMMUTATOR = '@',
   RESTRICT = contsel, JOIN = contjoinsel
);

------------------------------------------------------------------------------
-- RASTER_COLUMNS
--
-- The metadata is documented in the WKT Raster specification:
-- http://www.cef-cfr.ca/index.php?n=Membres.PierreRacineWKTRasterSpecifications
--
-- WARNING:
-- The definition is still being discussed, so chances are it will
-- change in near future. Consider current state a PROTOTYPE.
------------------------------------------------------------------------------

CREATE TABLE raster_columns (
    r_table_catalog varchar(256) not null,
    r_table_schema varchar(256) not null,
    r_table_name varchar(256) not null,
    r_column varchar(256) not null,
    srid integer not null,
    pixel_types varchar[] not null,
    out_db boolean not null,
    regular_blocking boolean not null,
    nodata_values double precision[],
    pixelsize_x double precision,
    pixelsize_y double precision,
    blocksize_x double precision,
    blocksize_y double precision,
    extent GEOMETRY,

    CONSTRAINT raster_columns_pk primary key (
        r_table_catalog,
        r_table_schema,
        r_table_name,
        r_column )
) WITH OIDS;


------------------------------------------------------------------------------
-- ADDRASTERCOLUMN
--      <catalog> <schema> <table> <column> <srid> <pixel_types>
--      <out_db> <regular_blocking>
--      <nodata_values>
--      <pixelsize_x> <pixelsize_y>
--      <blocksize_x> <blocksize_y>
--      <extent>
-------------------------------------------------------------------------------
--
-- TODO: to be documented
--
-------------------------------------------------------------------------------

CREATEFUNCTION AddRasterColumn(varchar, varchar, varchar, varchar,
                               integer,             -- srid
                               varchar[],           -- pixel_types
                               boolean,             -- out_db
                               boolean,             -- regular_blocking
                               double precision[],  -- nodata_values
                               double precision,    -- pixelsize_x
                               double precision,    -- pixelsize_y
                               integer,             -- blocksize_x
                               integer,             -- blocksize_y
                               geometry)            -- extent

    RETURNS text AS
$$
DECLARE

    -- Function arguments
    p_catalog_name ALIAS FOR $1;
    p_schema_name ALIAS FOR $2;
    p_table_name ALIAS FOR $3;
    p_column_name ALIAS FOR $4;
    p_srid ALIAS FOR $5;
    p_pixel_types ALIAS FOR $6;
    p_out_db ALIAS FOR $7;
    p_regular_blocking ALIAS FOR $8;
    p_nodata_values ALIAS FOR $9;
    p_pixelsize_x ALIAS FOR $10;
    p_pixelsize_y ALIAS FOR $11;
    p_blocksize_x ALIAS FOR $12;
    p_blocksize_y ALIAS FOR $13;
    p_extent ALIAS FOR $14;

    -- Local helpers
    sql text;
    real_schema name;
    srid_into varchar;
    geometry_op_into varchar;
    pixel_types varchar[];
    pixel_types_size integer := 0;
    pixel_types_found integer := 0;
    nodata_values_size integer := 0;

BEGIN

    RAISE DEBUG 'Parameters: catalog=%, schema=%, table=%, column=%, srid=%, pixel_types=%, out_db=%, regular_blocking=%, nodata_values=%, pixelsize_x=%, pixelsize_y=%, blocksize_x=%, blocksize_y=%',
                 p_catalog_name, p_schema_name, p_table_name, p_column_name, p_srid, p_pixel_types, p_out_db, p_regular_blocking, p_nodata_values, p_pixelsize_x, p_pixelsize_y, p_blocksize_x, p_blocksize_y;

    -- Validate required parametersa and combinations
    IF ( (p_catalog_name IS NULL) OR (p_schema_name IS NULL)
          OR (p_table_name IS NULL) OR (p_column_name IS NULL) ) THEN
        RAISE EXCEPTION 'Name of catalog, schema, table or column IS NULL, value expected';
        RETURN 'fail';
    END IF;

    IF ( p_srid IS NULL ) THEN
        RAISE EXCEPTION 'SRID IS NULL, value expected';
        RETURN 'fail';
    END IF;

    IF ( p_pixel_types IS NULL ) THEN
        RAISE EXCEPTION 'Array of pixel types IS NULL, value expected';
        RETURN 'fail';
    END IF;

    IF ( p_out_db IS NULL ) THEN
        RAISE EXCEPTION 'out_db IS NULL, value expected';
        RETURN 'fail';
    END IF;

    IF ( p_regular_blocking IS NULL ) THEN
        RAISE EXCEPTION 'regular_blocking IS NULL, value expected';
        RETURN 'fail';
    END IF;

    IF ( p_regular_blocking = true ) THEN
       IF ( p_blocksize_x IS NULL or p_blocksize_y IS NULL ) THEN
           RAISE EXCEPTION 'blocksize_x/blocksize_y IS NULL, value expected if regular_blocking is TRUE';
           RETURN 'fail';
       END IF;
    ELSE
       IF ( p_blocksize_x IS NOT NULL or p_blocksize_y IS NOT NULL ) THEN
           RAISE EXCEPTION 'blocksize_x/blocksize_y values given, but regular_blocking is FALSE';
           RETURN 'fail';
       END IF;
    END IF;


    -- Verify SRID
    IF ( (p_srid != 0) AND (p_srid != -1) ) THEN
        SELECT SRID INTO srid_into FROM spatial_ref_sys WHERE SRID = p_srid;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Invalid SRID';
            RETURN 'fail';
        END IF;
        RAISE DEBUG 'Verified SRID = %', p_srid;
    END IF;


    -- Verify PIXEL TYPE
    -- TODO: If only PostgreSQL 8.2+ supported, use @> operator instead of brute-force lookup
    --       SELECT p_pixel_types <@ pixel_types INTO pixel_types_found_into; -- boolean
    pixel_types := ARRAY['1BB','2BUI','4BUI','8BSI','8BUI','16BSI','16BUI','32BSI','32BUI','16BF','32BF','64BF'];

    FOR npti IN array_lower(p_pixel_types, 1) .. array_upper(p_pixel_types, 1) LOOP

        pixel_types_found := 0;
        FOR pti IN array_lower(pixel_types, 1) .. array_upper(pixel_types, 1) LOOP
            IF p_pixel_types[npti] = pixel_types[pti] THEN
                pixel_types_found := 1;
                RAISE DEBUG 'Identified pixel type %', p_pixel_types[npti];
            END IF;
        END LOOP;

        IF pixel_types_found = 0 THEN
            RAISE EXCEPTION 'Invalid pixel type % - valid ones are %', p_pixel_types[npti], pixel_types;
            RETURN 'fail';
        END IF;

        pixel_types_size := pixel_types_size + 1;
    END LOOP;


    -- Verify out_db
    -- TODO: Waiting for out-db rasters
    IF ( p_out_db = true ) THEN
            RAISE EXCEPTION 'TODO: Outside database rasters not implemented yet';
            RETURN 'fail';
    END IF;


    -- Verify NODATA
    -- TODO: Validate if NODATA values matche range of corresponding pixel types
    nodata_values_size := 1 + array_upper(p_nodata_values, 1) - array_lower(p_nodata_values, 1);
    IF ( pixel_types_size != nodata_values_size ) THEN
        RAISE EXCEPTION 'Number of pixel types (%) and NODATA values (%) do not match',
                        pixel_types_size, nodata_values_size;
        RETURN 'fail';
    END IF;


    -- Verify extent geometry
    IF ( p_extent IS NOT NULL ) THEN

        -- Verify POLYGON type
        SELECT GeometryType(p_extent) INTO geometry_op_into;
        IF ( NOT ( geometry_op_into = 'POLYGON' ) ) THEN
            RAISE EXCEPTION 'extent is of invalid type (%), expected simple and non-rotated POLYGON', geometry_op_into;
            RETURN 'fail';
        END IF;

        -- Verify SRID
        SELECT ST_SRID(p_extent) INTO srid_into;
        IF ( p_srid != srid_into::integer ) THEN
            RAISE EXCEPTION 'SRID values for raster (%) and extent (%) do not match', p_srid, srid_into;
            RETURN 'fail';
        END IF;
    END IF;


    -- Verify regular_blocking
    IF ( p_regular_blocking = true ) THEN

        IF ( p_blocksize_x IS NULL or p_blocksize_y IS NULL ) THEN
            RAISE EXCEPTION 'unexpected NULL for blocksize_x or blocksize_y';
            RETURN 'fail';
        END IF;

        -- Verify extent is non-rotated rectangle
        IF ( p_extent IS NOT NULL ) THEN
            -- TODO: Replace with bounding box overlapping test (&&)
            SELECT ST_Equals(p_extent, ST_Envelope(p_extent)) INTO geometry_op_into;
            IF ( NOT ( geometry_op_into = 't' ) ) THEN
                RAISE EXCEPTION 'extent does not represent non-rotated rectangle';
                RETURN 'fail';
            END IF;
        END IF;

        -- TODO: Set number of constraints on target table:
        --       - all tiles have the same size (blocksize_x and blocksize_y)
        --       - all tiles do not overlap
        --       - all tiles appear on the regular block grid
        --       - top left block start at the top left corner of the extent
    END IF;



    -- Verify SCHEMA
    IF ( p_schema_name IS NOT NULL AND p_schema_name != '' ) THEN
        sql := 'SELECT nspname FROM pg_namespace '
            || 'WHERE text(nspname) = ' || quote_literal(p_schema_name)
            || 'LIMIT 1';
        RAISE DEBUG '%', sql;
        EXECUTE sql INTO real_schema;

        IF ( real_schema IS NULL ) THEN
            RAISE EXCEPTION 'Schema % is not a valid schemaname', quote_literal(p_schema_name);
            RETURN 'fail';
        END IF;
    END IF;

    IF ( real_schema IS NULL ) THEN
        RAISE DEBUG 'Detecting schema';
        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(p_table_name);
        RAISE DEBUG '%', sql;
        EXECUTE sql INTO real_schema;

        IF ( real_schema IS NULL ) THEN
            RAISE EXCEPTION 'Table % does not occur in the search_path',
                            quote_literal(p_table_name);
            RETURN 'fail';
        END IF;
    END IF;


    -- Add raster column to target table
    sql := 'ALTER TABLE '
        || quote_ident(real_schema) || '.' || quote_ident(p_table_name)
        || ' ADD COLUMN ' || quote_ident(p_column_name) ||  ' raster ';
    RAISE DEBUG '%', sql;
    EXECUTE sql;


    -- Delete stale record in RASTER_COLUMNS (if any)
    sql := 'DELETE FROM raster_columns '
        || ' WHERE r_table_catalog = ' || quote_literal('')
        || ' AND r_table_schema = ' || quote_literal(real_schema)
        || ' AND r_table_name = ' || quote_literal(p_table_name)
        || ' AND r_column = ' || quote_literal(p_column_name);
    RAISE DEBUG '%', sql;
    EXECUTE sql;


    -- Add record in RASTER_COLUMNS
    sql := 'INSERT INTO raster_columns '
        || '(r_table_catalog, r_table_schema, r_table_name, r_column, srid, '
        || 'pixel_types, out_db, regular_blocking, nodata_values, '
        || 'pixelsize_x, pixelsize_y, blocksize_x, blocksize_y, extent) '
        || 'VALUES ('
        || quote_literal('') || ','
        || quote_literal(real_schema) || ','
        || quote_literal(p_table_name) || ','
        || quote_literal(p_column_name) || ','
        || p_srid::text || ','
        || quote_literal(p_pixel_types::text) || ','
        || p_out_db::text || ','
        || p_regular_blocking::text || ','
        || COALESCE(quote_literal(p_nodata_values::text), 'NULL') || ','
        || COALESCE(quote_literal(p_pixelsize_x), 'NULL') || ','
        || COALESCE(quote_literal(p_pixelsize_y), 'NULL') || ','
        || COALESCE(quote_literal(p_blocksize_x), 'NULL') || ','
        || COALESCE(quote_literal(p_blocksize_y), 'NULL') || ','
        || COALESCE(quote_literal(p_extent::text), 'NULL') || ')';
    RAISE DEBUG '%', sql;
    EXECUTE sql;


    -- Add CHECK for SRID
    sql := 'ALTER TABLE '
        || quote_ident(real_schema) || '.' || quote_ident(p_table_name)
        || ' ADD CONSTRAINT '
        || quote_ident('enforce_srid_' || p_column_name)
        || ' CHECK (RT_SRID(' || quote_ident(p_column_name)
        || ') = ' || p_srid::text || ')';
    RAISE DEBUG '%', sql;
    EXECUTE sql;


    -- TODO: Add more CHECKs
    -- - Add CHECK for pixel types
    -- - Add CHECK for pixel size
    -- - Do we need CHECK for NODATA values?


    RETURN p_schema_name || '.' || p_table_name || '.' || p_column_name
        || ' srid:' || p_srid::text
        || ' pixel_types:' || p_pixel_types::text
        || ' out_db:' || p_out_db::text
        || ' regular_blocking:' || p_regular_blocking::text
        || ' nodata_values:' || COALESCE(quote_literal(p_nodata_values::text), 'NULL')
        || ' pixelsize_x:' || COALESCE(quote_literal(p_pixelsize_x), 'NULL')
        || ' pixelsize_y:' || COALESCE(quote_literal(p_pixelsize_y), 'NULL')
        || ' blocksize_x:' || COALESCE(quote_literal(p_blocksize_x), 'NULL')
        || ' blocksize_y:' || COALESCE(quote_literal(p_blocksize_y), 'NULL')
        || ' extent:' || COALESCE(ST_AsText(p_extent), 'NULL');

END;
$$
LANGUAGE 'plpgsql' _VOLATILE;


------------------------------------------------------------------------------
-- ADDRASTERCOLUMN
--      <schema> <table> <column> <srid> <pixel_types>
--      <out_db> <regular_blocking>
--      <nodata_values>
--      <pixelsize_x> <pixelsize_y>
--      <blocksize_x> <blocksize_y>
--      <extent>
-------------------------------------------------------------------------------
--
-- TODO: to be documented
--
-------------------------------------------------------------------------------

CREATEFUNCTION AddRasterColumn(varchar, varchar, varchar, integer, varchar[],
                               boolean, boolean, double precision[],
                               double precision, double precision,
                               integer, integer, geometry)
    RETURNS text AS
$$
DECLARE
    ret text;
BEGIN
    SELECT AddRasterColumn('',$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) INTO ret;
    RETURN ret;
END;
$$
LANGUAGE 'plpgsql' _STABLE;


------------------------------------------------------------------------------
-- ADDRASTERCOLUMN
--      <table> <column> <srid> <pixel_types>
--      <out_db> <regular_blocking>
--      <nodata_values>
--      <pixelsize_x> <pixelsize_y>
--      <blocksize_x> <blocksize_y>
--      <extent>
-------------------------------------------------------------------------------
--
-- TODO: to be documented
--
-------------------------------------------------------------------------------

CREATEFUNCTION AddRasterColumn(varchar, varchar, integer, varchar[],
                               boolean, boolean, double precision[],
                               double precision, double precision,
                               integer, integer, geometry)
    RETURNS text AS
$$
DECLARE
    ret text;
BEGIN
    SELECT AddRasterColumn('','',$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) INTO ret;
    RETURN ret;
END;
$$
LANGUAGE 'plpgsql' _STABLE;


-------------------------------------------------------------------------------
-- DROPRASTERCOLUMN
--      <catalog> <schema> <table> <column>
-------------------------------------------------------------------------------
--
-- TODO: to be implemented, to be documented
--
-------------------------------------------------------------------------------

CREATEFUNCTION DropRasterColumn(catalog_name varchar, schema_name varchar,
                                table_name varchar, column_name varchar)
    RETURNS text AS
$$
DECLARE

    myrec record;
    okay boolean;

BEGIN

	-- Find, check or fix schema_name
	IF ( schema_name != '' ) THEN
		okay = 'f';

		FOR myrec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP
			okay := 't';
		END LOOP;

		IF ( okay <> 't' ) THEN
			RAISE NOTICE 'Invalid schema name - using current_schema()';
			SELECT current_schema() into real_schema;
		ELSE
			real_schema = schema_name;
		END IF;
	ELSE
		SELECT current_schema() into real_schema;
	END IF;

 	-- Find out if the column is in the raster_columns table
	okay = 'f';
	FOR myrec IN SELECT * FROM raster_columns WHERE r_table_schema = text(real_schema) AND r_table_name = table_name AND r_column = column_name LOOP
		okay := 't';
	END LOOP; 
	IF (okay <> 't') THEN 
		RAISE EXCEPTION 'column not found in raster_columns table';
		RETURN 'f';
	END IF;


    RETURN schema_name || '.' || table_name || '.' || column_name ||' NOT removed.';

END;
$$
LANGUAGE 'plpgsql' _VOLATILE_STRICT; -- WITH (isstrict);

-------------------------------------------------------------------
--  END
-------------------------------------------------------------------

-- COMMIT;