NAME

SET - 改變運行時參數

SYNOPSIS

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

DESCRIPTION 描述

SET 命令修改運行時配置參數。許多在 Section 16.4 ``Run-time Configuration'' 里面列出的運行時參數可以用 SET 在運行時設置。 （但是有些要求使用超級用戶權限來修改，而其它有些則在服務器或者會話 開始之后不能修改。）請注意 SET 只影響當前會話使用的數值。

 如果 SET 或者 SET SESSION 是在一個稍后退出的事務里發出的， 那么 SET 命令的效果將在事務回滾的之后小時。 （這個行為和PostgreSQL版本 7.3 之前的不同， 那個時候 SET 的效果在后面的錯誤之后不會回滾。） 一旦包圍它的事務提交，那么其效果將持續到事務的結束，除非被另外一個 SET 覆蓋。

SET LOCAL 的效果只持續到當前事務結束，不管是否提交。 一個特例是在一個事務里面的 SET 后面跟著一個 SET LOCAL：在事務結束之前只能看到 SET LOCAL 的數值，但是之后（如果事務提交），則是 SET 的值生效。  

PARAMETERS 選項

SESSION

 聲明這個命令只對當前會話起作用。 （如果 SESSION 或 LOCAL 都沒出現，那么這個是缺省。）

LOCAL

 聲明該命令只在當前事務中有效。在 COMMIT 或者 ROLLBACK 之后，會話級別的設置將再次生效。 請注意如果在 BEGIN 塊之外運行，那么 SET LOCAL  將表現出沒有作用，因為事務將立即結束。

name

 可設置的運行時參數的名字。可用的參數在 Section 16.4 ``Run-time Configuration'' 和下面歸檔。

value

 參數的新值。值可以聲明為字串常量，標識符，數字，或者逗號分隔的上面這些東西的列表。 DEFAULT 可以用于把這些參數設置為它們的缺省值。

 除了在 Section 16.4 ``Run-time Configuration'' 里面有文檔記載的配置參數之外， 還有幾個只能用 SET 命令設置，或者是有特殊的語法的參數：

NAMES

SET NAMES value 是 SET client_encoding TO value 的別名。

SEED

 為隨機數生成器（函數 random）設置內部的種子。 允許的值是介于 0 和 1 之間的浮點數，然后它會被乘以 231-1。

 我們也可以通過調用函數 setseed 來設置種子：

SELECT setseed(value);

TIME ZONE

SET TIME ZONE value 是 for SET timezone TO value 的一個別名。 語法 SET TIME ZONE 允許為時區設置特殊的語法。 下面是有效值的例子：

'PST8PDT'

 加州伯克利的時區。

'Portugal'

 葡萄牙時區。

'Europe/Rome'

 意大利時區。

-7

 UTC 以西 7 小時的時區（等效于 PDT）。

INTERVAL '-08:00' HOUR TO MINUTE

 UTC 以西 8 小時的時區（等效于 PST）。

LOCAL

DEFAULT

 將時區設置為你的本地時區（服務器的操作系統缺省的那個）。

 參閱 Section 8.5 ``Date/Time Types'' 獲取有關時區的更多細節。

NOTES 注意

 函數 set_config 提供了等效的功能。 參閱 Section 9.13 ``Miscellaneous Functions'' 。  

EXAMPLES 例子

 設置模式搜索路徑：

SET search_path TO my_schema, public;

把日期時間風格設置為傳統的 POSTGRES 風格， with ``day before month'' input convention:

SET datestyle TO postgres, dmy;

 把時區設置為加州伯克力， 使用雙引號保存時區聲明里大寫字符的屬性 （注意這里的日期/時間格式是 PostgreSQL）：

SET TIME ZONE 'PST8PDT';
SELECT current_timestamp AS today;

             today
-------------------------------
 2003-04-29 15:02:01.218622-07

COMPATIBILITY 兼容性

SET TIME ZONE 擴展了在 SQL 標準里定義的語法。 標準只允許有一個數字時區偏移， 而 PostgreSQL 還允許完整更靈活的時區聲明。 所有其它的 SET 特性都是 PostgreSQL 擴展。  

SEE ALSO 參見

RESET [reset(7)], SHOW [show(l)]

#p#

NAME

SET - change a run-time parameter

SYNOPSIS

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

DESCRIPTION

The SET command changes run-time configuration parameters. Many of the run-time parameters listed in the section called ``Run-time Configuration'' in the documentation can be changed on-the-fly with SET. (But some require superuser privileges to change, and others cannot be changed after server or session start.) SET only affects the value used by the current session.

If SET or SET SESSION is issued within a transaction that is later aborted, the effects of the SET command disappear when the transaction is rolled back. (This behavior represents a change from PostgreSQL versions prior to 7.3, where the effects of SET would not roll back after a later error.) Once the surrounding transaction is committed, the effects will persist until the end of the session, unless overridden by another SET.

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case is SET followed by SET LOCAL within a single transaction: the SET LOCAL value will be seen until the end of the transaction, but afterwards (if the transaction is committed) the SET value will take effect.  

PARAMETERS

SESSION

Specifies that the command takes effect for the current session. (This is the default if neither SESSION nor LOCAL appears.)

LOCAL

Specifies that the command takes effect for only the current transaction. After COMMIT or ROLLBACK, the session-level setting takes effect again. Note that SET LOCAL will appear to have no effect if it is executed outside a BEGIN block, since the transaction will end immediately.

name

Name of a settable run-time parameter. Available parameters are documented in the section called ``Run-time Configuration'' in the documentation and below.

value

New value of parameter. Values can be specified as string constants, identifiers, numbers, or comma-separated lists of these. DEFAULT can be used to specify resetting the parameter to its default value.

Besides the configuration parameters documented in the section called ``Run-time Configuration'' in the documentation, there are a few that can only be adjusted using the SET command or that have a special syntax:

NAMES

SET NAMES value is an alias for SET client_encoding TO value.

SEED

Sets the internal seed for the random number generator (the function random). Allowed values are floating-point numbers between 0 and 1, which are then multiplied by 231-1.

The seed can also be set by invoking the function setseed:

SELECT setseed(value);

TIME ZONE

SET TIME ZONE value is an alias for SET timezone TO value. The syntax SET TIME ZONE allows special syntax for the time zone specification. Here are examples of valid values (but note some are accepted only on some platforms):

'PST8PDT'

The time zone for Berkeley, California.

'Portugal'

The time zone for Portugal.

'Europe/Rome'

The time zone for Italy.

-7

The time zone 7 hours west from UTC (equivalent to PDT). Positive values are east from UTC.

INTERVAL '-08:00' HOUR TO MINUTE

The time zone 8 hours west from UTC (equivalent to PST).

LOCAL

DEFAULT

Set the time zone to your local time zone (the one that the server's operating system defaults to).

See the section called ``Date/Time Types'' in the documentation for more information about time zones.

NOTES

The function set_config provides equivalent functionality. See the section called ``Miscellaneous Functions'' in the documentation.  

EXAMPLES

Set the schema search path:

SET search_path TO my_schema, public;

Set the style of date to traditional POSTGRES with ``day before month'' input convention:

SET datestyle TO postgres, dmy;

Set the time zone for Berkeley, California, using quotes to preserve the uppercase spelling of the time zone name:

SET TIME ZONE 'PST8PDT';
SELECT current_timestamp AS today;

             today
-------------------------------
 2003-04-29 15:02:01.218622-07

COMPATIBILITY

SET TIME ZONE extends syntax defined in the SQL standard. The standard allows only numeric time zone offsets while PostgreSQL allows more flexible time-zone specifications. All other SET features are PostgreSQL extensions.  

SEE ALSO

RESET [reset(7)], SHOW [show(l)]