Skip to content

Useful or useless? Follow on post …

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.

Related posts

Post a comment — Trackback URI RSS 2.0 feed for these comments This entry (permalink) was posted on Tuesday, February 23, 2010, at 9:48 am by notnull. Filed in database, development, Example, ima, ingres and tagged , , , .
  • http://blogs.planetingres.org/grant Grant

    More of this please :)

  • http://blogs.planetingres.org/grant grantc

    Useful stuff – more please :)

  • notnull

    Have you tried it? Found some intriguing internal functions.

  • http://blogs.planetingres.org/grant grantc

    I seem to be getting:

    E_QE0400 An error was returned from a Gateway.
    Check the error log (errlog.log) for more information
    concerning the specific problem,
    (Tue Feb 23 15:10:39 2010)

    With 9.2/143 on int.lnx

  • notnull

    I just registered the tables from the previous post for ima_dbms_operators and ima_dbms_datatypes, registered the ima_dbms_fis table and just updated the query to update a.op to a.opid, but I didn't get E_QE0400 error. I've used Ingres Linux Version II 9.2.0 (int.lnx/142)NPTL.

  • http://blogs.planetingres.org/grant grantc

    strange – I'll take a look tomorrow.

  • Pingback: List of Ingres functions per release | An Ingres Blog

  • Pingback: List of Ingres functions per release | planetingres.org

  • jmbp

    I haven't tried this and already know that it will be VERY handy.
    Think of it as help for terminal monitor (returns a list of tables or other info. Come on, you remember that, its the thing we used to use before Eclipse DTP and the Ingres Database Workbench came along.)
    With apologies to the non-Java community, may I strongly suggest that it be built into the DTP in the DSE (there's almost a place holder for it.) Anyhow I'll be having a go with this at the earliest opportunity.