Re: SQLProcedureColumns

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