Chicken/egg problem with range types

Поиск
Список
Период
Сортировка
От Scott Bailey
Тема Chicken/egg problem with range types
Дата
Msg-id 5005F81B.6070006@gmail.com
обсуждение исходный текст
Список 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 по дате отправления:

Предыдущее
От: Mohd Shaiza Ibrahim
Дата:
Сообщение: Can I create a trigger to add another record based on the inserted record in the same table?
Следующее
От: Sachin Srivastava
Дата:
Сообщение: Re: installation problem with postgres password