Re: SQL:2011 Application Time Update & Delete

On 06.12.25 01:42, Paul A Jungwirth wrote:
>> transformForPortionOfClause():
>>
>> Using get_typname_and_namespace() to get the name of a range type and
>> then using that to construct a function call of the same name is
>> fragile.
>>
>> Also, it leads to unexpected error messages when the types don't
>> match:
>>
>> DELETE FROM for_portion_of_test
>>     FOR PORTION OF valid_at FROM 1 TO 2;
>> ERROR:  function pg_catalog.daterange(integer, integer) does not exist
>>
>> Well, you cover that in the tests, but I don't think it's right.
>>
>> There should be a way to go into the catalogs and get the correct
>> range constructor function for a range type using only OID references.
>> Then you can build a FuncExpr node directly and don't need to go the
>> detour of building a fake FuncCall node to transform.  (You'd still
>> need to transform the arguments separately in that case.)
> I added a function, get_range_constructor2, which I call to build a
> FuncExpr now. I got rid of get_typname_and_namespace. That said,
> looking up the constructor is tricky, because there isn't a direct oid
> lookup you can make. The rule is that it has the same name as the
> rangetype, with two args both matching the subtype. At least the rule
> is encapsulated now. And I think this function will be useful for the
> PERIODs patch, which needs similar don't-parse-your-own-node-trees
> work.

How about an alternative approach: We record the required constructor 
functions in the pg_range catalog, and then just look them up from 
there.  I have put together a quick patch for this, see attached.

Maybe we don't need to record all of them.  In particular, some of the 
multirange constructor functions seem to only exist to serve as cast 
functions.  Do you foresee down the road needing to look up any other 
ones starting from the range type?