By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,525 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

tinterval operators and functions

P: n/a
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)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #2

P: n/a

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.