SQLProcedureColumns

Hi *,

after spending several hours to get some basic functionality for the
ODBC SQLProcedureColumns call working, I see why we don't have it yet ;-)

Currently, only a very limited range of information about arguments
and return values of PostgreSQL database functions are available
from pg_proc: the basic type and the order of the arguments.
Other important properties like precision, length, default values or
even descriptive names for the arguments are missing.
Things are getting even more complicated since pg_proc is not in the
first normalized form.

To get most out of the ODBC capabilities and because I think it will
be a useful feature for other interface and application developers
out there, I suggest to introduce a new system table, lets say pg_procattr
to hold all the relevant information.
For the beginning, I would be willing to just fill in the data by hand.
Later on I suggest to extend the CREATE FUNCTION call to manage the basic
things automaticaly.

As a first shot into the blue my suggestion for the table structure:

CREATE TABLE pg_procattr (
    attrelid    oid,    -- OID of the pg_proc entry
    attname        name,
    attclass    int2,    -- SQL_PARAM_INPUT, SQL_RESUL_COL etc.
    atttypid    oid,    -- this should replace the oidvector thing
    attlen        int2,
    atttypmod    int4,
    attnotnull    bool,
    atthasdef    bool,
    ordinal        int4,
    remarks        varchar
)

This structure is very similar to pg_attribute, so if we dont break integrity
here we could as well use the existing table and add a column for the
attribute 'direction' class. Adding a column for descriptive remarks for
each column would not be too bad for ordinary table columns anyway...

Let me know what you think before I start coding.


Best regards,

  Sebastian
--
Sebastian Hetze            Linux Information Systems AG
                           Fon +49 (0)30 72 62 38-0         Ehrenbergstr. 19
S.Hetze@Linux-AG.com       Fax +49 (0)30 72 62 38-99        D-10245 Berlin
Linux is our Business. ____________________________________ www.Linux-AG.com __