NAME_RESOLUTION_ORDER
You are looking at an older version of the documentation. The latest version is found here.
Using this option, you can enable or disable resolving of non-fully qualified names in user queries.
Resolving of non-fully qualified names applies to tables, views, and stored procedures. Hereinafter, these entities are referred to as resources or group symbols.
Enabling Non-Fully Qualified Names Resolution
There are two ways to enable the non-fully qualified names resolution, depending on whether you need to enable it for the current session or globally: by using the SET statement or by calling the SYSADMIN.setDefaultOptionValue() stored procedure.
Using SET Statement
In this case, the option becomes valid only for the current session.
The example below demonstrates enabling the names resolution with the SET statement:
SET $NAME_RESOLUTION_ORDER "test_tables_pg, test_tables";;
SET $NAME_RESOLUTION_ORDER "*";;
SET $NAME_RESOLUTION_ORDER "-";;Using Stored Procedure
In this case, the option is set globally.
The example below demonstrates enabling the names resolution by calling the stored procedure:
CALL SYSADMIN.setDefaultOptionValue("opt" => 'NAME_RESOLUTION_ORDER', "val" => '-');;
CALL SYSADMIN.setDefaultOptionValue("opt" => 'NAME_RESOLUTION_ORDER', "val" => '*');;
CALL SYSADMIN.setDefaultOptionValue("opt" => 'NAME_RESOLUTION_ORDER', "val" => 'schema1, schema2');;
CALL SYSADMIN.setDefaultOptionValue("opt" => 'NAME_RESOLUTION_ORDER', "val" => 'schema1, schema2, *');Options
The table below summarizes the values allowed for this option:
To view the full table, click the expand button in its top right corner
| Option value | Description |
|---|---|
| - | Fully qualified names are always required; default value |
| * | Look in all schemas as in the previous implementation. The main difference is that now the resolution algorithm takes into account also the user rights on the resource and two different error messages ("Group is ambiguous" or "User has no rights") can be shown depending on the number of discovered resources accessible to the user. Some examples will be shown in the next section. |
| <comma separated list of schemas> | Look in these schemas exactly in this order. If the resource is defined in more than one schema provided in the list, the name will be resolved using the first schema in the order containing the resource; if the resource is not defined in these schemas, the system will throw a "Table/View/Proc/ not found..." exception. More examples will be shown in the following section. |
| <comma separated list of schema> + "*" symbol | Look in these schemas as for the previous option. If the resource is defined in none of these schemas but it is defined multiple times in other schemas, the system will throw a "Group specified is ambiguous..." error. |
Usage Examples
The following table shows some examples (this table does not cover all possible combinations) of the system behaviour when running a query like SELECT * FROM <non-fully qualified table name>:
To view the full table, click the expand button in its top right corner
| NAME_RESOLUTION_ORDER | sch1 | Rights on sch1 | sch2 | Rights on sch2 | schX | Rights on schX | schY | Rights on schY | Result |
|---|---|---|---|---|---|---|---|---|---|
- |  | | | | | | | | The query always fails if the table name is not fully qualified |
"sch1, sch2" |  |  | | | | | | | sch2 |
"sch1, sch2" | | | | | | | | | "No permissions..." error |
"sch1, sch2" | | | | | | | | | sch1 |
"sch1, sch2" | | | | | | | | | sch2 |
"sch1, sch2" | | | | | | | | | "Table not found" error |
"sch1, sch2" | | | | | | | | | "Table not found" error |
"sch1, sch2, *" | | | | | | | | | sch1 |
"sch1, sch2, *" | | | | | | | | | sch1 |
"sch1, sch2, *" | | | | | | | | | sch2 |
"sch1, sch2, *" | | | | | | | | | sch1 |
"sch1, sch2, *" | | | | | | | | | schX |
"sch1, sch2, *" | | | | | | | | | "Group specified is ambiguous..." error |
"sch1, sch2, *" | | | | | | | | | "Table not found" error |
* | | | | | | | | | "Table not found" error |
* | | | | | | | | | "Group specified is ambiguous..." error |
* | | | | | | | | | "No permissions..." error |
* | | | | | | | | | sch1 |
Legend:
– the schema has no tables with this name
– the schema contains a table with this name
– for the resolution, it does not matter if the schema contains such a table or not
For the rights column:
– no permission
– user has permissions on the schema/table
– does not matter