1 CREATE EXTENSION smlar;
13 opc.opcmethod = am.oid AND
15 opc.opcintype = t.oid AND
16 opc.opckeytype = k.oid AND
19 opcname | typname | opcdefault
20 ----------------------+--------------+------------
21 _bytea_sml_ops | _bytea | f
22 _char_sml_ops | _char | f
23 _cidr_sml_ops | _cidr | f
24 _date_sml_ops | _date | f
25 _float4_sml_ops | _float4 | f
26 _float8_sml_ops | _float8 | f
27 _inet_sml_ops | _inet | f
28 _int2_sml_ops | _int2 | f
29 _int4_sml_ops | _int4 | f
30 _int8_sml_ops | _int8 | f
31 _interval_sml_ops | _interval | f
32 _macaddr_sml_ops | _macaddr | f
33 _numeric_sml_ops | _numeric | f
34 _oid_sml_ops | _oid | f
35 _text_sml_ops | _text | f
36 _time_sml_ops | _time | f
37 _timestamp_sml_ops | _timestamp | f
38 _timestamptz_sml_ops | _timestamptz | f
39 _timetz_sml_ops | _timetz | f
40 _varchar_sml_ops | _varchar | f
52 opc.opcmethod = am.oid AND
54 opc.opcintype = t.oid AND
55 opc.opcname ~ '_sml_ops$'
57 opcname | typname | opcdefault
58 ----------------------+--------------+------------
59 _bit_sml_ops | _bit | f
60 _bytea_sml_ops | _bytea | f
61 _char_sml_ops | _char | f
62 _cidr_sml_ops | _cidr | f
63 _date_sml_ops | _date | f
64 _float4_sml_ops | _float4 | f
65 _float8_sml_ops | _float8 | f
66 _inet_sml_ops | _inet | f
67 _int2_sml_ops | _int2 | f
68 _int4_sml_ops | _int4 | f
69 _int8_sml_ops | _int8 | f
70 _interval_sml_ops | _interval | f
71 _macaddr_sml_ops | _macaddr | f
72 _money_sml_ops | _money | f
73 _numeric_sml_ops | _numeric | f
74 _oid_sml_ops | _oid | f
75 _text_sml_ops | _text | f
76 _time_sml_ops | _time | f
77 _timestamp_sml_ops | _timestamp | f
78 _timestamptz_sml_ops | _timestamptz | f
79 _timetz_sml_ops | _timetz | f
80 _varbit_sml_ops | _varbit | f
81 _varchar_sml_ops | _varchar | f
85 trim( leading '_' from t.typname ) || '[]' AS "Array Type",
86 gin.opcname AS "GIN operator class",
87 gist.opcname AS "GiST operator class"
92 pg_catalog.pg_opclass,
95 pg_opclass.opcmethod = pg_am.oid AND
96 pg_am.amname = 'gin' AND
97 pg_opclass.opcname ~ '_sml_ops$'
103 pg_catalog.pg_opclass,
106 pg_opclass.opcmethod = pg_am.oid AND
107 pg_am.amname = 'gist' AND
108 pg_opclass.opcname ~ '_sml_ops$'
111 gist.opcname = gin.opcname AND
112 gist.opcintype = gin.opcintype
116 t.oid = COALESCE(gist.opcintype, gin.opcintype) AND
121 Array Type | GIN operator class | GiST operator class
122 ---------------+----------------------+----------------------
123 bit[] | _bit_sml_ops |
124 bytea[] | _bytea_sml_ops | _bytea_sml_ops
125 char[] | _char_sml_ops | _char_sml_ops
126 cidr[] | _cidr_sml_ops | _cidr_sml_ops
127 date[] | _date_sml_ops | _date_sml_ops
128 float4[] | _float4_sml_ops | _float4_sml_ops
129 float8[] | _float8_sml_ops | _float8_sml_ops
130 inet[] | _inet_sml_ops | _inet_sml_ops
131 int2[] | _int2_sml_ops | _int2_sml_ops
132 int4[] | _int4_sml_ops | _int4_sml_ops
133 int8[] | _int8_sml_ops | _int8_sml_ops
134 interval[] | _interval_sml_ops | _interval_sml_ops
135 macaddr[] | _macaddr_sml_ops | _macaddr_sml_ops
136 money[] | _money_sml_ops |
137 numeric[] | _numeric_sml_ops | _numeric_sml_ops
138 oid[] | _oid_sml_ops | _oid_sml_ops
139 text[] | _text_sml_ops | _text_sml_ops
140 time[] | _time_sml_ops | _time_sml_ops
141 timestamp[] | _timestamp_sml_ops | _timestamp_sml_ops
142 timestamptz[] | _timestamptz_sml_ops | _timestamptz_sml_ops
143 timetz[] | _timetz_sml_ops | _timetz_sml_ops
144 varbit[] | _varbit_sml_ops |
145 varchar[] | _varchar_sml_ops | _varchar_sml_ops
148 SELECT set_smlar_limit(0.1);
154 SET smlar.threshold = 0.6;
156 SELECT smlar('{3,2}'::int[], '{3,2,1}');
162 SELECT smlar('{2,1}'::int[], '{3,2,1}');
168 SELECT smlar('{}'::int[], '{3,2,1}');
174 SELECT smlar('{12,10}'::int[], '{3,2,1}');
180 SELECT smlar('{123}'::int[], '{}');
186 SELECT smlar('{1,4,6}'::int[], '{1,4,6}');
192 SELECT smlar('{1,4,6}'::int[], '{6,4,1}');
198 SELECT smlar('{1,4,6}'::int[], '{5,4,6}');
204 SELECT smlar('{1,4,6}'::int[], '{5,4,6}');
210 SELECT smlar('{1,2}'::int[], '{2,2,2,2,2,1}');
216 SELECT smlar('{1,2}'::int[], '{1,2,2,2,2,2}');
222 SELECT smlar('{}'::int[], '{3}');
228 SELECT smlar('{3}'::int[], '{3}');
234 SELECT smlar('{2}'::int[], '{3}');
240 SELECT smlar('{1,4,6}'::int[], '{5,4,6}', 'N.i / (N.a + N.b)' );
246 SELECT smlar('{1,4,6}'::int[], '{5,4,6}', 'N.i / sqrt(N.a * N.b)' );
252 SELECT tsvector2textarray('qwe:12 asd:45');
258 SELECT array_unique('{12,12,1,4,1,16}'::int4[]);
264 SELECT array_unique('{12,12,1,4,1,16}'::bigint[]);
270 SELECT array_unique('{12,12,1,4,1,16}'::smallint[]);
276 SELECT array_unique('{12,12,1,4,1,16}'::text[]);
282 SELECT array_unique('{12,12,1,4,1,16}'::varchar[]);
288 SELECT inarray('{12,12,1,4,1,16}'::int[], 13::int);
294 SELECT inarray('{12,12,1,4,1,16}'::int[], 12::int);
300 SELECT inarray('{12,12,1,4,1,16}'::text[], 13::text);
306 SELECT inarray('{12,12,1,4,1,16}'::text[], 12::text);
312 SELECT inarray('{12,12,1,4,1,16}'::int[], 13::int, 0.9, 0.1);
318 SELECT inarray('{12,12,1,4,1,16}'::int[], 12::int, 0.9, 0.1);
324 SELECT inarray('{12,12,1,4,1,16}'::text[], 13::text, 0.9, 0.1);
330 SELECT inarray('{12,12,1,4,1,16}'::text[], 12::text, 0.9, 0.1);
337 CREATE OR REPLACE FUNCTION epoch2timestamp(int4)
338 RETURNS timestamp AS $$
339 SELECT ('1970-01-01 00:00:00'::timestamp + ( ($1*3600*24 + $1) ::text || ' seconds' )::interval)::timestamp;
340 $$ LANGUAGE SQL RETURNS NULL ON NULL INPUT IMMUTABLE;
341 CREATE OR REPLACE FUNCTION to_tsp_array(_int4)
342 RETURNS _timestamp AS $$
345 epoch2timestamp( $1[n] )
347 generate_series( 1, array_upper( $1, 1) - array_lower( $1, 1 ) + 1 ) AS n
349 $$ LANGUAGE SQL RETURNS NULL ON NULL INPUT IMMUTABLE;
350 CREATE OR REPLACE FUNCTION array_to_col(anyarray)
351 RETURNS SETOF anyelement AS
353 SELECT $1[n] FROM generate_series( 1, array_upper( $1, 1) - array_lower( $1, 1 ) + 1 ) AS n;
354 $$ LANGUAGE SQL RETURNS NULL ON NULL INPUT IMMUTABLE;