Thread: tinterval operators and functions

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)

On Friday 16 January 2004 05:14, Michael Glaesemann wrote:
>
> 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.)

If you know yourself and your enemy...

richardh=# \o tinterval.txt
richardh=# \df
richardh=# \do
richardh=# \q
[richardh@mainbox tmp]$ grep tinterval tinterval.txt


--
  Richard Huxton
  Archonet Ltd

On Jan 16, 2004, at 6:46 PM, Richard Huxton wrote:

> On Friday 16 January 2004 05:14, Michael Glaesemann wrote:
>>
>> 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.)
>
> If you know yourself and your enemy...
>
> richardh=# \o tinterval.txt
> richardh=# \df
> richardh=# \do
> richardh=# \q
> [richardh@mainbox tmp]$ grep tinterval tinterval.txt

Works for friends, too, eh? :)
Thanks, Richard!

Michael Glaesemann
grzm myrealbox com