~ubuntu-branches/debian/sid/postgresql-9.3/sid

SET [ SESSION | LOCAL ] <replaceable class="PARAMETER">configuration_parameter</replaceable> { TO | = } { <replaceable class="PARAMETER">value</replaceable> | '<replaceable class="PARAMETER">value</replaceable>' | DEFAULT }

SET [ SESSION | LOCAL ] TIME ZONE { <replaceable class="PARAMETER">timezone</replaceable> | LOCAL | DEFAULT }

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

The <command>SET</command> command changes run-time configuration

parameters. Many of the run-time parameters listed in

<xref linkend="runtime-config"> can be changed on-the-fly with

<command>SET</command>.

(But some require superuser privileges to change, and others cannot

be changed after server or session start.)

<command>SET</command> only affects the value used by the current

session.

</para>

<para>

If <command>SET</command> (or equivalently <command>SET SESSION</command>)

is issued within a transaction that is later aborted, the effects of the

<command>SET</command> command disappear when the transaction is rolled

back. Once the surrounding transaction is committed, the effects

will persist until the end of the session, unless overridden by another

<command>SET</command>.

</para>

<para>

The effects of <command>SET LOCAL</command> last only till the end of

the current transaction, whether committed or not. A special case is

<command>SET</command> followed by <command>SET LOCAL</command> within

a single transaction: the <command>SET LOCAL</command> value will be

seen until the end of the transaction, but afterwards (if the transaction

is committed) the <command>SET</command> value will take effect.

</para>

<para>

The effects of <command>SET</command> or <command>SET LOCAL</command> are

also canceled by rolling back to a savepoint that is earlier than the

command.

</para>

<para>

If <command>SET LOCAL</command> is used within a function that has a

<literal>SET</> option for the same variable (see

<xref linkend="sql-createfunction">),

the effects of the <command>SET LOCAL</command> command disappear at

function exit; that is, the value in effect when the function was called is

restored anyway. This allows <command>SET LOCAL</command> to be used for

dynamic or repeated changes of a parameter within a function, while still

having the convenience of using the <literal>SET</> option to save and

restore the caller's value. However, a regular <command>SET</> command

overrides any surrounding function's <literal>SET</> option; its effects

will persist unless rolled back.

</para>

<note>

<para>

In <productname>PostgreSQL</productname> versions 8.0 through 8.2,

the effects of a <command>SET LOCAL</command> would be canceled by

releasing an earlier savepoint, or by successful exit from a

<application>PL/pgSQL</application> exception block. This behavior

has been changed because it was deemed unintuitive.

</para>

</note>

</refsect1>

<title>Parameters</title>

<term><literal>SESSION</></term>

<para>

100

Specifies that the command takes effect for the current session.

101

(This is the default if neither <literal>SESSION</> nor

102

<literal>LOCAL</> appears.)

103

</para>

104

</listitem>

105

</varlistentry>

106

107

108

<term><literal>LOCAL</></term>

109

110

<para>

111

Specifies that the command takes effect for only the current

112

transaction. After <command>COMMIT</> or <command>ROLLBACK</>,

113

the session-level setting takes effect again. Note that

114

<command>SET LOCAL</> will appear to have no effect if it is

115

executed outside a <command>BEGIN</> block, since the

116

transaction will end immediately.

117

</para>

118

</listitem>

119

</varlistentry>

120

121

122

<term><replaceable class="PARAMETER">configuration_parameter</replaceable></term>

123

124

<para>

125

Name of a settable run-time parameter. Available parameters are

126

documented in <xref linkend="runtime-config"> and below.

127

</para>

128

</listitem>

129

</varlistentry>

130

131

132

<term><replaceable class="PARAMETER">value</replaceable></term>

133

134

<para>

135

New value of parameter. Values can be specified as string

136

constants, identifiers, numbers, or comma-separated lists of

137

these, as appropriate for the particular parameter.

138

<literal>DEFAULT</literal> can be written to specify

139

resetting the parameter to its default value (that is, whatever

140

value it would have had if no <command>SET</> had been executed

141

in the current session).

142

</para>

143

</listitem>

144

</varlistentry>

145

</variablelist>

146

147

<para>

148

Besides the configuration parameters documented in <xref

149

linkend="runtime-config">, there are a few that can only be

150

adjusted using the <command>SET</command> command or that have a

151

special syntax:

152

153

154

155

<term><literal>SCHEMA</literal></term>

156

157

<para><literal>SET SCHEMA '<replaceable>value</>'</> is an alias for

158

<literal>SET search_path TO <replaceable>value</></>. Only one

159

schema can be specified using this syntax.

160

</para>

161

</listitem>

162

</varlistentry>

163

164

165

<term><literal>NAMES</literal></term>

166

167

<para><literal>SET NAMES <replaceable>value</></> is an alias for

168

<literal>SET client_encoding TO <replaceable>value</></>.

169

</para>

170

</listitem>

171

</varlistentry>

172

173

174

175

176

<para>

177

Sets the internal seed for the random number generator (the

178

function <function>random</function>). Allowed values are

179

floating-point numbers between -1 and 1, which are then

180

multiplied by 2<superscript>31</>-1.

181

</para>

182

183

<para>

184

The seed can also be set by invoking the function

185

<function>setseed</function>:

186

187

SELECT setseed(<replaceable>value</replaceable>);

188

</programlisting></para>

189

</listitem>

190

</varlistentry>

191

192

193

194

195

<para><literal>SET TIME ZONE <replaceable>value</></> is an alias

196

for <literal>SET timezone TO <replaceable>value</></>. The

197

syntax <literal>SET TIME ZONE</literal> allows special syntax

198

for the time zone specification. Here are examples of valid

199

values:

200

201

202

203

204

205

<para>

206

The time zone for Berkeley, California.

207

</para>

208

</listitem>

209

</varlistentry>

210

211

<term><literal>'Europe/Rome'</literal></term>

212

213

<para>

214

The time zone for Italy.

215

</para>

216

</listitem>

217

</varlistentry>

218

219

220

221

<para>

222

The time zone 7 hours west from UTC (equivalent

223

to PDT). Positive values are east from UTC.

224

</para>

225

</listitem>

226

</varlistentry>

227

228

<term><literal>INTERVAL '-08:00' HOUR TO MINUTE</literal></term>

229

230

<para>

231

The time zone 8 hours west from UTC (equivalent

232

to PST).

233

</para>

234

</listitem>

235

</varlistentry>

236

237

<term><literal>LOCAL</literal></term>

238

<term><literal>DEFAULT</literal></term>

239

240

<para>

241

Set the time zone to your local time zone (that is, the

242

server's default value of <varname>timezone</>).

243

</para>

244

</listitem>

245

</varlistentry>

246

</variablelist>

247

248

See <xref linkend="datatype-timezones"> for more information

249

about time zones.

250

</para>

251

</listitem>

252

</varlistentry>

253

</variablelist>

254

</para>

255

</refsect1>

256

257

258

<title>Notes</title>

259

260

<para>

261

The function <function>set_config</function> provides equivalent

262

functionality; see <xref linkend="functions-admin">.

263

Also, it is possible to UPDATE the

264

265

system view to perform the equivalent of <command>SET</>.

266

</para>

267

</refsect1>

268

269

270

<title>Examples</title>

271

272

<para>

273

Set the schema search path:

274

275

SET search_path TO my_schema, public;

276

</programlisting>

277

</para>

278

279

<para>

280

Set the style of date to traditional

281

<productname>POSTGRES</productname> with <quote>day before month</>

282

input convention:

283

284

SET datestyle TO postgres, dmy;

285

</screen>

286

</para>

287

288

<para>

289

Set the time zone for Berkeley, California:

290

291

SET TIME ZONE 'PST8PDT';

292

</screen>

293

</para>

294

295

<para>

296

Set the time zone for Italy:

297

298

SET TIME ZONE 'Europe/Rome';

299

</screen></para>

300

</refsect1>

301

302

303

<title>Compatibility</title>

304

305

<para>

306

<literal>SET TIME ZONE</literal> extends syntax defined in the SQL

307

standard. The standard allows only numeric time zone offsets while

308

<productname>PostgreSQL</productname> allows more flexible

309

time-zone specifications. All other <literal>SET</literal>

310

features are <productname>PostgreSQL</productname> extensions.

311

</para>

312

</refsect1>

313

314

315

316

317

318

319

320

</simplelist>

321

</refsect1>

322

</refentry>

Older »