Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is the subtype_diff function in CREATE TYPE only can be C function?

Is the subtype_diff function in CREATE TYPE only can be C function?

From
ddme
Date:
Hi all,

I notice that the CREATE TYPE syntax can specify subtype_diff function
CREATE TYPE name AS RANGE (    SUBTYPE = subtype    [ , SUBTYPE_OPCLASS = subtype_operator_class ]    [ , COLLATION = collation ]    [ , CANONICAL = canonical_function ]    [ , SUBTYPE_DIFF = subtype_diff_function ] <————— here    [ , MULTIRANGE_TYPE_NAME = multirange_type_name ]
)
And a example is
```sql
CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
```

I notice that float8mi is a C function, and I find the call_subtype_diff() in source code that it seems only can call C function.

I want to know

1. Can the subtype_diff function in CREATE TYPE be sql or plpgsql function?
2. How to call subtype_diff function? I know it related with GiST index, I need a example on how to trigger subtype_diff function.

What’s more,  I want to learn how Postgres kernel call subtype_diff function (in which source file or function), that will help me a lot.
 

Thank you all!

Re: Is the subtype_diff function in CREATE TYPE only can be C function?

From
Ashutosh Bapat
Date:
On Wed, Jan 10, 2024 at 1:49 PM ddme <ret2ddme@qq.com> wrote:
>
> Hi all,
>
> I notice that the CREATE TYPE syntax can specify subtype_diff function
>
> CREATE TYPE name AS RANGE (
>     SUBTYPE = subtype
>     [ , SUBTYPE_OPCLASS = subtype_operator_class ]
>     [ , COLLATION = collation ]
>     [ , CANONICAL = canonical_function ]
>     [ , SUBTYPE_DIFF = subtype_diff_function ] <————— here
>     [ , MULTIRANGE_TYPE_NAME = multirange_type_name ]
> )
>
> And a example is
> ```sql
>
> CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
>
> ```
>
> I notice that float8mi is a C function, and I find the call_subtype_diff() in source code that it seems only can call
Cfunction. 

call_subtype_diff() invokes FunctionCall2Coll() which in turn invokes
the function handler for non-C functions. See
fmgr_info_cxt_security() for example. So subtype_diff can be a SQL
callable function written in any supported language.

>
> I want to know
>
> 1. Can the subtype_diff function in CREATE TYPE be sql or plpgsql function?

I think so.

> 2. How to call subtype_diff function? I know it related with GiST index, I need a example on how to trigger
subtype_difffunction. 

I am not familiar with GiST code enough to answer that question. But
looking at the places where call_subtype_diff() is called, esp. the
comments there might give you hints.
OR somebody more familiar with GiST code will give you a direct answer.

--
Best Wishes,
Ashutosh Bapat

Re: Is the subtype_diff function in CREATE TYPE only can be C function?

From
ddme
Date:


2024年1月10日 18:04，Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> 写道：

On Wed, Jan 10, 2024 at 1:49 PM ddme <ret2ddme@qq.com> wrote:

Hi all,

I notice that the CREATE TYPE syntax can specify subtype_diff function

CREATE TYPE name AS RANGE (
   SUBTYPE = subtype
   [ , SUBTYPE_OPCLASS = subtype_operator_class ]
   [ , COLLATION = collation ]
   [ , CANONICAL = canonical_function ]
   [ , SUBTYPE_DIFF = subtype_diff_function ] <————— here
   [ , MULTIRANGE_TYPE_NAME = multirange_type_name ]
)

And a example is
```sql

CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);

```

I notice that float8mi is a C function, and I find the call_subtype_diff() in source code that it seems only can call C function.

call_subtype_diff() invokes FunctionCall2Coll() which in turn invokes
the function handler for non-C functions. See
fmgr_info_cxt_security() for example. So subtype_diff can be a SQL
callable function written in any supported language.


I want to know

1. Can the subtype_diff function in CREATE TYPE be sql or plpgsql function?

I think so.

2. How to call subtype_diff function? I know it related with GiST index, I need a example on how to trigger subtype_diff function.

I am not familiar with GiST code enough to answer that question. But
looking at the places where call_subtype_diff() is called, esp. the
comments there might give you hints.
OR somebody more familiar with GiST code will give you a direct answer.

-- 
Best Wishes,
Ashutosh Bapat

Thank you!


I know that range_gist_picksplit call call_subtype_diff() but I find not call path for range_gist_picksplit.
I have try to trigger  GiST index like `CREATE INDEX … USING GIST` and using select with filter to trigger index. With the help of EXPLAIN, I get that the gist index have been triggered but subtype_diff function have not


```sql
create function float4mi(a float8, b float8) RETURNS float8 LANGUAGE SQL … …

create type float8range as range (subtype=float8, subtype_diff=float4mi);
create table float8range_test(f8r float8range);
insert into float8range_test values('[1.111,2.344]'::float8range), ('[1.111, 4.567]'::float8range);
create index my_index on float8range_test using gist(f8r);
SET enable_seqscan = off;
select * from float8range_test ORDER BY f8r;
```

Is there need more setup SQL like `CREATE OPERATOR CLASS … USING gist` to trigger?