I_S views to list stored packages

In MariaDB packages (created by CREATE PACKAGE and CREATE PACKAGE BODY statements) are listed in the INFORMATION_SCHEMA.ROUTINES view with the following non-standard ROUTINE_TYPE column values:

• 'PACKAGE' for 'CREATE PACKAGE' statements
• 'PACKAGE BODY' for 'CREATE PACKAGE BODY' statements

As a PACKAGE is not a routine, it should probably be listed in some other I_S view rather than in ROUTINES.

A dedicated view INFORMATION_SCHEMA.PACKAGES

A possible option could be to have a new non-standard view INFORMATION_SCHEMA.PACKAGES.

Reusing SQL Standard view INFORMATION_SCHEMA.MODULES

Another options is to use the SQL Standard view INFORMATION_SCHEMA.MODULES, which has the following columns:

• MODULE_CATALOG
• MODULE_SCHEMA
• MODULE_NAME
• DEFAULT_CHARACTER_SET_CATALOG
• DEFAULT_CHARACTER_SET_SCHEMA
• DEFAULT_CHARACTER_SET_NAME
• DEFAULT_SCHEMA_CATALOG
• DEFAULT_SCHEMA_NAME
• MODULE_DEFINITION
• MODULE_AUTHORIZATION
• SQL_PATH
• CREATED

However, although a MariaDB PACKAGE is very close to a MODULE, it's still not a MODULE:

• an SQL Standard MODULE definition consists only of a single CREATE MODULE statement
• while a MariaDB PACKAGE definition consists of two parts: CREATE PACKAGE and CREATE PACKAGE BODY, and these two parts can even have different privileges.

Thus there is a question how to distinguish 'PACKAGE' versus 'PACKAGE BODY' (and versus 'MODULE' when we have it). A non-standard column MODULE_TYPE could solve this.

2024-02-15 Update

Peter Gulutzan wrote as a feedback to this MDEV:

Re MDEV-33385: If you do it this way, I suppose you will probably say
PACKAGE_NAME rather than MODULE_NAME and you will not need to add
PACKAGE_CATALOG or PACKAGE_SCHEMA and you will put it at the end i.e.
you will say it is the last column in the view because somewhere there
is an old-fashioned person who will say SELECT * FROM
INFORMATION_SCHEMA.ROUTINES ORDER BY integer;