USER MANUALS

GET_ASSOCIATIONS

Description

The stored procedure GET_ASSOCIATIONS returns information about all the associations, or the associations in which a view is involved, or the associations published by a web service.

Each row represents an association.

Syntax

GET_ASSOCIATIONS (
      input_database_name : text
    , input_name : text
    , input_type : element type
)

<element type> ::=
    | 'views'
    | 'webservices'

  • input_database_name: database of the view or web service for which you want to obtain its associations.

  • input_name: name of the view or web service for which you want to obtain its associations. If null, it does not filter by name.

  • input_type (mandatory): type of the element for which you want to obtain its associations.

    If webservices, the procedure returns the associations published by a web service.

    If views, the procedure returns the association in which a view is involved.

If input_type is webservices, input_database_name and input_name are mandatory.

The procedure returns one row per associations, with these fields:

  • database_name: name of database where the element belongs to.

  • name: name of the element.

  • association_name: name of the association.

  • association_description: description of the association.

  • left_view_name: name of the view of the left endpoint of the association.

  • left_view_database: database name of the view of the left endpoint of the association.

  • left_role: role of the view of the left endpoint of the association.

  • left_multiplicity: multiplicity of the left endpoint of the association.

  • right_view_name: name of the view of the right endpoint of the association.

  • right_view_database: database name of the view of the right endpoint of the association.

  • right_role: role of the view of the right endpoint of the association.

  • right_multiplicity: multiplicity of the right endpoint of the association.

  • mappings: comma-separated list of the condition mappings of the association.

  • valid: true if it is a valid association.

  • is_left_principal: true if the left endpoint of the association is the Principal view; false otherwise.

  • left_role_precondition: role precondition of the view of the left endpoint of the association.

  • right_role_precondition: role precondition of the view of the right endpoint of the association.

  • is_referential_constraint: true if the association is a referential constraint. That is, it represents a primary key/foreign key constraint; false otherwise.

  • association_database: name of database where the association belongs to.

Privileges Required

The procedure only returns information about an association if the user has the METADATA privilege over both endpoints of the association.

Examples

Example 1

SELECT *
FROM GET_ASSOCIATIONS()
WHERE input_database_name = 'customer_report'
    AND name = 'invoice'
    AND input_type = 'views'

Obtains all the associations of the view “invoice” in the database “customer_report”.

Example 2

SELECT *
FROM GET_ASSOCIATIONS()
WHERE input_database_name = 'customer_report'
    AND input_type = 'views'

Obtains all the associations of the database “customer_report”.

Example 3

SELECT *
FROM GET_ASSOCIATIONS()
WHERE name = 'invoice'
    AND input_type = 'views'

Obtains all the associations of the views named “invoice” of any database.

Example 4

SELECT *
FROM GET_ASSOCIATIONS()
WHERE input_type = 'views'

Obtains all the associations of the server.

Example 5

SELECT *
FROM GET_ASSOCIATIONS()
WHERE input_database_name = 'customer_report'
    AND name = 'invoice'
    AND input_type = 'webservices'

Obtains all the associations published by the web service “invoice” of the database “customer_report”. It only returns information about REST web services (SOAP web services do not publish associations).

Add feedback