CREATE SCHEMA opclass;

SET SESSION search_path = 'opclass';

CREATE EXTENSION orioledb;

CREATE TABLE o_test_custom_opclass (

int_val int

) USING orioledb;

CREATE FUNCTION cmp_is_valid_prepare(test_proc regproc)

RETURNS TABLE (a int, b int, ab int, ba int, abn int, ban int, valid bool)

AS $$

BEGIN

CREATE TEMP TABLE s1 ON COMMIT DROP AS

WITH s0 AS (

SELECT (vs - 1) / 6 - 5 v,

COALESCE(NULLIF((vs - 1) / 6 - 5, 0), 1) vn,

(vs - 1) % 6 r

FROM generate_series(1, 6 * 11) vs

)

SELECT v va, CASE WHEN (r = 0) THEN v - 4 * abs(vn)

WHEN (r = 1) THEN v - 2 * abs(vn)

WHEN (r = 2) THEN CASE WHEN (v > 0) THEN 0 ELSE v END

WHEN (r = 3) THEN CASE WHEN (v < 0) THEN 0 ELSE v END

WHEN (r = 4) THEN v + 7 * abs(vn)

WHEN (r = 5) THEN v + 9 * abs(vn)

ELSE 0

END vb

FROM s0;

EXECUTE 'CREATE TEMP TABLE s2 ON COMMIT DROP AS SELECT DISTINCT va, vb, ' ||

test_proc || '(va, vb) vab, ' ||

test_proc || '(vb, va) vba FROM s1';

RETURN QUERY

WITH

s3 AS (

SELECT va, vb, vab, vba,

(CASE WHEN (vab = 0) THEN 0

WHEN (vab > 0) THEN 1 ELSE -1 END) vabn,

(CASE WHEN (vba = 0) THEN 0

WHEN (vba > 0) THEN 1 ELSE -1 END) vban

FROM s2

),

s4 AS (

SELECT va, vb, vab, vba, vabn, vban,

((vabn = -vban) or (vabn = vban and vabn = 0)) AS valid

FROM s3

)

SELECT * FROM s4;

RETURN;

END

$$ LANGUAGE plpgsql;

CREATE FUNCTION cmp_is_valid(test_proc regproc)

RETURNS bool

AS $$

DECLARE

result BOOL;

BEGIN

SELECT bool_and(valid) into result

FROM cmp_is_valid_prepare(test_proc);

RETURN result;

END

$$ LANGUAGE plpgsql;

-- Letter in function names means:

-- s - sql

-- p - plpgsql

-- i - internal

-- v - volatile

-- if several letters go one next to the other in name

-- that means it calls that kind of function

CREATE FUNCTION my_int_cmp_p_i(a int, b int) RETURNS int AS $$

BEGIN

RETURN btint4cmp((a::bit(5) & X'A8'::bit(5))::int,

(b::bit(5) & X'A8'::bit(5))::int);

END

$$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION my_int_cmp_s_i(a int, b int) RETURNS int AS $$

SELECT btint4cmp((a::bit(5) & X'A8'::bit(5))::int,

(b::bit(5) & X'A8'::bit(5))::int);

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_v_i(a int, b int) RETURNS int AS $$

SELECT btint4cmp((a::bit(5) & X'A8'::bit(5))::int,

(b::bit(5) & X'A8'::bit(5))::int);

$$ LANGUAGE SQL VOLATILE;

CREATE FUNCTION my_int_cmp_s_p_i(a int, b int) RETURNS int AS $$

SELECT my_int_cmp_p_i(a, b);

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_s_s_p_i(a int, b int) RETURNS int AS $$

SELECT my_int_cmp_s_p_i(a, b);

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_s_s_s_p_i(a int, b int) RETURNS int AS $$

SELECT my_int_cmp_s_s_p_i(a, b);

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_s_s_i(a int, b int) RETURNS int AS $$

SELECT my_int_cmp_s_i(a, b);

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_s_s_s_i(a int, b int) RETURNS int AS $$

SELECT my_int_cmp_s_s_i(a, b);

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_s_s_s_s_i(a int, b int) RETURNS int AS $$

SELECT my_int_cmp_s_s_s_i(a, b);

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_s_v_i(a int, b int) RETURNS int AS $$

SELECT my_int_cmp_v_i(a, b);

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_s_s_v_i(a int, b int) RETURNS int AS $$

SELECT my_int_cmp_s_v_i(a, b);

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_s_s_s_v_i(a int, b int) RETURNS int AS $$

SELECT my_int_cmp_s_s_v_i(a, b);

$$ LANGUAGE SQL IMMUTABLE;

CREATE TABLE test_ints AS (SELECT generate_series(1, 5) val);

CREATE PROCEDURE my_test_proc(a int, b int) AS $$

SELECT a + b + 3;

$$ LANGUAGE SQL;

CREATE OPERATOR FAMILY my_op_family USING btree;

-- INVALID OPCLASSES

SELECT cmp_is_valid('my_int_cmp_p_i');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_p_i FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_p_i(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_p_i);

ERROR: function "my_int_cmp_p_i" cannot be used here

HINT: only C and SQL functions should be used as B-tree comparsion support function in operator class used for field of orioledb index

DROP OPERATOR CLASS my_op_class_p_i USING btree CASCADE;

SELECT cmp_is_valid('my_int_cmp_s_p_i');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_s_p_i FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_s_p_i(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_s_p_i);

ERROR: function "my_int_cmp_p_i" cannot be used here

HINT: only C and SQL functions should be used as B-tree comparsion support function in operator class used for field of orioledb index

CONTEXT: SQL function "my_int_cmp_s_p_i" during body validation

DROP OPERATOR CLASS my_op_class_s_p_i USING btree CASCADE;

SELECT cmp_is_valid('my_int_cmp_s_s_s_p_i');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_s_s_s_p_i FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_s_s_s_p_i(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_s_s_s_p_i);

ERROR: function "my_int_cmp_p_i" cannot be used here

HINT: only C and SQL functions should be used as B-tree comparsion support function in operator class used for field of orioledb index

CONTEXT: SQL function "my_int_cmp_s_p_i" during body validation

SQL function "my_int_cmp_s_s_p_i" during body validation

SQL function "my_int_cmp_s_s_s_p_i" during body validation

DROP OPERATOR CLASS my_op_class_s_s_s_p_i USING btree CASCADE;

SELECT cmp_is_valid('my_int_cmp_v_i');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_v_i FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_v_i(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_v_i);

ERROR: function "my_int_cmp_v_i" cannot be used here

HINT: only immutable functions should be used as B-tree comparsion support function in operator class used for field of orioledb index

DROP OPERATOR CLASS my_op_class_v_i USING btree CASCADE;

SELECT cmp_is_valid('my_int_cmp_s_v_i');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_s_v_i FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_s_v_i(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_s_v_i);

ERROR: function "my_int_cmp_v_i" cannot be used here

HINT: only immutable functions should be used as B-tree comparsion support function in operator class used for field of orioledb index

CONTEXT: SQL function "my_int_cmp_s_v_i" during body validation

DROP OPERATOR CLASS my_op_class_s_v_i USING btree CASCADE;

SELECT cmp_is_valid('my_int_cmp_s_s_s_v_i');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_s_s_s_v_i FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_s_s_s_v_i(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_s_s_s_v_i);

ERROR: function "my_int_cmp_v_i" cannot be used here

HINT: only immutable functions should be used as B-tree comparsion support function in operator class used for field of orioledb index

CONTEXT: SQL function "my_int_cmp_s_v_i" during body validation

SQL function "my_int_cmp_s_s_v_i" during body validation

SQL function "my_int_cmp_s_s_s_v_i" during body validation

DROP OPERATOR CLASS my_op_class_s_s_s_v_i USING btree CASCADE;

CREATE FUNCTION my_int_cmp_call(a int, b int) RETURNS int AS $$

CALL my_test_proc(1, 5);

SELECT (VALUES (a*a%11-b*b%11));

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_call');

ERROR: CALL is not allowed in a non-volatile function

CONTEXT: SQL function "my_int_cmp_call" during startup

SQL statement "CREATE TEMP TABLE s2 ON COMMIT DROP AS SELECT DISTINCT va, vb, my_int_cmp_call(va, vb) vab, my_int_cmp_call(vb, va) vba FROM s1"

PL/pgSQL function cmp_is_valid_prepare(regproc) line 20 at EXECUTE

SQL statement "SELECT bool_and(valid) FROM cmp_is_valid_prepare(test_proc)"

PL/pgSQL function cmp_is_valid(regproc) line 5 at SQL statement

CREATE OPERATOR CLASS my_op_class_call FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_call(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_call);

ERROR: CALL is not allowed in a non-volatile function

DROP OPERATOR CLASS my_op_class_call USING btree CASCADE;

CREATE FUNCTION my_int_cmp_delete(a int, b int) RETURNS int AS $$

DELETE FROM test_ints WHERE val = a - b RETURNING val;

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_delete');

ERROR: DELETE is not allowed in a non-volatile function

CONTEXT: SQL function "my_int_cmp_delete" during startup

SQL statement "CREATE TEMP TABLE s2 ON COMMIT DROP AS SELECT DISTINCT va, vb, my_int_cmp_delete(va, vb) vab, my_int_cmp_delete(vb, va) vba FROM s1"

PL/pgSQL function cmp_is_valid_prepare(regproc) line 20 at EXECUTE

SQL statement "SELECT bool_and(valid) FROM cmp_is_valid_prepare(test_proc)"

PL/pgSQL function cmp_is_valid(regproc) line 5 at SQL statement

CREATE OPERATOR CLASS my_op_class_delete FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_delete(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_delete);

ERROR: function "my_int_cmp_delete" cannot be used here

HINT: only queries without relation references should be used as B-tree comparsion support function in operator class used for field of orioledb index

CONTEXT: SQL function "my_int_cmp_delete" during body validation

DROP OPERATOR CLASS my_op_class_delete USING btree CASCADE;

CREATE FUNCTION my_int_cmp_update(a int, b int) RETURNS int AS $$

UPDATE test_ints SET val = val WHERE val = a - b RETURNING val;

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_update');

ERROR: UPDATE is not allowed in a non-volatile function

CONTEXT: SQL function "my_int_cmp_update" during startup

SQL statement "CREATE TEMP TABLE s2 ON COMMIT DROP AS SELECT DISTINCT va, vb, my_int_cmp_update(va, vb) vab, my_int_cmp_update(vb, va) vba FROM s1"

PL/pgSQL function cmp_is_valid_prepare(regproc) line 20 at EXECUTE

SQL statement "SELECT bool_and(valid) FROM cmp_is_valid_prepare(test_proc)"

PL/pgSQL function cmp_is_valid(regproc) line 5 at SQL statement

CREATE OPERATOR CLASS my_op_class_update FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_update(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_update);

ERROR: function "my_int_cmp_update" cannot be used here

HINT: only queries without relation references should be used as B-tree comparsion support function in operator class used for field of orioledb index

CONTEXT: SQL function "my_int_cmp_update" during body validation

DROP OPERATOR CLASS my_op_class_update USING btree CASCADE;

CREATE FUNCTION my_int_cmp_insert(a int, b int) RETURNS int AS $$

INSERT INTO test_ints VALUES (-1) RETURNING val;

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_insert');

ERROR: INSERT is not allowed in a non-volatile function

CONTEXT: SQL function "my_int_cmp_insert" during startup

SQL statement "CREATE TEMP TABLE s2 ON COMMIT DROP AS SELECT DISTINCT va, vb, my_int_cmp_insert(va, vb) vab, my_int_cmp_insert(vb, va) vba FROM s1"

PL/pgSQL function cmp_is_valid_prepare(regproc) line 20 at EXECUTE

SQL statement "SELECT bool_and(valid) FROM cmp_is_valid_prepare(test_proc)"

PL/pgSQL function cmp_is_valid(regproc) line 5 at SQL statement

CREATE OPERATOR CLASS my_op_class_insert FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_insert(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_insert);

ERROR: function "my_int_cmp_insert" cannot be used here

HINT: only queries without relation references should be used as B-tree comparsion support function in operator class used for field of orioledb index

CONTEXT: SQL function "my_int_cmp_insert" during body validation

DROP OPERATOR CLASS my_op_class_insert USING btree CASCADE;

CREATE FUNCTION my_int_cmp_select_from_table(a int, b int) RETURNS int AS $$

SELECT a%(val+6)-b%(val+6) FROM test_ints LIMIT 1;

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_select_from_table');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_select_from_table FOR TYPE int

USING btree FAMILY my_op_family

AS FUNCTION 1 my_int_cmp_select_from_table(int, int);

CREATE INDEX int_val_ix ON

o_test_custom_opclass(int_val my_op_class_select_from_table);

ERROR: function "my_int_cmp_select_from_table" cannot be used here

HINT: only queries without relation references should be used as B-tree comparsion support function in operator class used for field of orioledb index

CONTEXT: SQL function "my_int_cmp_select_from_table" during body validation

DROP OPERATOR CLASS my_op_class_select_from_table USING btree CASCADE;

-- VALID OPCLASSES

CREATE OPERATOR <^ (

LEFTARG = int4,

RIGHTARG = int4,

PROCEDURE = int4lt

);

CREATE OPERATOR =^ (

LEFTARG = int4,

RIGHTARG = int4,

PROCEDURE = int4eq,

COMMUTATOR = OPERATOR(=^)

);

SELECT cmp_is_valid('my_int_cmp_s_i');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_s_i FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_s_i(int, int);

CREATE INDEX int_val_ix_s_i ON

o_test_custom_opclass(int_val my_op_class_s_i);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

-----------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_s_i

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

2

8

10

1

3

9

4

6

5

7

(10 rows)

DROP OPERATOR CLASS my_op_class_s_i USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_s_i

TRUNCATE o_test_custom_opclass;

SELECT cmp_is_valid('my_int_cmp_s_s_s_s_i');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_s_s_s_s_i FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_s_s_s_s_i(int, int);

CREATE INDEX int_val_ix_s_s_s_s_i ON

o_test_custom_opclass(int_val my_op_class_s_s_s_s_i);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

----------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_s_s_s_s_i

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

2

8

10

1

3

9

4

6

5

7

(10 rows)

DROP OPERATOR CLASS my_op_class_s_s_s_s_i USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_s_s_s_s_i

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_simple_expression(a int, b int) RETURNS int

AS $$

SELECT (3 - a % 2) - (3 - b % 2);

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_simple_expression');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_simple_expression FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_simple_expression(int, int);

CREATE INDEX int_val_ix_simple_expression ON

o_test_custom_opclass(int_val my_op_class_simple_expression);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

------------------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_simple_expression

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

1

3

5

7

9

2

4

6

8

10

(10 rows)

DROP OPERATOR CLASS my_op_class_simple_expression USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_simple_expression

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_values(a int, b int) RETURNS int

AS $$

SELECT (VALUES (a*a%11-b*b%11));

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_values');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_values FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_values(int, int);

CREATE INDEX int_val_ix_values ON

o_test_custom_opclass(int_val my_op_class_values);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

-------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_values

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

1

10

5

6

2

9

4

7

3

8

(10 rows)

DROP OPERATOR CLASS my_op_class_values USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_values

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_generate_series(a int, b int) RETURNS int

AS $$

SELECT a % COUNT(*) - b % COUNT(*) FROM generate_series(1, 7);

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_generate_series');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_generate_series FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_generate_series(int, int);

CREATE INDEX int_val_ix_generate_series ON

o_test_custom_opclass(int_val my_op_class_generate_series);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

----------------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_generate_series

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

7

1

8

2

9

3

10

4

5

6

(10 rows)

DROP OPERATOR CLASS my_op_class_generate_series USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_generate_series

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_subselect(a int, b int) RETURNS int

AS $$

SELECT a % COUNT(*) - b % COUNT(*) FROM

(SELECT v % 5, COUNT(v) FROM generate_series(1, 30) v GROUP BY 1) s1;

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_subselect');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_subselect FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_subselect(int, int);

CREATE INDEX int_val_ix_subselect ON

o_test_custom_opclass(int_val my_op_class_subselect);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

----------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_subselect

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

5

10

1

6

2

7

3

8

4

9

(10 rows)

DROP OPERATOR CLASS my_op_class_subselect USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_subselect

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_cte(a int, b int) RETURNS int

AS $$

WITH a1 (v) AS (

SELECT COUNT(*)+1 FROM generate_series(1, 5)

)

SELECT a%v-b%v FROM a1 LIMIT 1;

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_cte');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_cte FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_cte(int, int);

CREATE INDEX int_val_ix_cte ON

o_test_custom_opclass(int_val my_op_class_cte);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

-----------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_cte

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

6

1

7

2

8

3

9

4

10

5

(10 rows)

DROP OPERATOR CLASS my_op_class_cte USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_cte

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_test_func(a int, b int) RETURNS int AS $$

SELECT LEAST(a, a)%8 - GREATEST(b, b)%8;

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION my_int_cmp_rows_func(a int, b int) RETURNS int

AS $$

SELECT * FROM ROWS FROM(my_test_func(a, b));

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_rows_func');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_rows_func FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_rows_func(int, int);

CREATE INDEX int_val_ix_rows_func ON

o_test_custom_opclass(int_val my_op_class_rows_func);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

----------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_rows_func

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

8

1

9

2

10

3

4

5

6

7

(10 rows)

DROP OPERATOR CLASS my_op_class_rows_func USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_rows_func

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_join(a int, b int) RETURNS int

AS $$

SELECT * FROM (VALUES (a * 7 % 3 - b * 7 % 3)) s1

LEFT JOIN (VALUES (3), (5)) s2 USING(column1);

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_join');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_join FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_join(int, int);

CREATE INDEX int_val_ix_join ON

o_test_custom_opclass(int_val my_op_class_join);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

-----------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_join

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

3

6

9

1

4

7

10

2

5

8

(10 rows)

DROP OPERATOR CLASS my_op_class_join USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_join

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_complex(a int, b int) RETURNS int

AS $$

SELECT pow(a, s1.r)::int%5 - pow(b, s1.r)::int%5 FROM (SELECT 2) s1(r);

WITH a1 (v) AS (

SELECT COUNT(*)+1 FROM generate_series(1, 5)

)

SELECT a%v-b%v FROM a1 LIMIT 1;

SELECT * FROM (VALUES (a * 7 % 3 - b * 7 % 3)) s1

LEFT JOIN (VALUES (3), (5)) s2 USING(column1);

SELECT (VALUES (a*a%11-b*b%11));

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_complex');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_complex FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_complex(int, int);

CREATE INDEX int_val_ix_complex ON

o_test_custom_opclass(int_val my_op_class_complex);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

--------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_complex

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

1

10

5

6

2

9

4

7

3

8

(10 rows)

DROP OPERATOR CLASS my_op_class_complex USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_complex

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_sqlbody(a int, b int) RETURNS int

LANGUAGE SQL IMMUTABLE

BEGIN ATOMIC

SELECT * FROM (VALUES (a * 7 % 3 - b * 7 % 3)) s1

LEFT JOIN (VALUES (3), (5)) s2 USING(column1);

END;

SELECT cmp_is_valid('my_int_cmp_sqlbody');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_sqlbody FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_sqlbody(int, int);

CREATE INDEX int_val_ix_sqlbody ON

o_test_custom_opclass(int_val my_op_class_sqlbody);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

--------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_sqlbody

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

3

6

9

1

4

7

10

2

5

8

(10 rows)

DROP OPERATOR CLASS my_op_class_sqlbody USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_sqlbody

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_init_plan(a int, b int) RETURNS int

AS $$

SELECT a % (select COUNT(*) from generate_series(1, 4)) -

b % (select COUNT(*) from generate_series(1, 4));

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_init_plan');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_init_plan FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_init_plan(int, int);

CREATE INDEX int_val_ix_init_plan ON

o_test_custom_opclass(int_val my_op_class_init_plan);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

----------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_init_plan

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

4

8

1

5

9

2

6

10

3

7

(10 rows)

DROP OPERATOR CLASS my_op_class_init_plan USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_init_plan

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_cast(a int, b int) RETURNS int

AS $$

SELECT substring(CAST(ABS(a) as text) from 1 for 1)::int -

substring(CAST(ABS(b) as text) from 1 for 1)::int;

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_cast');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_cast FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_cast(int, int);

CREATE INDEX int_val_ix_cast ON

o_test_custom_opclass(int_val my_op_class_cast);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

-----------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_cast

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

1

10

2

3

4

5

6

7

8

9

(10 rows)

DROP OPERATOR CLASS my_op_class_cast USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_cast

TRUNCATE o_test_custom_opclass;

CREATE FUNCTION my_int_cmp_windowagg(a int, b int) RETURNS int

AS $$

WITH s1 AS (

select exists(select COUNT(*) from generate_series(1, 4)) vv, sum(v) OVER () vvv

from generate_series(1, 4) v

)

SELECT a % COUNT(vv) - b % COUNT(vvv) FROM s1;

$$ LANGUAGE SQL IMMUTABLE;

SELECT cmp_is_valid('my_int_cmp_windowagg');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_windowagg FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_windowagg(int, int);

CREATE INDEX int_val_ix_windowagg ON

o_test_custom_opclass(int_val my_op_class_windowagg);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,10);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

QUERY PLAN

----------------------------------------------------

Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_windowagg

(2 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^);

int_val

---------

4

8

1

5

9

2

6

10

3

7

(10 rows)

DROP OPERATOR CLASS my_op_class_windowagg USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_windowagg

TRUNCATE o_test_custom_opclass;

SELECT cmp_is_valid('my_int_cmp_s_i');

cmp_is_valid

--------------

t

(1 row)

CREATE OPERATOR CLASS my_op_class_build_ix FOR TYPE int

USING btree FAMILY my_op_family

AS OPERATOR 1 <^, OPERATOR 3 =^,

FUNCTION 1 my_int_cmp_s_i(int, int);

INSERT INTO o_test_custom_opclass SELECT generate_series(1,300);

CREATE INDEX int_val_ix_build_ix ON

o_test_custom_opclass(int_val my_op_class_build_ix);

EXPLAIN (COSTS OFF) SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^) LIMIT 10;

QUERY PLAN

---------------------------------------------------------

Limit

-> Custom Scan (o_scan) on o_test_custom_opclass

Forward index only scan of: int_val_ix_build_ix

(3 rows)

SELECT * FROM o_test_custom_opclass

ORDER BY int_val USING OPERATOR(<^) LIMIT 10;

int_val

---------

2

8

10

32

34

40

42

64

66

72

(10 rows)

DROP OPERATOR CLASS my_op_class_build_ix USING btree CASCADE;

NOTICE: drop cascades to index int_val_ix_build_ix

TRUNCATE o_test_custom_opclass;

CREATE TYPE my_rec_arr AS (arr char[]);

CREATE FUNCTION my_rec_arr_to_int(my_rec_arr) RETURNS int

AS $$

SELECT ASCII($1.arr[1])-ASCII('A')+1;

$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION int_to_my_rec_arr(int) RETURNS my_rec_arr

AS $$

SELECT ROW(ARRAY[CHR(ASCII('A')+$1-1)])::my_rec_arr;

$$ LANGUAGE SQL IMMUTABLE;

CREATE CAST (my_rec_arr AS int)

WITH FUNCTION my_rec_arr_to_int;

CREATE CAST (int AS my_rec_arr)

WITH FUNCTION int_to_my_rec_arr;

CREATE TYPE my_record AS (

val_1 integer,

val_2 my_rec_arr