Amazon Redshift RSQL 变量 - Amazon Redshift
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

Amazon Redshift RSQL 变量

有些关键字在 RSQL 中充当变量。您可以将每个值设置为特定值,也可以重新设置值。大多数值都由 \rset 设置,它具有交互式模式和批处理模式。命令可以用小写或大写定义。

ACTIVITYCOUNT

指示受上次提交的请求影响的行数。对于数据返回请求,这是从数据库返回到 RSQL 的行数。该值为 0 或正整数。最大值为 18,446,744,073,709,551,615。

经过特殊处理的变量 ACTIVITYCOUNT 类似于变量 ROW_COUNT。但是,在命令完成时,ROW_COUNT 不会向客户端应用程序报告受影响的 SELECTCOPYUNLOAD 行数。而 ACTIVITYCOUNT 会报告相关行数。

activitycount_01.sql:

select viewname, schemaname from pg_views where schemaname = 'not_existing_schema'; \if :ACTIVITYCOUNT = 0 \remark 'views do not exist' \endif

控制台输出:

viewname | schemaname ----------+------------ (0 rows) views do not exist

ERRORLEVEL

为错误分配严重性级别。使用严重性级别来确定适当操作。如果尚未使用 ERRORLEVEL 命令,那么默认情况下,它的值为 ON

errorlevel_01.sql:

\rset errorlevel 42P01 severity 0 select * from tbl; select 1 as col; \echo exit \quit

控制台输出:

Errorlevel is on. rsql: ERROR: relation "tbl" does not exist (1 row) col 1 exit

HEADING 和 RTITLE

允许用户指定显示在报告顶部的标题。使用 RSET RTITLE 命令指定的标题会自动包含客户端电脑的当前系统日期。

rset_head _rtitle_02.rsql 内容:

\remark Starting... \rset rtitle "Marketing Department||Confidential//Third Quarter//Chicago" \rset width 70 \rset rformat on select * from rsql_test.tbl_currency order by id limit 2; \exit \remark Finishing...

控制台输出:

Starting... Rtitle is set to: &DATE||Marketing Department||Confidential//Third Quarter//Chicago (Changes will take effect after RFORMAT is switched ON) Target width is 70. Rformat is on. 09/11/20 Marketing Department Confidential Third Quarter Chicago id | bankid | name | start_date 100 | 1 | USD | 2020-09-11 10:51:39.106905 110 | 1 | EUR | 2020-09-11 10:51:39.106905 (2 rows) Press any key to continue . . .

MAXERROR

指定 RSQL 终止任务处理的最大错误严重性级别。在完成每个作业或任务后,返回代码是 RSQL 返回到客户端操作系统的整数值。返回代码的值表示作业或任务的完成状态。如果脚本包含生成错误严重性级别大于指定的错误严重性级别的 maxerror 值,RSQL 将立即退出。因此,要将 RSQL 退出的错误严重性级别设为 8,请使用 RSET MAXERROR 7

maxerror_01.sql 内容:

\rset maxerror 0 select 1 as col; \quit

控制台输出:

Maxerror is default. (1 row) col 1

RFORMAT

允许用户指定是否应用格式化命令的设置。

rset_rformat.rsql 内容:

\remark Starting... \pset border 2 \pset format wrapped \pset expanded on \pset title 'Great Title' select * from rsql_test.tbl_long where id = 500; \rset rformat select * from rsql_test.tbl_long where id = 500; \rset rformat off select * from rsql_test.tbl_long where id = 500; \rset rformat on select * from rsql_test.tbl_long where id = 500; \exit \remark Finishing...

控制台输出:

Starting... Border style is 2. (Changes will take effect after RFORMAT is switched ON) Output format is wrapped. (Changes will take effect after RFORMAT is switched ON) Expanded display is on. (Changes will take effect after RFORMAT is switched ON) Title is "Great Title". (Changes will take effect after RFORMAT is switched ON) id | long_string 500 | In general, the higher the number the more borders and lines the tables will have, but details depend on the particular format. (1 row) Rformat is on. Great Title +-[ RECORD 1 ]+---------------------------------------------------------------------------------------------------------------------- -----------+ | id | 500 | | long_string | In general, the higher the number the more borders and lines the tables will have, but details depend on the particular format. | +-------------+---------------------------------------------------------------------------------------------------------------------- -----------+ Rformat is off. id | long_string 500 | In general, the higher the number the more borders and lines the tables will have, but details depend on the particular format. (1 row) Rformat is on. Great Title +-[ RECORD 1 ]+---------------------------------------------------------------------------------------------------------------------- -----------+ | id | 500 | | long_string | In general, the higher the number the more borders and lines the tables will have, but details depend on the particular format. | +-------------+---------------------------------------------------------------------------------------------------------------------- -----------+ Press any key to continue . . .

ROW_COUNT

获取受以前查询影响的记录数。它通常用于检查结果,如以下代码片段所示:

SET result = ROW_COUNT; IF result = 0 ...

TITLEDASHES

使用此控件,用户可以指定是否要在为 SQL 语句返回的列数据的上方打印一行破折号字符。

例如:

\rset titledashes on select dept_no, emp_no, salary from rsql_test.EMPLOYEE where dept_no = 100; \rset titledashes off select dept_no, emp_no, salary from rsql_test.EMPLOYEE where dept_no = 100;

控制台输出:

dept_no emp_no salary ----------- ----------- -------------------- 100 1000346 1300.00 100 1000245 5000.00 100 1000262 2450.00 dept_no emp_no salary 100 1000346 1300.00 100 1000245 5000.00 100 1000262 2450.00

WIDTH

将输出格式设置为换行并指定报告中每行的目标宽度。如果没有参数,它将返回格式和目标宽度的当前设置。

rset_width_01.rsql 内容:

\echo Starting... \rset width \rset width 50 \rset width \quit \echo Finishing...

控制台输出:

Starting... Target width is 75. Target width is 50. Target width is 50. Press any key to continue . . .

使用参数的示例:

\echo Starting... \rset rformat on \pset format wrapped select * from rsql_test.tbl_long where id = 500; \rset width 50 select * from rsql_test.tbl_long where id = 500; \quit \echo Finishing...

控制台输出:

Starting... Rformat is on. Output format is wrapped. id | long_string 500 | In general, the higher the number the more borders and lines the ta. |.bles will have, but details depend on the particular format. (1 row) Target width is 50. id | long_string 500 | In general, the higher the number the more. |. borders and lines the tables will have, b. |.ut details depend on the particular format. |.. (1 row) Press any key to continue . . .