CATALOG_ELEMENTS (deprecated)¶
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 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'
| '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, passnull
to it.usercreator
(optional): owner of the element.When filtering by
name
,usercreator
and/ordescription
, the comparison is performed with thecontains
operator. E.g.: ifusercreator
isadm
, the procedure will return all the elements which creator contains the stringadm
.When providing a value for
initcreatedate
andendcreatedate
, orinitlastmodificationdate
andendlastmodificationdate
, the procedure returns the elements between these two intervals. I.e.: when you provide a value forinitcreatedate
andendcreatedate
, the procedure returns the elements created during these two dates.If
initcreatedate
isnull
, the procedure returns all the elements that were created beforeendcreatedate
.If
endcreatedate
isnull
, the procedure returns all the elements that were created afterinitcreatedate
. Searching byinitlastmodificationdate
andendlastmodificationdate
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”.