Thread: [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.hetze@linux-ag.de> -----

Date: Mon, 16 Sep 2002 07:53:34 +0200
From: Sebastian Hetze <s.hetze@linux-ag.de>
To: dpage@vale-housing.co.uk
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 entryattname        name,attclass    int2,    --
SQL_PARAM_INPUT,SQL_RESUL_COL etc.atttypid    oid,    -- this should replace the oidvector thingattlen
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 __

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

-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Michael Meskes <meskes@postgresql.org> forwards:
> 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.

This seems to be predicated on the assumption that these properties
would exist if only there were table columns to store them.

In point of fact, function arguments and results do not have precision
or length, as a rule.

Addition of default values sounds like a nice idea in principle but in
practice it plays hob with the system's ability to choose a matching
function --- if I write foo(1,2), that could match not only foo(int,int)
but foo(int,int,almost-anything) if there are defaults available for
arguments of the second version of foo.  That needs very careful thought
before we buy into it.

Names for arguments would be nice, but they are not really worth a
wholesale restructuring of pg_proc; a per-argument comment facility
would serve the need as well or better.

Finally, the reason pg_proc is not normalized is that it's necessary to
allow reasonable lookup of functions by signature.  How would you
enforce "only one function named foo of arguments x,y,z" in the proposed
restructured catalog?  It's certainly not as easy as making a unique index.

> Adding a column for descriptive remarks for
> each column would not be too bad for ordinary table columns anyway...

See pg_description.
        regards, tom lane

Thanks for the answer Tom. I will forward it to Sebastian.

> function --- if I write foo(1,2), that could match not only foo(int,int)
> but foo(int,int,almost-anything) if there are defaults available for
> arguments of the second version of foo.  That needs very careful thought
> before we buy into it.

That's one of the things I talked to Sebastian about as well.

And if you really need a Default you can code this in pretty easily.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!