tinterval operators and functions - Mailing list pgsql-general

Just finding out about the tinterval type (thanks again, Alvaro!), I've
been looking to see what operators and functions are already built-in
to PostgreSQL that involve this type. I thought I'd post this in case
anyone else is interested. The results are at the bottom of the post. I
haven't looked at what they do yet: that's the next step.

That said, I haven't been successful finding which functions accept
tinterval as arguments, though this is probably because I don't know
how to handle where clauses involving arrays, or perhaps its because
the pg_proc.proargtypes attribute is type oid vector and haven't
figured out how to handle that. Any insight appreciated. (Query details
below.)

Here's the query I used to find the operators that involve tinterval:
oprleft, oprright, and oprresult just show where tinterval is found.

geos=# select oprname, 'oprleft' as oprarg
geos-# from pg_catalog.pg_operator, pg_catalog.pg_type
geos-# where typname = 'tinterval' and oprleft = pg_type.oid
geos-# union
geos-# select oprname, 'oprright' as oprarg
geos-# from pg_catalog.pg_operator, pg_catalog.pg_type
geos-# where typname = 'tinterval' and oprleft = pg_type.oid
geos-# union
geos-# select oprname, 'oprresult' as oprarg
geos-# from pg_catalog.pg_operator, pg_catalog.pg_type
geos-# where typname = 'tinterval' and oprleft = pg_type.oid
geos-# order by oprname;

Here's the query I used to find functions that return tinterval.
geos=# select proname, 'rettype' as param
geos-# from pg_catalog.pg_proc, pg_catalog.pg_type
geos-# where typname = 'tinterval' and prorettype = pg_type.oid
geos-# ;
     proname    |  param
---------------+---------
  tintervalrecv | rettype
  tinterval     | rettype
  mktinterval   | rettype
  tintervalin   | rettype
(4 rows)

I'd also like to find out which functions accept tinterval as
arguments. Here's the query I tried, including the errors that were
returned.

geos=# select proname, 'argtype' as param
geos-# from pg_catalog.pg_proc, pg_catalog.pg_type
geos-# where typname = 'tinterval' and any(proargtypes) = pg_type.oid;
ERROR:  syntax error at or near "any" at character 113
ERROR:  syntax error at or near "any" at character 113

Obviously ANY doesn't work here, but I don't know what else would.

Michael Glaesemann
grzm myrealbox com

geos=# select oprname, 'oprleft' as oprarg
geos-# from pg_catalog.pg_operator, pg_catalog.pg_type
geos-# where typname = 'tinterval' and oprleft = pg_type.oid
geos-# union
geos-# select oprname, 'oprright' as oprarg
geos-# from pg_catalog.pg_operator, pg_catalog.pg_type
geos-# where typname = 'tinterval' and oprleft = pg_type.oid
geos-# union
geos-# select oprname, 'oprresult' as oprarg
geos-# from pg_catalog.pg_operator, pg_catalog.pg_type
geos-# where typname = 'tinterval' and oprleft = pg_type.oid
geos-# order by oprname;
  oprname |  oprarg
---------+-----------
  #<      | oprresult
  #<      | oprleft
  #<      | oprright
  #<=     | oprresult
  #<=     | oprleft
  #<=     | oprright
  #<>     | oprleft
  #<>     | oprresult
  #<>     | oprright
  #=      | oprresult
  #=      | oprleft
  #=      | oprright
  #>      | oprresult
  #>      | oprleft
  #>      | oprright
  #>=     | oprresult
  #>=     | oprleft
  #>=     | oprright
  &&      | oprleft
  &&      | oprresult
  &&      | oprright
  <       | oprresult
  <       | oprleft
  <       | oprright
  <<      | oprleft
  <<      | oprresult
  <<      | oprright
  <=      | oprresult
  <=      | oprleft
  <=      | oprright
  <>      | oprleft
  <>      | oprresult
  <>      | oprright
  =       | oprresult
  =       | oprleft
  =       | oprright
  >       | oprresult
  >       | oprleft
  >       | oprright
  >=      | oprresult
  >=      | oprleft
  >=      | oprright
  ~=      | oprleft
  ~=      | oprresult
  ~=      | oprright
(45 rows)