Skip site navigation (1) Skip section navigation (2)

SQLProcedureColumns

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
Date: 2002-09-16 05:53:34
Message-ID: 20020916075334.A13301@linux-ag.de (view raw or flat)
Thread:
Lists: pgsql-odbc
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 __

pgsql-odbc by date

Next:From: Mohammad RahmanDate: 2002-09-16 06:57:34
Subject:
Previous:From: Stefan AllersDate: 2002-09-15 19:59:30
Subject: fetch auto-increment

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group