Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1fd8917

Browse files
committed
timestamptz
1 parentc2dc080 commit1fd8917

File tree

3 files changed

+105
-1
lines changed

3 files changed

+105
-1
lines changed

‎expected/timestamp.out

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@ INSERT INTO test_timestamp VALUES
99
( '2004-10-26 09:55:08' ),
1010
( '2004-10-26 10:55:08' )
1111
;
12+
SELECT i::timestamptz AS i INTO test_timestamptz FROM test_timestamp;
1213
SELECT i <=> '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
1314
?column? | i
1415
----------+--------------------------
@@ -43,6 +44,7 @@ SELECT i |=> '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
4344
(6 rows)
4445

4546
CREATE INDEX idx_timestamp ON test_timestamp USING rum (i);
47+
CREATE INDEX idx_timestamptz ON test_timestamptz USING rum (i);
4648
set enable_seqscan=off;
4749
explain (costs off)
4850
SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
@@ -130,3 +132,20 @@ SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i
130132
Tue Oct 26 10:55:08 2004
131133
(2 rows)
132134

135+
explain (costs off)
136+
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
137+
QUERY PLAN
138+
------------------------------------------------------------------------------------
139+
Sort
140+
Sort Key: i
141+
-> Index Scan using idx_timestamptz on test_timestamptz
142+
Index Cond: (i > 'Tue Oct 26 08:55:08 2004 PDT'::timestamp with time zone)
143+
(4 rows)
144+
145+
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
146+
i
147+
------------------------------
148+
Tue Oct 26 09:55:08 2004 PDT
149+
Tue Oct 26 10:55:08 2004 PDT
150+
(2 rows)
151+

‎rum--1.0.sql

Lines changed: 78 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -95,7 +95,6 @@ AS
9595
FUNCTION8 rum_tsquery_distance(internal,smallint,tsvector,int,internal,internal,internal,internal,internal),
9696
FUNCTION10rum_ts_join_pos(internal, internal),
9797
STORAGEtext;
98-
9998
-- timestamp ops
10099

101100
CREATEFUNCTIONtimestamp_distance(timestamp,timestamp)
@@ -209,6 +208,83 @@ AS
209208
FUNCTION7 rum_tsquery_pre_consistent(internal,smallint,tsvector,int,internal,internal,internal,internal),
210209
STORAGEtext;
211210

211+
-- timestamptz ops
212+
213+
CREATEFUNCTIONtimestamptz_distance(timestamptz,timestamptz)
214+
RETURNS float8
215+
AS'MODULE_PATHNAME','timestamp_distance'
216+
LANGUAGE C IMMUTABLE STRICT;
217+
218+
CREATE OPERATOR<=> (
219+
PROCEDURE= timestamptz_distance,
220+
LEFTARG=timestamptz,
221+
RIGHTARG=timestamptz,
222+
COMMUTATOR=<=>
223+
);
224+
225+
CREATEFUNCTIONtimestamptz_left_distance(timestamptz,timestamptz)
226+
RETURNS float8
227+
AS'MODULE_PATHNAME','timestamp_left_distance'
228+
LANGUAGE C IMMUTABLE STRICT;
229+
230+
CREATE OPERATOR<=| (
231+
PROCEDURE= timestamptz_left_distance,
232+
LEFTARG=timestamptz,
233+
RIGHTARG=timestamptz,
234+
COMMUTATOR= |=>
235+
);
236+
237+
CREATEFUNCTIONtimestamptz_right_distance(timestamptz,timestamptz)
238+
RETURNS float8
239+
AS'MODULE_PATHNAME','timestamp_right_distance'
240+
LANGUAGE C IMMUTABLE STRICT;
241+
242+
CREATE OPERATOR |=> (
243+
PROCEDURE= timestamptz_right_distance,
244+
LEFTARG=timestamptz,
245+
RIGHTARG=timestamptz,
246+
COMMUTATOR=<=|
247+
);
248+
249+
250+
CREATEOPERATOR CLASStimestamptz_ops
251+
DEFAULT FOR TYPEtimestamptz USING rum
252+
AS
253+
OPERATOR1<,
254+
OPERATOR2<=,
255+
OPERATOR3=,
256+
OPERATOR4>=,
257+
OPERATOR5>,
258+
--support
259+
FUNCTION1 timestamptz_cmp(timestamptz,timestamptz),
260+
FUNCTION2 rum_timestamp_extract_value(timestamp,internal,internal,internal,internal),
261+
FUNCTION3 rum_timestamp_extract_query(timestamp,internal,smallint,internal,internal,internal,internal),
262+
FUNCTION4 rum_timestamp_consistent(internal,smallint,timestamp,int,internal,internal,internal,internal),
263+
FUNCTION5 rum_timestamp_compare_prefix(timestamp,timestamp,smallint,internal),
264+
FUNCTION6 rum_timestamp_config(internal),
265+
-- support to timestamptz disttance in rum_tsvector_timestamptz_ops
266+
FUNCTION9 rum_timestamp_outer_distance(timestamp,timestamp,smallint),
267+
OPERATOR20<=> (timestamptz,timestamptz) FORORDER BYpg_catalog.float_ops,
268+
OPERATOR21<=| (timestamptz,timestamptz) FORORDER BYpg_catalog.float_ops,
269+
OPERATOR22|=> (timestamptz,timestamptz) FORORDER BYpg_catalog.float_ops,
270+
STORAGEtimestamptz;
271+
272+
--together
273+
274+
CREATEOPERATOR CLASSrum_tsvector_timestamptz_ops
275+
FOR TYPE tsvector USING rum
276+
AS
277+
OPERATOR1 @@ (tsvector, tsquery),
278+
--support function
279+
FUNCTION1 gin_cmp_tslexeme(text,text),
280+
FUNCTION2 rum_extract_tsvector(tsvector,internal,internal,internal,internal),
281+
FUNCTION3 rum_extract_tsquery(tsquery,internal,smallint,internal,internal,internal,internal),
282+
FUNCTION4 rum_tsquery_timestamp_consistent(internal,smallint,tsvector,int,internal,internal,internal,internal),
283+
FUNCTION5 gin_cmp_prefix(text,text,smallint,internal),
284+
FUNCTION7 rum_tsquery_pre_consistent(internal,smallint,tsvector,int,internal,internal,internal,internal),
285+
STORAGEtext;
286+
287+
-- inversed
212288

213289
CREATEFUNCTIONruminv_extract_tsquery(tsquery,internal,internal,internal,internal)
214290
RETURNS internal
@@ -240,3 +316,4 @@ AS
240316
FUNCTION4 ruminv_tsvector_consistent(internal,smallint,tsvector,int,internal,internal,internal,internal),
241317
FUNCTION6 ruminv_tsquery_config(internal),
242318
STORAGEtext;
319+

‎sql/timestamp.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,11 +12,14 @@ INSERT INTO test_timestamp VALUES
1212
('2004-10-26 10:55:08' )
1313
;
1414

15+
SELECT i::timestamptzAS i INTO test_timestamptzFROM test_timestamp;
16+
1517
SELECT i<=>'2004-10-26 06:24:08', iFROM test_timestampORDER BY1,2ASC;
1618
SELECT i<=|'2004-10-26 06:24:08', iFROM test_timestampORDER BY1,2ASC;
1719
SELECT i |=>'2004-10-26 06:24:08', iFROM test_timestampORDER BY1,2ASC;
1820

1921
CREATEINDEXidx_timestampON test_timestamp USING rum (i);
22+
CREATEINDEXidx_timestamptzON test_timestamptz USING rum (i);
2023

2124
set enable_seqscan=off;
2225

@@ -39,3 +42,8 @@ SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY
3942
explain (costs off)
4043
SELECT*FROM test_timestampWHERE i>'2004-10-26 08:55:08'::timestampORDER BY i;
4144
SELECT*FROM test_timestampWHERE i>'2004-10-26 08:55:08'::timestampORDER BY i;
45+
46+
explain (costs off)
47+
SELECT*FROM test_timestamptzWHERE i>'2004-10-26 08:55:08'::timestamptzORDER BY i;
48+
SELECT*FROM test_timestamptzWHERE i>'2004-10-26 08:55:08'::timestamptzORDER BY i;
49+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp