Re: SQLProcedureColumns

> -----Original Message-----
> From: Sebastian Hetze [mailto:s.hetze@linux-ag.de]
> Sent: 16 September 2002 06:54
> To: Dave Page
> Cc: pgsql-odbc@postgresql.org; michael.meskes@credativ.de
> Subject: 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.

I think you should post to pgsql-hackers explaining the problem and
proposed solution.

Regards, Dave.