Chicken/egg problem with range types

Поиск
Список
Период
Сортировка
От Scott Bailey
Тема Chicken/egg problem with range types
Дата
Msg-id 50060F38.2020905@gmail.com
обсуждение исходный текст
Ответы Re: Chicken/egg problem with range types  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Rajeev rastogi
Дата:
Сообщение: Info regarding bug list for all previous releases
Следующее
От: raghu ram
Дата:
Сообщение: Re: Problem running "ALTER TABLE...", ALTER TABLE waiting