Amazon Redshift RSQL 變數

某些關鍵字會作為 RSQL 中的變數。您可以將每個關鍵字設定為特定值，或重設該值。大多數關鍵字會使用具有互動式模式和批次模式的 \rset 來進行設定。命令可以用小寫或大寫來定義。

ACTIVITYCOUNT

指出上次提交的請求所影響的資料列數目。若為資料傳回請求，這是從資料庫傳回到 RSQL 的資料列數目。此值是 0 或正整數。最大值是 18,446,744,073,709,551,615。

特殊對待的變數 ACTIVITYCOUNT 類似於變數 ROW_COUNT。不過，ROW_COUNT 不會在 SELECT、COPY 或 UNLOAD 的命令完成時，向用戶端應用程式報告受影響的資料列計數。但 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_heading_rtitle_02.rsql content：

\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 content：

\rset maxerror 0
                        
select 1 as col;

\quit

主控台輸出：

Maxerror is default.
(1 row)

col
1

RFORMAT

可讓使用者指定是否要套用格式化命令的設定。

rset_rformat.rsql content：

\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 content：

\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 . . .