type cast with horizontal filtering

[fjf2002]

Environment

• PostgreSQL version: docker image, postgresql 13.4
• PostgREST version: docker image, postgrest 8
• Operating system: WSL2

Description of issue

On page https://postgrest.org/en/v8.0/api.html?highlight=filtering#vertical-filtering-columns I learnt that type casting the columns is possible.

I would like to type cast on horizontal filtering, i. e. I need something at the REST level that translates at the SQL level to:

SELECT *
FROM foo
WHERE foo.bar = 'myvalue'::mycustomtype

Let's put it like this: Probably horizontal filtering on any user defined type needs a string-to-user-defined-type-cast like this? Are user defined types currently locked out from horizontal filtering?

More precisely, foo.bar has type regclass, and I am failing with both

foo.bar=eq.my_table

foo.bar=eq.'my_table'::regclass

(at REST level)

[steve-chavez]

@fjf2002 You can do this with a computed column, check this previous answer here: #1651 (comment)

Let's put it like this: Probably horizontal filtering on any user defined type needs a string-to-user-defined-type-cast like this? Are user defined types currently locked out from horizontal filtering?

Hm, this feature request has come up before but the main drawback of implementing casting at the filter level is that it invalidates indexes. computed columns are safer in that regard.

[fjf2002]

@steve-chavez Thank You for the hint with computed columns. That works.

the main drawback of implementing casting at the filter level is that it invalidates indexes

Are you sure that a WHERE clause like

WHERE mycolumn = myconstant::mytype

does NOT USE the index on mycolumn? I would doubt that, because the type cast happens on the constant value, not on the column.

A test of both sql statements

explain select * from foo where bar = 25603;

explain select * from foo where bar = 'person'::regclass;

... showed me both times the same query plan, using the index on table_oid.

[wolfgangwalther]

Are you sure that a WHERE clause like

WHERE mycolumn = myconstant::mytype

does NOT USE the index on mycolumn? I would double that, because the type cast happens on the constant value, not on the column.

Please see this #1651 (comment) for the background on this.

The problem is not expected when the right cast is used, but when the wrong cast is used. This could increase the surface for DDoS attacks.

[wolfgangwalther]

As of right now, the same reasoning as in #1651 (comment) holds true. Unless we find better arguments to support a syntax like this, we're unlikely to continue with this, so I'll close this for now.

[fjf2002]

ok, thanks!