Re: I do not get the point of the information_schema

Thiemo Kellner <thiemo(at)gelassene-pferde(dot)biz> writes:
> I try to implement SCD2 on trigger level and try to generated needed
> code on the fly. Therefore I need to read data about the objects in the
> database. So far so good. I know of the information_schema and the
> pg_catalog. The documentation for the information_schema states that it
> 'is defined in the SQL standard and can therefore be expected to be
> portable and remain stable'. I can think of a sensible meaning of
> portable. One cannot port it to MariaDB, can one?

If MariaDB implements information_schema according to the spec, then
yes. (If they don't, that's something to complain about to them,
not us.)

> I created following query to get
> the index columns of an index. I fear breakage when not run on the
> specific version I developed it against. Is there a more elegant way by
> the information_schema?

No, because indexes are not a part of the SQL standard. (I'm not here
to debate the wisdom of that choice; we didn't make it.) You can get
information about constraints out of the information_schema, so to the
extent that what you're interested in is the indexes underlying PK or
UNIQUE constraints, that's an option. Otherwise, you're dealing with an
implementation-specific feature and you shouldn't be surprised that the
way of finding out about it is likewise implementation-specific.

FWIW, the stuff used in your sample query has all been there for a
very long time; we don't like to break plausible client queries lightly.

regards, tom lane