Re: I do not get the point of the information_schema

On 2018-02-12 23:01:41 +0100, Thiemo Kellner wrote:
> 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?

You don't port the information schema to MariaDB. The information schema
is provided by the database.

The *use* of the information schema is portable between standard-
conforming databases, however.

You can use

    select table_schema, table_name
    from information_schema.tables
    where table_type='BASE TABLE';

on both PostgreSQL and MariaDB to get a list of tables.

(That said, it looks like both PostgreSQL and MariaDB include additional
columns beyond those mandated by the standard - you can't rely on those,
of course. And some databases like Oracle don't even have an information
schema.)

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>