Search Postgresql Archives

Chicken/egg problem with range types

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm trying to create a discrete range type and I'm having trouble with the canonical function.

--Create shell type
CREATE TYPE dt_range;

--Create subtype diff
CREATE OR REPLACE FUNCTION dt_subtype_diff(timestamptz, timestamptz)
RETURNS float8 AS
$$
  SELECT EXTRACT(EPOCH FROM $1 - $2);
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

-- Create the canonical function
CREATE OR REPLACE FUNCTION dt_range_canonical(dt_range)
RETURNS dt_range AS
$$
  SELECT dt_range(
    CASE WHEN lower_inc($1)
        THEN lower($1)::timestampTz(0)
        ELSE lower($1)::timestampTz(0) - INTERVAL '1s' END,
    CASE WHEN NOT upper_inc($1)
        THEN upper($1)::timestampTz(0)
        ELSE upper($1)::timestampTz(0) + INTERVAL '1s' END
  );
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

Fails with ERROR: SQL function cannot accept shell type dt_range. So I add the type and try to alter it later.

-- Create the type any way
CREATE TYPE dt_range AS RANGE (
  SUBTYPE = timestamptz,
  SUBTYPE_DIFF = dt_subtype_diff
  -- CANONICAL = dt_range_canonical -- can't use, fn doesn't exist
);

ALTER TYPE dt_range SET CANONICAL = dt_range_canonical;

This doesn't work either. I'm stuck.

Scott Bailey

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux