From: | Scott Bailey <artacus72(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Chicken/egg problem with range types |
Date: | 2012-07-17 23:41:15 |
Message-ID: | 5005F81B.6070006@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mohd Shaiza Ibrahim | 2012-07-18 00:59:56 | Can I create a trigger to add another record based on the inserted record in the same table? |
Previous Message | Brian McNally | 2012-07-17 19:54:01 | Problem running "ALTER TABLE...", ALTER TABLE waiting |