Re: Should PG backend know how to represent metadata?

At 10:45 17/07/00 -0400, eisentrp(at)csis(dot)gvsu(dot)edu wrote:
>On Mon, 17 Jul 2000, Philip Warner wrote:
>
>> I would have thought that the database itself should be the tool used to
>> display SQL, and if not the database, then one of the interface libraries.
>
>SQL is only one of the many formats that people might want meta data in.
>psql and pgaccess, for example, have somewhat different requirements.

I would have thought that pgaccess would still need to display table
definitions in SQL, but I have not looked at it closely enough. At the
lowest level I suspect pgaccess will always have to use direct access to
pg_* tables.

>The SQL standard defines a large set of information schema views which
>provide the database metadata in a portable fashion, from there it should
>be a relatively short distance to the format of your choice, and the
>maintainance problem across releases is decreased.

This sounds good; where are they defined in the spec?

>Of course without schema support these views would intolerably clutter the
>user name space, but I could think of a couple of ways to work around that
>for the moment.

Presumably they could be called pg_*...

>Btw., in your wheeling and dealing in pg_dump you might want to look at
>the format_type function I added, which is a step in this direction.
>(examples in psql/describe.c)

This is the sort of thing I'd like to see, but on a more general level:

format_object('table', <oid>)

would return the base definition of the table.

But I'd also like some kind of interface that allowed related items (&
their type) to be returned, which is where I came from with a 'select'
expression returning multiple rows. The functional interface could be
written as (ignoring names!):

typedef {
int relationship;
char* objType;
Oid oid
} objRef;

formatObject(const char* objType, Oid oid,
char* objDefn, int *defnLen, objRef *refs[], int *numRefs)

where formatObject is passed a type and an Oid, and returns a definition
and an array of references to other objects. Note that the fields of the
objRef structure match the input args of formatObjects.

One could also call formatObject with a null oid to get a list of objects
of the given type, and call it with a null objTye and oid to get a list of
available types to dump...perhaps I am overloading the function just a
little, but does this sound reasonable?

If desired, the 'relationship' field could be used to indicate te parent
table for an index, or the 'child' indexes for a table, but it might be
better to have a separate list for parent (one only?) and children?

Any suggestions would be appreciated....

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/