[s.hetze@linux-ag.de: SQLProcedureColumns]

I just got this. Since it should be discussed here I just forward it
(with permission of Sebastian).

Michael

----- Forwarded message from Sebastian Hetze <s(dot)hetze(at)linux-ag(dot)de> -----

Date: Mon, 16 Sep 2002 07:53:34 +0200
From: Sebastian Hetze <s(dot)hetze(at)linux-ag(dot)de>
To: dpage(at)vale-housing(dot)co(dot)uk
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.

Best regards,

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

----- End forwarded message -----

--
Michael Meskes
Michael(at)Fam-Meskes(dot)De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!