Re: [PoC] XMLCast (SQL/XML X025)

On 12.11.24 15:59, Robert Haas wrote:
> Those are good things to check, but we also need to consider how it
> interacts with features PostgreSQL itself already has.

I totally agree. It just didn't occur to me to check how XMLTABLE()
deals with these conversions :)

> In particular,
> I'm concerned about the special handling you seem to have for times
> and intervals.

The spec dictates that SQL types should be converted to their xsd
equivalents, e.g.

6.7 <XML cast specification>: Syntax Rules
...
15 e)
 * i)   If the type designator of SQLT is DATE, then let XT be xs:date.
 * ii)  If the type designator of SQLT is TIME WITH TIME ZONE, then let
XT be xs:time.
 * iii) If the type designator of SQLT is TIME WITHOUT TIME ZONE, then
let XT be xs:time.
 * iv)  If the type designator of SQLT is TIMESTAMP WITH TIME ZONE, then
let XT be xs:dateTime.
 * v)   If the type designator of SQLT is TIMESTAMP WITHOUT TIME ZONE,
then let XT be xs:dateTime.

> That handling might be different from what, say,
> XMLTABLE() does.

XMLTABLE() does seem to have a similar behaviour (also regarding
intervals and timestamps):

WITH j (val) AS (
 SELECT
  '<foo>
    <interval>P1Y2M3DT4H5M6S</interval>
    <timestamp>2002-05-30T09:30:10</timestamp>
    <integer>42</integer>
    <numeric>-42.73</numeric>
    <text>foo &amp; &lt;&quot;bar&quot;&gt;</text>
    <boolean>false</boolean>
  </foo>'::xml
)
SELECT a, b, c, d, e, f
FROM j,
  XMLTABLE(
    '/foo'
    PASSING val
    COLUMNS
      a interval PATH 'interval',
      b timestamp PATH 'timestamp',
      c integer PATH 'integer',
      d numeric PATH 'numeric',
      e text PATH 'text',
      f boolean PATH 'boolean');
               a               |          b          | c  |   d   
|       e       | f
-------------------------------+---------------------+----+--------+---------------+---
 1 year 2 mons 3 days 04:05:06 | 2002-05-30 09:30:10 | 42 | -42.73 | foo
& <"bar"> | f
(1 row)

> In a perfect world, we'd probably like the features
> to share code, unless there is some good reason to do otherwise. But
> at the very least we want them to work in compatible ways. For
> example, if the way you convert a date into the JSON-preferred format
> happened to use slightly different time zone handling than the way
> that some other existing feature does it, that would be extremely sad.
> Or if the existing features don't have interval handling and you do,
> perhaps we ought to add that capability to the existing features and
> then have your new feature call the same code so that it works the
> same way.
At least XMLTABLE() does handle intervals in the same way. I'll do some
research to check if maybe other related XML features follow a different
path.
> I haven't researched what the exact situation is here too
> and these examples I'm giving you here are strictly hypothetical --
> they're just the kind of thing that needs to be sorted out before we
> can think about committing anything.
+1
> There's still also the question of desirability. I take it for granted
> that you want this feature and consider it valuable, but sometimes
> people submit patches for a feature that only the submitter wants and
> nobody else cares about it (or even, other people actively dislike
> it).
I've been there a few times :)
> I am in a very poor position to assess how important this feature
> is or to what extent it complies with the relevant specification. Vik,
> who I see you copied, is probably in a much better position to
> interpret the spec than I am, and may or may not also know something
> about whether people want this. I continue to hope that we'll get some
> comments from others as well.

Thanks for taking a look at this patch. Much appreciated!

--
Jim