USER MANUALS

CATALOG_ELEMENTS

Note

This stored procedure is deprecated and it may be removed in the next major version of the Denodo Platform. Use the procedure GET_ELEMENTS instead of this one because “GET_ELEMENTS” can search on any database, not just in the one you are connected to and it returns the same information.

The section Features Deprecated in Denodo Platform 8.0 lists all the features that are deprecated.

Description

The stored procedure CATALOG_ELEMENTS returns a list of the elements (data sources, views, Web services, etc.) of the Virtual DataPort database you are connected to. You can filter the result by several parameters: element name, type, etc.

Syntax

CATALOG_ELEMENTS (
      name : text
    , type:
        {   NULL
          | 'Folders'
          | 'DataSources'
          | 'StoredProcedures'
          | 'Wrappers'
          | 'Views'
          | 'WebServices'
          | 'Widgets'
          | 'Associations'
          | 'JMSListeners'
        }
    , usercreator : text
    , lastusermodifier : text
    , initcreatedate : timestamptz
    , endcreatedate : timestamptz
    , initlastmodificationdate : timestamptz
    , endlastmodificationdate : timestamptz
    , description : text
)

  • If invoke this procedure with CALL and you do not want to filter by a parameter, pass null to it.

  • usercreator (optional): owner of the element.

  • When filtering by name, usercreator and/or description, the comparison is performed with the contains operator. E.g.: if usercreator is adm, the procedure will return all the elements which creator contains the string adm.

  • When providing a value for initcreatedate and endcreatedate, or initlastmodificationdate and endlastmodificationdate, the procedure returns the elements between these two intervals. I.e.: when you provide a value for initcreatedate and endcreatedate, the procedure returns the elements created during these two dates.

    If initcreatedate is null, the procedure returns all the elements that were created before endcreatedate.

    If endcreatedate is null, the procedure returns all the elements that were created after initcreatedate. Searching by initlastmodificationdate and endlastmodificationdate works in the same way.

For the input parameters of type timestamptz, you can also provide a “localdate” or a “timestamp”.

Privileges Required

No privileges are required to execute this procedure.

Examples

Example 1

SELECT resultName
    ,resultType
    ,resultSubtype
    ,resultUserCreator
    ,resultLastUserModifier
    ,resultCreateDate
    ,resultLastModificationDate
    ,resultDescription
FROM CATALOG_ELEMENTS()
WHERE type = 'WebServices'
    AND initCreateDate = trunc(current_timestamp, 'MONTH')
    AND endCreateDate = current_timestamp

The procedure returns all the Web services created during the current month.

Example 2

SELECT *
FROM CATALOG_ELEMENTS()
WHERE usercreator = 'jsmith';

Returns all the elements owned by the user “jsmith”.

Add feedback