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 по дате отправления: