DESIGNER_ADD_DESIGN_QUERIES

Reads and evaluates queries from an input file, and adds the queries that it accepts to the specified design. All accepted queries are assigned a weight of 1.

The following requirements apply:

• All queried tables must previously be added to the design with DESIGNER_ADD_DESIGN_TABLES.

• If the design type is incremental, the Database Designer reads only the first 100 queries in the input file, and ignores all queries beyond that number.

All accepted queries are added to the system table DESIGN_QUERIES.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

DESIGNER_ADD_DESIGN_QUERIES ( 'design-name', 'queries-file' [, return-results] )

Parameters

design-name

Name of the target design.

queries-file

Absolute path and name of the file that contains the queries to evaluate, on the local file system of the node where the session is connected, or another file system or object store that Vertica supports.

return-results

Boolean, optionally specifies whether to return results of the add operation to standard output. If set to true, Database Designer returns the following results:

• Number of accepted queries

• Number of queries referencing non-design tables

• Number of unsupported queries

• Number of illegal queries

Privileges

Non-superuser: design creator with all privileges required to execute the queries in input-file.

Errors

Database Designer returns an error in the following cases:

• The query contains illegal syntax.

• The query references:

  • External or system tables only

  • Local temporary or other non-design tables

• DELETE or UPDATE query has one or more subqueries.

• INSERT query does not include a SELECT clause.

• Database Designer cannot optimize the query.

Examples

The following example adds queries from vmart_queries.sql to the VMART_DESIGN design. This file contains nine queries. The statement includes a third argument of true, so Database Designer returns results of the add operation:

=> SELECT DESIGNER_ADD_DESIGN_QUERIES ('VMART_DESIGN', '/tmp/examples/vmart_queries.sql', 'true');
...
 DESIGNER_ADD_DESIGN_QUERIES
----------------------------------------------------
 Number of accepted queries                      =9
 Number of queries referencing non-design tables =0
 Number of unsupported queries                   =0
 Number of illegal queries                       =0
(1 row)

See also