Way back in May last year I experimented with some IMA tables that showed data types and operators defined in the DBMS. Further poking around and a table for a list of the function instances is available,
drop ima_dbms_fis;
\p\g
register table ima_dbms_fis (
server varchar(64) not null not default is
'SERVER',
id integer4 not null not default is
'exp.adf.adg.fi_id',
complement integer4 not null not default is
'exp.adf.adg.fi_cmplmnt',
type varchar(20) not null not default is
'exp.adf.adg.fi_type',
flags integer4 not null not default is
'exp.adf.adg.fi_flags',
opid integer4 not null not default is
'exp.adf.adg.fi_opid',
args integer4 not null not default is
'exp.adf.adg.fi_numargs',
dtresult integer4 not null not default is
'exp.adf.adg.fi_dtresult',
dtarg1 integer4 not null not default is
'exp.adf.adg.fi_dtarg1',
dtarg2 integer4 not null not default is
'exp.adf.adg.fi_dtarg2',
dtarg3 integer4 not null not default is
'exp.adf.adg.fi_dtarg3',
dtarg4 integer4 not null not default is
'exp.adf.adg.fi_dtarg4'
)
as import from 'tables'
with dbms = IMA,
structure = sortkeyed,
key = (server);
\p\g
grant all on ima_dbms_fis to ingres;
\p\g
grant select on ima_dbms_fis to public;
\p\g
Adding this table to ima_dbms_types and ima_dbms_operators adds the ability to retrieve the list of functions and their signatures. The query makes use of an internal function iitypename which translates the numeric type code into a name.
select
a.name,
case when a.type='COMPARISON' then
trim(iitypename(b.dtresult)) + ' = ( ' +
trim(iitypename(b.dtarg1)) + ' ' +
a.name + ' ' +
trim(iitypename(b.dtarg2)) + ' )'
when a.type='OPERATOR' then
trim(iitypename(b.dtresult)) + ' = ( ' +
trim(iitypename(b.dtarg1)) + ' ' +
a.name + ' ' +
trim(iitypename(b.dtarg2)) + ' )'
else
trim(iitypename(b.dtresult)) + ' = ' +
a.name + '( ' +
case when b.dtarg1 != '' then trim(iitypename(b.dtarg1)) else '' end +
case when b.dtarg2 != '' then ', ' + trim(iitypename(b.dtarg2)) else '' end +
case when b.dtarg3 != '' then ', ' + trim(iitypename(b.dtarg3)) else '' end +
case when b.dtarg4 != '' then ', ' + trim(iitypename(b.dtarg4)) else '' end
+ ' )'
end as signature
from
ima_dbms_operators a,
ima_dbms_fis b
where
a.opid = b.opid
group by a.name, 2
order by a.name
Some sample output from this query:
|name |signature |
|soundex |char = soundex( text ) |
|soundex |char = soundex( varchar ) |
|sqrt |float = sqrt( float ) |
|squeeze |nvarchar = squeeze( nchar ) |
|squeeze |nvarchar = squeeze( nvarchar ) |
|squeeze |text = squeeze( c ) |
So in answer to my own question; yes, it is useful. I’ll be trying this on an IngresGeospatial build when I get my hands on one.
