~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

ALTER FUNCTION <replaceable>name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] )

<replaceable class="PARAMETER">action</replaceable> [ ... ] [ RESTRICT ]

RENAME TO <replaceable>new_name</replaceable>

OWNER TO <replaceable>new_owner</replaceable>

SET SCHEMA <replaceable>new_schema</replaceable>

<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>

CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

IMMUTABLE | STABLE | VOLATILE

[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER

COST <replaceable class="parameter">execution_cost</replaceable>

ROWS <replaceable class="parameter">result_rows</replaceable>

SET <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT }

SET <replaceable class="parameter">configuration_parameter</replaceable> FROM CURRENT

RESET <replaceable class="parameter">configuration_parameter</replaceable>

RESET ALL

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

<command>ALTER FUNCTION</command> changes the definition of a

function.

</para>

<para>

You must own the function to use <command>ALTER FUNCTION</>.

To change a function's schema, you must also have <literal>CREATE</>

privilege on the new schema.

To alter the owner, you must also be a direct or indirect member of the new

owning role, and that role must have <literal>CREATE</literal> privilege on

the function's schema. (These restrictions enforce that altering the owner

doesn't do anything you couldn't do by dropping and recreating the function.

However, a superuser can alter ownership of any function anyway.)

</para>

</refsect1>

<title>Parameters</title>

<para>

The name (optionally schema-qualified) of an existing function.

</para>

</listitem>

</varlistentry>

<term><replaceable class="parameter">argmode</replaceable></term>

<para>

The mode of an argument: <literal>IN</>, <literal>OUT</>,

<literal>INOUT</>, or <literal>VARIADIC</>.

If omitted, the default is <literal>IN</>.

Note that <command>ALTER FUNCTION</command> does not actually pay

any attention to <literal>OUT</> arguments, since only the input

arguments are needed to determine the function's identity.

So it is sufficient to list the <literal>IN</>, <literal>INOUT</>,

and <literal>VARIADIC</> arguments.

</para>

</listitem>

</varlistentry>

<term><replaceable class="parameter">argname</replaceable></term>

100

101

<para>

102

The name of an argument.

103

Note that <command>ALTER FUNCTION</command> does not actually pay

104

any attention to argument names, since only the argument data

105

types are needed to determine the function's identity.

106

</para>

107

</listitem>

108

</varlistentry>

109

110

111

<term><replaceable class="parameter">argtype</replaceable></term>

112

113

114

<para>

115

The data type(s) of the function's arguments (optionally

116

schema-qualified), if any.

117

</para>

118

</listitem>

119

</varlistentry>

120

121

122

123

124

<para>

125

The new name of the function.

126

</para>

127

</listitem>

128

</varlistentry>

129

130

131

<term><replaceable class="parameter">new_owner</replaceable></term>

132

133

<para>

134

The new owner of the function. Note that if the function is

135

marked <literal>SECURITY DEFINER</literal>, it will subsequently

136

execute as the new owner.

137

</para>

138

</listitem>

139

</varlistentry>

140

141

142

<term><replaceable class="parameter">new_schema</replaceable></term>

143

144

<para>

145

The new schema for the function.

146

</para>

147

</listitem>

148

</varlistentry>

149

150

151

<term><literal>CALLED ON NULL INPUT</literal></term>

152

<term><literal>RETURNS NULL ON NULL INPUT</literal></term>

153

<term><literal>STRICT</literal></term>

154

155

156

<para>

157

<literal>CALLED ON NULL INPUT</literal> changes the function so

158

that it will be invoked when some or all of its arguments are

159

null. <literal>RETURNS NULL ON NULL INPUT</literal> or

160

<literal>STRICT</literal> changes the function so that it is not

161

invoked if any of its arguments are null; instead, a null result

162

is assumed automatically. See <xref linkend="sql-createfunction">

163

for more information.

164

</para>

165

</listitem>

166

</varlistentry>

167

168

169

<term><literal>IMMUTABLE</literal></term>

170

<term><literal>STABLE</literal></term>

171

<term><literal>VOLATILE</literal></term>

172

173

174

<para>

175

Change the volatility of the function to the specified setting.

176

See <xref linkend="sql-createfunction"> for details.

177

</para>

178

</listitem>

179

</varlistentry>

180

181

182

<term><literal><optional> EXTERNAL </optional> SECURITY INVOKER</literal></term>

183

<term><literal><optional> EXTERNAL </optional> SECURITY DEFINER</literal></term>

184

185

186

<para>

187

Change whether the function is a security definer or not. The

188

key word <literal>EXTERNAL</literal> is ignored for SQL

189

conformance. See <xref linkend="sql-createfunction"> for more information about

190

this capability.

191

</para>

192

</listitem>

193

</varlistentry>

194

195

196

<term><literal>COST</literal> <replaceable class="parameter">execution_cost</replaceable></term>

197

198

199

<para>

200

Change the estimated execution cost of the function.

201

See <xref linkend="sql-createfunction"> for more information.

202

</para>

203

</listitem>

204

</varlistentry>

205

206

207

<term><literal>ROWS</literal> <replaceable class="parameter">result_rows</replaceable></term>

208

209

210

<para>

211

Change the estimated number of rows returned by a set-returning

212

function. See <xref linkend="sql-createfunction"> for more information.

213

</para>

214

</listitem>

215

</varlistentry>

216

217

218

<term><replaceable>configuration_parameter</replaceable></term>

219

<term><replaceable>value</replaceable></term>

220

221

<para>

222

Add or change the assignment to be made to a configuration parameter

223

when the function is called. If

224

<replaceable>value</replaceable> is <literal>DEFAULT</literal>

225

or, equivalently, <literal>RESET</literal> is used, the function-local

226

setting is removed, so that the function executes with the value

227

present in its environment. Use <literal>RESET

228

ALL</literal> to clear all function-local settings.

229

<literal>SET FROM CURRENT</> saves the session's current value of

230

the parameter as the value to be applied when the function is entered.

231

</para>

232

233

<para>

234

See <xref linkend="sql-set"> and

235

236

for more information about allowed parameter names and values.

237

</para>

238

</listitem>

239

</varlistentry>

240

241

242

<term><literal>RESTRICT</literal></term>

243

244

245

<para>

246

Ignored for conformance with the SQL standard.

247

</para>

248

</listitem>

249

</varlistentry>

250

</variablelist>

251

</refsect1>

252

253

254

<title>Examples</title>

255

256

<para>

257

To rename the function <literal>sqrt</literal> for type

258

<type>integer</type> to <literal>square_root</literal>:

259

260

ALTER FUNCTION sqrt(integer) RENAME TO square_root;

261

</programlisting>

262

</para>

263

264

<para>

265

To change the owner of the function <literal>sqrt</literal> for type

266

<type>integer</type> to <literal>joe</literal>:

267

268

ALTER FUNCTION sqrt(integer) OWNER TO joe;

269

</programlisting>

270

</para>

271

272

<para>

273

To change the schema of the function <literal>sqrt</literal> for type

274

<type>integer</type> to <literal>maths</literal>:

275

276

ALTER FUNCTION sqrt(integer) SET SCHEMA maths;

277

</programlisting>

278

</para>

279

280

<para>

281

To adjust the search path that is automatically set for a function:

282

283

ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;

284

</programlisting>

285

</para>

286

287

<para>

288

To disable automatic setting of <varname>search_path</> for a function:

289

290

ALTER FUNCTION check_password(text) RESET search_path;

291

</programlisting>

292

The function will now execute with whatever search path is used by its

293

caller.

294

</para>

295

</refsect1>

296

297

298

<title>Compatibility</title>

299

300

<para>

301

This statement is partially compatible with the <command>ALTER

302

FUNCTION</> statement in the SQL standard. The standard allows more

303

properties of a function to be modified, but does not provide the

304

ability to rename a function, make a function a security definer,

305

attach configuration parameter values to a function,

306

or change the owner, schema, or volatility of a function. The standard also

307

requires the <literal>RESTRICT</> key word, which is optional in

308

<productname>PostgreSQL</>.

309

</para>

310

</refsect1>

311

312

313

314

315

316

317

318

</simplelist>

319

</refsect1>

320

</refentry>

Older »