Product Documentation

Appearance

SETTING Clause ​

As an alternative to changing the value of a configuration parameter with a SET command, you can specify a SETTING clause at the beginning of the following SQL commands:

  • SELECT
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • CREATE TABLE AS (CTAS)
  • EXPLAIN (with the commands in this list)

Note: The values you specify in the SETTING clause remain in effect for the duration of a single SQL statement. Values do not persist for the life of the session or transaction.

The SETTING clause syntax is as follows:

SETTING ( parameter = value [ ; parameter = value ] )

where value must be a constant; it cannot be an expression or a function.

If a SELECT statement contains a WITH clause (common table expression) and a SETTING clause, the SETTING clause must precede the WITH clause.

Examples ​

Assume that a table called att has the following values:

premdb=# select avg_att from att order by 1;
  avg_att   
------------
 11.1890000
 20.5940000
 20.7110000
 24.6310000
 24.6360000
 26.9720000
 ...

The following query casts these values to REAL and applies the extra_float_digits parameter, setting it to 3:

premdb=# setting (extra_float_digits=3) select avg_att::real from att order by 1;
  avg_att   
------------
 11.1890001
 20.5939999
 20.7110004
 24.6310005
 24.6359997
 26.9720001
 ...

In this case, the same query is run but the parameter is set to 1:

premdb=# setting (extra_float_digits=1) select avg_att::real from att order by 1;
 avg_att 
---------
  11.189
  20.594
  20.711
  24.631
  24.636
  26.972
  ...

The following example sets two parameters in the SETTING clause. Note that they must be separated by a semicolon:

premdb=# setting (application_name=ybsql_extra_float; extra_float_digits=-3) 
select avg_att::real from att order by 1;
 avg_att 
---------
   11.2
   20.6
   20.7
   24.6
   24.6
     27
  ... rows)

Note that the application name that was set in this example is logged in sys.query and sys.log_query:

premdb=# select * from sys.log_query where application_name='ybsql_extra_float';
-[ RECORD 1 ]----------------+----------------------------------------------------------------------------------------------------
query_id                     | 327395
session_id                   | 24780
transaction_id               | 0
plan_id                      | 8EjFRXhVcfqyE8H1P+YBUgdcO-XUleSHmHFGw4lrkI4=
state                        | done
username                     | yellowbrick
application_name             | ybsql_extra_float
database_name                | premdbtype                         | select
tags                         | [NULL]
error_code                   | 00000
error_message                | [NULL]
query_text                   | setting (application_name=ybsql_extra_float; extra_float_digits=-1) select avg_att::real from att;
...

The following example sets the same parameters for an INSERT statement:

premdb=# setting (application_name=ybsql_extra_float; extra_float_digits=-3) 
insert into att select avg_att from team;
INSERT 0 1367654

The following example sets ybd_query_tags for an EXPLAIN statement:

premdb=# explain setting (ybd_query_tags='COUNT_STAR_QUERY') 
select count(*) from newmatchstats where seasonid>12;
                                                      QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 id   rows_planned   workers   node   
  1              1    single   SELECT   
  3              1    single   AGGREGATE SIMPLE   
  4             10       all   DISTRIBUTE SINGLE   
  5             10       all   AGGREGATE SIMPLE   
  6         342000       all   EXPRESSION   
                                calculate: (NULL cast)
  9         342000       all   SCAN newmatchstats   
                                (newmatchstats.seasonid::INT4 > $1) AND scan_constraints: (newmatchstats.seasonid > $1)
 Database: premdb
 Version: 5.3.0-20210825161549
 Hostname: yb007-mgr1.sjc.yellowbrick.io
 
(13 rows)

In turn, you can see the value that was set for ybd_query_tags in the tags column of sys.log_query:

premdb=# select * from sys.log_query where tags='COUNT_STAR_QUERY';
-[ RECORD 1 ]----------------+----------------------------------------------------------------------------------------------------------
query_id                     | 337496
session_id                   | 24780
transaction_id               | 0
plan_id                      | 6lgLXILZfBFbqhwrnxWnhLMRxyx-bQfznLd9BPx8wGY=
state                        | done
username                     | yellowbrick
application_name             | ybsql
database_name                | premdb
type                         | explain
tags                         | COUNT_STAR_QUERY
error_code                   | 00000
error_message                | [NULL]
query_text                   | explain setting (ybd_query_tags='COUNT_STAR_QUERY') select count(*) from newmatchstats where seasonid>12;
...

Parent topic:SELECT