| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/create_function.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-createfunction"> |
| 7 |
<indexterm zone="sql-createfunction"> |
| 8 |
<primary>CREATE FUNCTION</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>CREATE FUNCTION</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>CREATE FUNCTION</refname> |
| 19 |
<refpurpose>define a new function</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
CREATE [ OR REPLACE ] FUNCTION |
| 25 |
<replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">default_expr</replaceable> ] [, ...] ] ) |
| 26 |
[ RETURNS <replaceable class="parameter">rettype</replaceable> |
| 27 |
| RETURNS TABLE ( <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">column_type</replaceable> [, ...] ) ] |
| 28 |
{ LANGUAGE <replaceable class="parameter">lang_name</replaceable> |
| 29 |
| TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] |
| 30 |
| WINDOW |
| 31 |
| { IMMUTABLE | STABLE | VOLATILE } |
| 32 |
| [ NOT ] LEAKPROOF |
| 33 |
| { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } |
| 34 |
| { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER } |
| 35 |
| PARALLEL { UNSAFE | RESTRICTED | SAFE } |
| 36 |
| COST <replaceable class="parameter">execution_cost</replaceable> |
| 37 |
| ROWS <replaceable class="parameter">result_rows</replaceable> |
| 38 |
| SUPPORT <replaceable class="parameter">support_function</replaceable> |
| 39 |
| SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT } |
| 40 |
| AS '<replaceable class="parameter">definition</replaceable>' |
| 41 |
| AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>' |
| 42 |
| <replaceable class="parameter">sql_body</replaceable> |
| 43 |
} ... |
| 44 |
</synopsis> |
| 45 |
</refsynopsisdiv> |
| 46 |
|
| 47 |
<refsect1 id="sql-createfunction-description"> |
| 48 |
<title>Description</title> |
| 49 |
|
| 50 |
<para> |
| 51 |
<command>CREATE FUNCTION</command> defines a new function. |
| 52 |
<command>CREATE OR REPLACE FUNCTION</command> will either create a |
| 53 |
new function, or replace an existing definition. |
| 54 |
To be able to define a function, the user must have the |
| 55 |
<literal>USAGE</literal> privilege on the language. |
| 56 |
</para> |
| 57 |
|
| 58 |
<para> |
| 59 |
If a schema name is included, then the function is created in the |
| 60 |
specified schema. Otherwise it is created in the current schema. |
| 61 |
The name of the new function must not match any existing function or procedure |
| 62 |
with the same input argument types in the same schema. However, |
| 63 |
functions and procedures of different argument types can share a name (this is |
| 64 |
called <firstterm>overloading</firstterm>). |
| 65 |
</para> |
| 66 |
|
| 67 |
<para> |
| 68 |
To replace the current definition of an existing function, use |
| 69 |
<command>CREATE OR REPLACE FUNCTION</command>. It is not possible |
| 70 |
to change the name or argument types of a function this way (if you |
| 71 |
tried, you would actually be creating a new, distinct function). |
| 72 |
Also, <command>CREATE OR REPLACE FUNCTION</command> will not let |
| 73 |
you change the return type of an existing function. To do that, |
| 74 |
you must drop and recreate the function. (When using <literal>OUT</literal> |
| 75 |
parameters, that means you cannot change the types of any |
| 76 |
<literal>OUT</literal> parameters except by dropping the function.) |
| 77 |
</para> |
| 78 |
|
| 79 |
<para> |
| 80 |
When <command>CREATE OR REPLACE FUNCTION</command> is used to replace an |
| 81 |
existing function, the ownership and permissions of the function |
| 82 |
do not change. All other function properties are assigned the |
| 83 |
values specified or implied in the command. You must own the function |
| 84 |
to replace it (this includes being a member of the owning role). |
| 85 |
</para> |
| 86 |
|
| 87 |
<para> |
| 88 |
If you drop and then recreate a function, the new function is not |
| 89 |
the same entity as the old; you will have to drop existing rules, views, |
| 90 |
triggers, etc. that refer to the old function. Use |
| 91 |
<command>CREATE OR REPLACE FUNCTION</command> to change a function |
| 92 |
definition without breaking objects that refer to the function. |
| 93 |
Also, <command>ALTER FUNCTION</command> can be used to change most of the |
| 94 |
auxiliary properties of an existing function. |
| 95 |
</para> |
| 96 |
|
| 97 |
<para> |
| 98 |
The user that creates the function becomes the owner of the function. |
| 99 |
</para> |
| 100 |
|
| 101 |
<para> |
| 102 |
To be able to create a function, you must have <literal>USAGE</literal> |
| 103 |
privilege on the argument types and the return type. |
| 104 |
</para> |
| 105 |
|
| 106 |
<para> |
| 107 |
Refer to <xref linkend="xfunc"/> for further information on writing |
| 108 |
functions. |
| 109 |
</para> |
| 110 |
</refsect1> |
| 111 |
|
| 112 |
<refsect1> |
| 113 |
<title>Parameters</title> |
| 114 |
|
| 115 |
<variablelist> |
| 116 |
|
| 117 |
<varlistentry> |
| 118 |
<term><replaceable class="parameter">name</replaceable></term> |
| 119 |
|
| 120 |
<listitem> |
| 121 |
<para> |
| 122 |
The name (optionally schema-qualified) of the function to create. |
| 123 |
</para> |
| 124 |
</listitem> |
| 125 |
</varlistentry> |
| 126 |
|
| 127 |
<varlistentry> |
| 128 |
<term><replaceable class="parameter">argmode</replaceable></term> |
| 129 |
|
| 130 |
<listitem> |
| 131 |
<para> |
| 132 |
The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>, |
| 133 |
<literal>INOUT</literal>, or <literal>VARIADIC</literal>. |
| 134 |
If omitted, the default is <literal>IN</literal>. |
| 135 |
Only <literal>OUT</literal> arguments can follow a <literal>VARIADIC</literal> one. |
| 136 |
Also, <literal>OUT</literal> and <literal>INOUT</literal> arguments cannot be used |
| 137 |
together with the <literal>RETURNS TABLE</literal> notation. |
| 138 |
</para> |
| 139 |
</listitem> |
| 140 |
</varlistentry> |
| 141 |
|
| 142 |
<varlistentry> |
| 143 |
<term><replaceable class="parameter">argname</replaceable></term> |
| 144 |
|
| 145 |
<listitem> |
| 146 |
<para> |
| 147 |
The name of an argument. Some languages (including SQL and PL/pgSQL) |
| 148 |
let you use the name in the function body. For other languages the |
| 149 |
name of an input argument is just extra documentation, so far as |
| 150 |
the function itself is concerned; but you can use input argument names |
| 151 |
when calling a function to improve readability (see <xref |
| 152 |
linkend="sql-syntax-calling-funcs"/>). In any case, the name |
| 153 |
of an output argument is significant, because it defines the column |
| 154 |
name in the result row type. (If you omit the name for an output |
| 155 |
argument, the system will choose a default column name.) |
| 156 |
</para> |
| 157 |
</listitem> |
| 158 |
</varlistentry> |
| 159 |
|
| 160 |
<varlistentry> |
| 161 |
<term><replaceable class="parameter">argtype</replaceable></term> |
| 162 |
|
| 163 |
<listitem> |
| 164 |
<para> |
| 165 |
The data type(s) of the function's arguments (optionally |
| 166 |
schema-qualified), if any. The argument types can be base, composite, |
| 167 |
or domain types, or can reference the type of a table column. |
| 168 |
</para> |
| 169 |
<para> |
| 170 |
Depending on the implementation language it might also be allowed |
| 171 |
to specify <quote>pseudo-types</quote> such as <type>cstring</type>. |
| 172 |
Pseudo-types indicate that the actual argument type is either |
| 173 |
incompletely specified, or outside the set of ordinary SQL data types. |
| 174 |
</para> |
| 175 |
<para> |
| 176 |
The type of a column is referenced by writing |
| 177 |
<literal><replaceable |
| 178 |
class="parameter">table_name</replaceable>.<replaceable |
| 179 |
class="parameter">column_name</replaceable>%TYPE</literal>. |
| 180 |
Using this feature can sometimes help make a function independent of |
| 181 |
changes to the definition of a table. |
| 182 |
</para> |
| 183 |
</listitem> |
| 184 |
</varlistentry> |
| 185 |
|
| 186 |
<varlistentry> |
| 187 |
<term><replaceable class="parameter">default_expr</replaceable></term> |
| 188 |
|
| 189 |
<listitem> |
| 190 |
<para> |
| 191 |
An expression to be used as default value if the parameter is |
| 192 |
not specified. The expression has to be coercible to the |
| 193 |
argument type of the parameter. |
| 194 |
Only input (including <literal>INOUT</literal>) parameters can have a default |
| 195 |
value. All input parameters following a |
| 196 |
parameter with a default value must have default values as well. |
| 197 |
</para> |
| 198 |
</listitem> |
| 199 |
</varlistentry> |
| 200 |
|
| 201 |
<varlistentry> |
| 202 |
<term><replaceable class="parameter">rettype</replaceable></term> |
| 203 |
|
| 204 |
<listitem> |
| 205 |
<para> |
| 206 |
The return data type (optionally schema-qualified). The return type |
| 207 |
can be a base, composite, or domain type, |
| 208 |
or can reference the type of a table column. |
| 209 |
Depending on the implementation language it might also be allowed |
| 210 |
to specify <quote>pseudo-types</quote> such as <type>cstring</type>. |
| 211 |
If the function is not supposed to return a value, specify |
| 212 |
<type>void</type> as the return type. |
| 213 |
</para> |
| 214 |
<para> |
| 215 |
When there are <literal>OUT</literal> or <literal>INOUT</literal> parameters, |
| 216 |
the <literal>RETURNS</literal> clause can be omitted. If present, it |
| 217 |
must agree with the result type implied by the output parameters: |
| 218 |
<literal>RECORD</literal> if there are multiple output parameters, or |
| 219 |
the same type as the single output parameter. |
| 220 |
</para> |
| 221 |
<para> |
| 222 |
The <literal>SETOF</literal> |
| 223 |
modifier indicates that the function will return a set of |
| 224 |
items, rather than a single item. |
| 225 |
</para> |
| 226 |
<para> |
| 227 |
The type of a column is referenced by writing |
| 228 |
<literal><replaceable |
| 229 |
class="parameter">table_name</replaceable>.<replaceable |
| 230 |
class="parameter">column_name</replaceable>%TYPE</literal>. |
| 231 |
</para> |
| 232 |
</listitem> |
| 233 |
</varlistentry> |
| 234 |
|
| 235 |
<varlistentry> |
| 236 |
<term><replaceable class="parameter">column_name</replaceable></term> |
| 237 |
|
| 238 |
<listitem> |
| 239 |
<para> |
| 240 |
The name of an output column in the <literal>RETURNS TABLE</literal> |
| 241 |
syntax. This is effectively another way of declaring a named |
| 242 |
<literal>OUT</literal> parameter, except that <literal>RETURNS TABLE</literal> |
| 243 |
also implies <literal>RETURNS SETOF</literal>. |
| 244 |
</para> |
| 245 |
</listitem> |
| 246 |
</varlistentry> |
| 247 |
|
| 248 |
<varlistentry> |
| 249 |
<term><replaceable class="parameter">column_type</replaceable></term> |
| 250 |
|
| 251 |
<listitem> |
| 252 |
<para> |
| 253 |
The data type of an output column in the <literal>RETURNS TABLE</literal> |
| 254 |
syntax. |
| 255 |
</para> |
| 256 |
</listitem> |
| 257 |
</varlistentry> |
| 258 |
|
| 259 |
<varlistentry> |
| 260 |
<term><replaceable class="parameter">lang_name</replaceable></term> |
| 261 |
|
| 262 |
<listitem> |
| 263 |
<para> |
| 264 |
The name of the language that the function is implemented in. |
| 265 |
It can be <literal>sql</literal>, <literal>c</literal>, |
| 266 |
<literal>internal</literal>, or the name of a user-defined |
| 267 |
procedural language, e.g., <literal>plpgsql</literal>. The default is |
| 268 |
<literal>sql</literal> if <replaceable |
| 269 |
class="parameter">sql_body</replaceable> is specified. Enclosing the |
| 270 |
name in single quotes is deprecated and requires matching case. |
| 271 |
</para> |
| 272 |
</listitem> |
| 273 |
</varlistentry> |
| 274 |
|
| 275 |
<varlistentry> |
| 276 |
<term><literal>TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] }</literal></term> |
| 277 |
|
| 278 |
<listitem> |
| 279 |
<para> |
| 280 |
Lists which transforms a call to the function should apply. Transforms |
| 281 |
convert between SQL types and language-specific data types; |
| 282 |
see <xref linkend="sql-createtransform"/>. Procedural language |
| 283 |
implementations usually have hardcoded knowledge of the built-in types, |
| 284 |
so those don't need to be listed here. If a procedural language |
| 285 |
implementation does not know how to handle a type and no transform is |
| 286 |
supplied, it will fall back to a default behavior for converting data |
| 287 |
types, but this depends on the implementation. |
| 288 |
</para> |
| 289 |
</listitem> |
| 290 |
</varlistentry> |
| 291 |
|
| 292 |
<varlistentry> |
| 293 |
<term><literal>WINDOW</literal></term> |
| 294 |
|
| 295 |
<listitem> |
| 296 |
<para><literal>WINDOW</literal> indicates that the function is a |
| 297 |
<firstterm>window function</firstterm> rather than a plain function. |
| 298 |
This is currently only useful for functions written in C. |
| 299 |
The <literal>WINDOW</literal> attribute cannot be changed when |
| 300 |
replacing an existing function definition. |
| 301 |
</para> |
| 302 |
</listitem> |
| 303 |
</varlistentry> |
| 304 |
|
| 305 |
<varlistentry> |
| 306 |
<term><literal>IMMUTABLE</literal></term> |
| 307 |
<term><literal>STABLE</literal></term> |
| 308 |
<term><literal>VOLATILE</literal></term> |
| 309 |
|
| 310 |
<listitem> |
| 311 |
<para> |
| 312 |
These attributes inform the query optimizer about the behavior |
| 313 |
of the function. At most one choice |
| 314 |
can be specified. If none of these appear, |
| 315 |
<literal>VOLATILE</literal> is the default assumption. |
| 316 |
</para> |
| 317 |
|
| 318 |
<para><literal>IMMUTABLE</literal> indicates that the function |
| 319 |
cannot modify the database and always |
| 320 |
returns the same result when given the same argument values; that |
| 321 |
is, it does not do database lookups or otherwise use information not |
| 322 |
directly present in its argument list. If this option is given, |
| 323 |
any call of the function with all-constant arguments can be |
| 324 |
immediately replaced with the function value. |
| 325 |
</para> |
| 326 |
|
| 327 |
<para><literal>STABLE</literal> indicates that the function |
| 328 |
cannot modify the database, |
| 329 |
and that within a single table scan it will consistently |
| 330 |
return the same result for the same argument values, but that its |
| 331 |
result could change across SQL statements. This is the appropriate |
| 332 |
selection for functions whose results depend on database lookups, |
| 333 |
parameter variables (such as the current time zone), etc. (It is |
| 334 |
inappropriate for <literal>AFTER</literal> triggers that wish to |
| 335 |
query rows modified by the current command.) Also note |
| 336 |
that the <function>current_timestamp</function> family of functions qualify |
| 337 |
as stable, since their values do not change within a transaction. |
| 338 |
</para> |
| 339 |
|
| 340 |
<para><literal>VOLATILE</literal> indicates that the function value can |
| 341 |
change even within a single table scan, so no optimizations can be |
| 342 |
made. Relatively few database functions are volatile in this sense; |
| 343 |
some examples are <literal>random()</literal>, <literal>currval()</literal>, |
| 344 |
<literal>timeofday()</literal>. But note that any function that has |
| 345 |
side-effects must be classified volatile, even if its result is quite |
| 346 |
predictable, to prevent calls from being optimized away; an example is |
| 347 |
<literal>setval()</literal>. |
| 348 |
</para> |
| 349 |
|
| 350 |
<para> |
| 351 |
For additional details see <xref linkend="xfunc-volatility"/>. |
| 352 |
</para> |
| 353 |
</listitem> |
| 354 |
</varlistentry> |
| 355 |
|
| 356 |
<varlistentry> |
| 357 |
<term><literal>LEAKPROOF</literal></term> |
| 358 |
<listitem> |
| 359 |
<para> |
| 360 |
<literal>LEAKPROOF</literal> indicates that the function has no side |
| 361 |
effects. It reveals no information about its arguments other than by |
| 362 |
its return value. For example, a function which throws an error message |
| 363 |
for some argument values but not others, or which includes the argument |
| 364 |
values in any error message, is not leakproof. This affects how the |
| 365 |
system executes queries against views created with the |
| 366 |
<literal>security_barrier</literal> option or tables with row level |
| 367 |
security enabled. The system will enforce conditions from security |
| 368 |
policies and security barrier views before any user-supplied conditions |
| 369 |
from the query itself that contain non-leakproof functions, in order to |
| 370 |
prevent the inadvertent exposure of data. Functions and operators |
| 371 |
marked as leakproof are assumed to be trustworthy, and may be executed |
| 372 |
before conditions from security policies and security barrier views. |
| 373 |
In addition, functions which do not take arguments or which are not |
| 374 |
passed any arguments from the security barrier view or table do not have |
| 375 |
to be marked as leakproof to be executed before security conditions. See |
| 376 |
<xref linkend="sql-createview"/> and <xref linkend="rules-privileges"/>. |
| 377 |
This option can only be set by the superuser. |
| 378 |
</para> |
| 379 |
</listitem> |
| 380 |
</varlistentry> |
| 381 |
|
| 382 |
<varlistentry> |
| 383 |
<term><literal>CALLED ON NULL INPUT</literal></term> |
| 384 |
<term><literal>RETURNS NULL ON NULL INPUT</literal></term> |
| 385 |
<term><literal>STRICT</literal></term> |
| 386 |
|
| 387 |
<listitem> |
| 388 |
<para><literal>CALLED ON NULL INPUT</literal> (the default) indicates |
| 389 |
that the function will be called normally when some of its |
| 390 |
arguments are null. It is then the function author's |
| 391 |
responsibility to check for null values if necessary and respond |
| 392 |
appropriately. |
| 393 |
</para> |
| 394 |
|
| 395 |
<para><literal>RETURNS NULL ON NULL INPUT</literal> or |
| 396 |
<literal>STRICT</literal> indicates that the function always |
| 397 |
returns null whenever any of its arguments are null. If this |
| 398 |
parameter is specified, the function is not executed when there |
| 399 |
are null arguments; instead a null result is assumed |
| 400 |
automatically. |
| 401 |
</para> |
| 402 |
</listitem> |
| 403 |
</varlistentry> |
| 404 |
|
| 405 |
<varlistentry> |
| 406 |
<term><literal><optional>EXTERNAL</optional> SECURITY INVOKER</literal></term> |
| 407 |
<term><literal><optional>EXTERNAL</optional> SECURITY DEFINER</literal></term> |
| 408 |
|
| 409 |
<listitem> |
| 410 |
<para><literal>SECURITY INVOKER</literal> indicates that the function |
| 411 |
is to be executed with the privileges of the user that calls it. |
| 412 |
That is the default. <literal>SECURITY DEFINER</literal> |
| 413 |
specifies that the function is to be executed with the |
| 414 |
privileges of the user that owns it. For information on how to |
| 415 |
write <literal>SECURITY DEFINER</literal> functions safely, |
| 416 |
<link linkend="sql-createfunction-security">see below</link>. |
| 417 |
</para> |
| 418 |
|
| 419 |
<para> |
| 420 |
The key word <literal>EXTERNAL</literal> is allowed for SQL |
| 421 |
conformance, but it is optional since, unlike in SQL, this feature |
| 422 |
applies to all functions not only external ones. |
| 423 |
</para> |
| 424 |
</listitem> |
| 425 |
</varlistentry> |
| 426 |
|
| 427 |
<varlistentry> |
| 428 |
<term><literal>PARALLEL</literal></term> |
| 429 |
|
| 430 |
<listitem> |
| 431 |
<para> |
| 432 |
<literal>PARALLEL UNSAFE</literal> indicates that the function |
| 433 |
can't be executed in parallel mode; the presence of such a |
| 434 |
function in an SQL statement forces a serial execution plan. This is |
| 435 |
the default. <literal>PARALLEL RESTRICTED</literal> indicates that |
| 436 |
the function can be executed in parallel mode, but only in the parallel |
| 437 |
group leader process. <literal>PARALLEL SAFE</literal> |
| 438 |
indicates that the function is safe to run in parallel mode without |
| 439 |
restriction, including in parallel worker processes. |
| 440 |
</para> |
| 441 |
|
| 442 |
<para> |
| 443 |
Functions should be labeled parallel unsafe if they modify any database |
| 444 |
state, change the transaction state (other than by using a |
| 445 |
subtransaction for error recovery), access sequences (e.g., by |
| 446 |
calling <literal>currval</literal>) or make persistent changes to |
| 447 |
settings. They should |
| 448 |
be labeled parallel restricted if they access temporary tables, |
| 449 |
client connection state, cursors, prepared statements, or miscellaneous |
| 450 |
backend-local state which the system cannot synchronize in parallel mode |
| 451 |
(e.g., <literal>setseed</literal> cannot be executed other than by the group |
| 452 |
leader because a change made by another process would not be reflected |
| 453 |
in the leader). In general, if a function is labeled as being safe when |
| 454 |
it is restricted or unsafe, or if it is labeled as being restricted when |
| 455 |
it is in fact unsafe, it may throw errors or produce wrong answers |
| 456 |
when used in a parallel query. C-language functions could in theory |
| 457 |
exhibit totally undefined behavior if mislabeled, since there is no way |
| 458 |
for the system to protect itself against arbitrary C code, but in most |
| 459 |
likely cases the result will be no worse than for any other function. |
| 460 |
If in doubt, functions should be labeled as <literal>UNSAFE</literal>, which is |
| 461 |
the default. |
| 462 |
</para> |
| 463 |
</listitem> |
| 464 |
</varlistentry> |
| 465 |
|
| 466 |
<varlistentry> |
| 467 |
<term><literal>COST</literal> <replaceable class="parameter">execution_cost</replaceable></term> |
| 468 |
|
| 469 |
<listitem> |
| 470 |
<para> |
| 471 |
A positive number giving the estimated execution cost for the function, |
| 472 |
in units of <xref linkend="guc-cpu-operator-cost"/>. If the function |
| 473 |
returns a set, this is the cost per returned row. If the cost is |
| 474 |
not specified, 1 unit is assumed for C-language and internal functions, |
| 475 |
and 100 units for functions in all other languages. Larger values |
| 476 |
cause the planner to try to avoid evaluating the function more often |
| 477 |
than necessary. |
| 478 |
</para> |
| 479 |
</listitem> |
| 480 |
</varlistentry> |
| 481 |
|
| 482 |
<varlistentry> |
| 483 |
<term><literal>ROWS</literal> <replaceable class="parameter">result_rows</replaceable></term> |
| 484 |
|
| 485 |
<listitem> |
| 486 |
<para> |
| 487 |
A positive number giving the estimated number of rows that the planner |
| 488 |
should expect the function to return. This is only allowed when the |
| 489 |
function is declared to return a set. The default assumption is |
| 490 |
1000 rows. |
| 491 |
</para> |
| 492 |
</listitem> |
| 493 |
</varlistentry> |
| 494 |
|
| 495 |
<varlistentry> |
| 496 |
<term><literal>SUPPORT</literal> <replaceable class="parameter">support_function</replaceable></term> |
| 497 |
|
| 498 |
<listitem> |
| 499 |
<para> |
| 500 |
The name (optionally schema-qualified) of a <firstterm>planner support |
| 501 |
function</firstterm> to use for this function. See |
| 502 |
<xref linkend="xfunc-optimization"/> for details. |
| 503 |
You must be superuser to use this option. |
| 504 |
</para> |
| 505 |
</listitem> |
| 506 |
</varlistentry> |
| 507 |
|
| 508 |
<varlistentry> |
| 509 |
<term><replaceable>configuration_parameter</replaceable></term> |
| 510 |
<term><replaceable>value</replaceable></term> |
| 511 |
<listitem> |
| 512 |
<para> |
| 513 |
The <literal>SET</literal> clause causes the specified configuration |
| 514 |
parameter to be set to the specified value when the function is |
| 515 |
entered, and then restored to its prior value when the function exits. |
| 516 |
<literal>SET FROM CURRENT</literal> saves the value of the parameter that |
| 517 |
is current when <command>CREATE FUNCTION</command> is executed as the value |
| 518 |
to be applied when the function is entered. |
| 519 |
</para> |
| 520 |
|
| 521 |
<para> |
| 522 |
If a <literal>SET</literal> clause is attached to a function, then |
| 523 |
the effects of a <command>SET LOCAL</command> command executed inside the |
| 524 |
function for the same variable are restricted to the function: the |
| 525 |
configuration parameter's prior value is still restored at function exit. |
| 526 |
However, an ordinary |
| 527 |
<command>SET</command> command (without <literal>LOCAL</literal>) overrides the |
| 528 |
<literal>SET</literal> clause, much as it would do for a previous <command>SET |
| 529 |
LOCAL</command> command: the effects of such a command will persist after |
| 530 |
function exit, unless the current transaction is rolled back. |
| 531 |
</para> |
| 532 |
|
| 533 |
<para> |
| 534 |
See <xref linkend="sql-set"/> and |
| 535 |
<xref linkend="runtime-config"/> |
| 536 |
for more information about allowed parameter names and values. |
| 537 |
</para> |
| 538 |
</listitem> |
| 539 |
</varlistentry> |
| 540 |
|
| 541 |
<varlistentry> |
| 542 |
<term><replaceable class="parameter">definition</replaceable></term> |
| 543 |
|
| 544 |
<listitem> |
| 545 |
<para> |
| 546 |
A string constant defining the function; the meaning depends on the |
| 547 |
language. It can be an internal function name, the path to an |
| 548 |
object file, an SQL command, or text in a procedural language. |
| 549 |
</para> |
| 550 |
|
| 551 |
<para> |
| 552 |
It is often helpful to use dollar quoting (see <xref |
| 553 |
linkend="sql-syntax-dollar-quoting"/>) to write the function definition |
| 554 |
string, rather than the normal single quote syntax. Without dollar |
| 555 |
quoting, any single quotes or backslashes in the function definition must |
| 556 |
be escaped by doubling them. |
| 557 |
</para> |
| 558 |
|
| 559 |
</listitem> |
| 560 |
</varlistentry> |
| 561 |
|
| 562 |
<varlistentry> |
| 563 |
<term><literal><replaceable class="parameter">obj_file</replaceable>, <replaceable class="parameter">link_symbol</replaceable></literal></term> |
| 564 |
|
| 565 |
<listitem> |
| 566 |
<para> |
| 567 |
This form of the <literal>AS</literal> clause is used for |
| 568 |
dynamically loadable C language functions when the function name |
| 569 |
in the C language source code is not the same as the name of |
| 570 |
the SQL function. The string <replaceable |
| 571 |
class="parameter">obj_file</replaceable> is the name of the shared |
| 572 |
library file containing the compiled C function, and is interpreted |
| 573 |
as for the <link linkend="sql-load"><command>LOAD</command></link> command. The string |
| 574 |
<replaceable class="parameter">link_symbol</replaceable> is the |
| 575 |
function's link symbol, that is, the name of the function in the C |
| 576 |
language source code. If the link symbol is omitted, it is assumed to |
| 577 |
be the same as the name of the SQL function being defined. The C names |
| 578 |
of all functions must be different, so you must give overloaded C |
| 579 |
functions different C names (for example, use the argument types as |
| 580 |
part of the C names). |
| 581 |
</para> |
| 582 |
|
| 583 |
<para> |
| 584 |
When repeated <command>CREATE FUNCTION</command> calls refer to |
| 585 |
the same object file, the file is only loaded once per session. |
| 586 |
To unload and |
| 587 |
reload the file (perhaps during development), start a new session. |
| 588 |
</para> |
| 589 |
|
| 590 |
</listitem> |
| 591 |
</varlistentry> |
| 592 |
|
| 593 |
<varlistentry> |
| 594 |
<term><replaceable class="parameter">sql_body</replaceable></term> |
| 595 |
|
| 596 |
<listitem> |
| 597 |
<para> |
| 598 |
The body of a <literal>LANGUAGE SQL</literal> function. This can |
| 599 |
either be a single statement |
| 600 |
<programlisting> |
| 601 |
RETURN <replaceable>expression</replaceable> |
| 602 |
</programlisting> |
| 603 |
or a block |
| 604 |
<programlisting> |
| 605 |
BEGIN ATOMIC |
| 606 |
<replaceable>statement</replaceable>; |
| 607 |
<replaceable>statement</replaceable>; |
| 608 |
... |
| 609 |
<replaceable>statement</replaceable>; |
| 610 |
END |
| 611 |
</programlisting> |
| 612 |
</para> |
| 613 |
|
| 614 |
<para> |
| 615 |
This is similar to writing the text of the function body as a string |
| 616 |
constant (see <replaceable>definition</replaceable> above), but there |
| 617 |
are some differences: This form only works for <literal>LANGUAGE |
| 618 |
SQL</literal>, the string constant form works for all languages. This |
| 619 |
form is parsed at function definition time, the string constant form is |
| 620 |
parsed at execution time; therefore this form cannot support |
| 621 |
polymorphic argument types and other constructs that are not resolvable |
| 622 |
at function definition time. This form tracks dependencies between the |
| 623 |
function and objects used in the function body, so <literal>DROP |
| 624 |
... CASCADE</literal> will work correctly, whereas the form using |
| 625 |
string literals may leave dangling functions. Finally, this form is |
| 626 |
more compatible with the SQL standard and other SQL implementations. |
| 627 |
</para> |
| 628 |
</listitem> |
| 629 |
</varlistentry> |
| 630 |
|
| 631 |
</variablelist> |
| 632 |
</refsect1> |
| 633 |
|
| 634 |
<refsect1 id="sql-createfunction-overloading"> |
| 635 |
<title>Overloading</title> |
| 636 |
|
| 637 |
<para> |
| 638 |
<productname>PostgreSQL</productname> allows function |
| 639 |
<firstterm>overloading</firstterm>; that is, the same name can be |
| 640 |
used for several different functions so long as they have distinct |
| 641 |
input argument types. Whether or not you use it, this capability entails |
| 642 |
security precautions when calling functions in databases where some users |
| 643 |
mistrust other users; see <xref linkend="typeconv-func"/>. |
| 644 |
</para> |
| 645 |
|
| 646 |
<para> |
| 647 |
Two functions are considered the same if they have the same names and |
| 648 |
<emphasis>input</emphasis> argument types, ignoring any <literal>OUT</literal> |
| 649 |
parameters. Thus for example these declarations conflict: |
| 650 |
<programlisting> |
| 651 |
CREATE FUNCTION foo(int) ... |
| 652 |
CREATE FUNCTION foo(int, out text) ... |
| 653 |
</programlisting> |
| 654 |
</para> |
| 655 |
|
| 656 |
<para> |
| 657 |
Functions that have different argument type lists will not be considered |
| 658 |
to conflict at creation time, but if defaults are provided they might |
| 659 |
conflict in use. For example, consider |
| 660 |
<programlisting> |
| 661 |
CREATE FUNCTION foo(int) ... |
| 662 |
CREATE FUNCTION foo(int, int default 42) ... |
| 663 |
</programlisting> |
| 664 |
A call <literal>foo(10)</literal> will fail due to the ambiguity about which |
| 665 |
function should be called. |
| 666 |
</para> |
| 667 |
|
| 668 |
</refsect1> |
| 669 |
|
| 670 |
<refsect1 id="sql-createfunction-notes"> |
| 671 |
<title>Notes</title> |
| 672 |
|
| 673 |
<para> |
| 674 |
The full <acronym>SQL</acronym> type syntax is allowed for |
| 675 |
declaring a function's arguments and return value. However, |
| 676 |
parenthesized type modifiers (e.g., the precision field for |
| 677 |
type <type>numeric</type>) are discarded by <command>CREATE FUNCTION</command>. |
| 678 |
Thus for example |
| 679 |
<literal>CREATE FUNCTION foo (varchar(10)) ...</literal> |
| 680 |
is exactly the same as |
| 681 |
<literal>CREATE FUNCTION foo (varchar) ...</literal>. |
| 682 |
</para> |
| 683 |
|
| 684 |
<para> |
| 685 |
When replacing an existing function with <command>CREATE OR REPLACE |
| 686 |
FUNCTION</command>, there are restrictions on changing parameter names. |
| 687 |
You cannot change the name already assigned to any input parameter |
| 688 |
(although you can add names to parameters that had none before). |
| 689 |
If there is more than one output parameter, you cannot change the |
| 690 |
names of the output parameters, because that would change the |
| 691 |
column names of the anonymous composite type that describes the |
| 692 |
function's result. These restrictions are made to ensure that |
| 693 |
existing calls of the function do not stop working when it is replaced. |
| 694 |
</para> |
| 695 |
|
| 696 |
<para> |
| 697 |
If a function is declared <literal>STRICT</literal> with a <literal>VARIADIC</literal> |
| 698 |
argument, the strictness check tests that the variadic array <emphasis>as |
| 699 |
a whole</emphasis> is non-null. The function will still be called if the |
| 700 |
array has null elements. |
| 701 |
</para> |
| 702 |
|
| 703 |
</refsect1> |
| 704 |
|
| 705 |
<refsect1 id="sql-createfunction-examples"> |
| 706 |
<title>Examples</title> |
| 707 |
|
| 708 |
<para> |
| 709 |
Add two integers using an SQL function: |
| 710 |
<programlisting> |
| 711 |
CREATE FUNCTION add(integer, integer) RETURNS integer |
| 712 |
AS 'select $1 + $2;' |
| 713 |
LANGUAGE SQL |
| 714 |
IMMUTABLE |
| 715 |
RETURNS NULL ON NULL INPUT; |
| 716 |
</programlisting> |
| 717 |
The same function written in a more SQL-conforming style, using argument |
| 718 |
names and an unquoted body: |
| 719 |
<programlisting> |
| 720 |
CREATE FUNCTION add(a integer, b integer) RETURNS integer |
| 721 |
LANGUAGE SQL |
| 722 |
IMMUTABLE |
| 723 |
RETURNS NULL ON NULL INPUT |
| 724 |
RETURN a + b; |
| 725 |
</programlisting> |
| 726 |
</para> |
| 727 |
|
| 728 |
<para> |
| 729 |
Increment an integer, making use of an argument name, in |
| 730 |
<application>PL/pgSQL</application>: |
| 731 |
<programlisting> |
| 732 |
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ |
| 733 |
BEGIN |
| 734 |
RETURN i + 1; |
| 735 |
END; |
| 736 |
$$ LANGUAGE plpgsql; |
| 737 |
</programlisting> |
| 738 |
</para> |
| 739 |
|
| 740 |
<para> |
| 741 |
Return a record containing multiple output parameters: |
| 742 |
<programlisting> |
| 743 |
CREATE FUNCTION dup(in int, out f1 int, out f2 text) |
| 744 |
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ |
| 745 |
LANGUAGE SQL; |
| 746 |
|
| 747 |
SELECT * FROM dup(42); |
| 748 |
</programlisting> |
| 749 |
You can do the same thing more verbosely with an explicitly named |
| 750 |
composite type: |
| 751 |
<programlisting> |
| 752 |
CREATE TYPE dup_result AS (f1 int, f2 text); |
| 753 |
|
| 754 |
CREATE FUNCTION dup(int) RETURNS dup_result |
| 755 |
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ |
| 756 |
LANGUAGE SQL; |
| 757 |
|
| 758 |
SELECT * FROM dup(42); |
| 759 |
</programlisting> |
| 760 |
Another way to return multiple columns is to use a <literal>TABLE</literal> |
| 761 |
function: |
| 762 |
<programlisting> |
| 763 |
CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text) |
| 764 |
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ |
| 765 |
LANGUAGE SQL; |
| 766 |
|
| 767 |
SELECT * FROM dup(42); |
| 768 |
</programlisting> |
| 769 |
However, a <literal>TABLE</literal> function is different from the |
| 770 |
preceding examples, because it actually returns a <emphasis>set</emphasis> |
| 771 |
of records, not just one record. |
| 772 |
</para> |
| 773 |
</refsect1> |
| 774 |
|
| 775 |
<refsect1 id="sql-createfunction-security"> |
| 776 |
<title>Writing <literal>SECURITY DEFINER</literal> Functions Safely</title> |
| 777 |
|
| 778 |
<indexterm> |
| 779 |
<primary><varname>search_path</varname> configuration parameter</primary> |
| 780 |
<secondary>use in securing functions</secondary> |
| 781 |
</indexterm> |
| 782 |
|
| 783 |
<indexterm> |
| 784 |
<primary><varname>createrole_self_grant</varname> configuration parameter</primary> |
| 785 |
<secondary>use in securing functions</secondary> |
| 786 |
</indexterm> |
| 787 |
|
| 788 |
<para> |
| 789 |
Because a <literal>SECURITY DEFINER</literal> function is executed |
| 790 |
with the privileges of the user that owns it, care is needed to |
| 791 |
ensure that the function cannot be misused. For security, |
| 792 |
<xref linkend="guc-search-path"/> should be set to exclude any schemas |
| 793 |
writable by untrusted users. This prevents |
| 794 |
malicious users from creating objects (e.g., tables, functions, and |
| 795 |
operators) that mask objects intended to be used by the function. |
| 796 |
Particularly important in this regard is the |
| 797 |
temporary-table schema, which is searched first by default, and |
| 798 |
is normally writable by anyone. A secure arrangement can be obtained |
| 799 |
by forcing the temporary schema to be searched last. To do this, |
| 800 |
write <literal>pg_temp</literal><indexterm><primary>pg_temp</primary><secondary>securing functions</secondary></indexterm> as the last entry in <varname>search_path</varname>. |
| 801 |
This function illustrates safe usage: |
| 802 |
|
| 803 |
<programlisting> |
| 804 |
CREATE FUNCTION check_password(uname TEXT, pass TEXT) |
| 805 |
RETURNS BOOLEAN AS $$ |
| 806 |
DECLARE passed BOOLEAN; |
| 807 |
BEGIN |
| 808 |
SELECT (pwd = $2) INTO passed |
| 809 |
FROM pwds |
| 810 |
WHERE username = $1; |
| 811 |
|
| 812 |
RETURN passed; |
| 813 |
END; |
| 814 |
$$ LANGUAGE plpgsql |
| 815 |
SECURITY DEFINER |
| 816 |
-- Set a secure search_path: trusted schema(s), then 'pg_temp'. |
| 817 |
SET search_path = admin, pg_temp; |
| 818 |
</programlisting> |
| 819 |
|
| 820 |
This function's intention is to access a table <literal>admin.pwds</literal>. |
| 821 |
But without the <literal>SET</literal> clause, or with a <literal>SET</literal> clause |
| 822 |
mentioning only <literal>admin</literal>, the function could be subverted by |
| 823 |
creating a temporary table named <literal>pwds</literal>. |
| 824 |
</para> |
| 825 |
|
| 826 |
<para> |
| 827 |
If the security definer function intends to create roles, and if it |
| 828 |
is running as a non-superuser, <varname>createrole_self_grant</varname> |
| 829 |
should also be set to a known value using the <literal>SET</literal> |
| 830 |
clause. |
| 831 |
</para> |
| 832 |
|
| 833 |
<para> |
| 834 |
Another point to keep in mind is that by default, execute privilege |
| 835 |
is granted to <literal>PUBLIC</literal> for newly created functions |
| 836 |
(see <xref linkend="ddl-priv"/> for more |
| 837 |
information). Frequently you will wish to restrict use of a security |
| 838 |
definer function to only some users. To do that, you must revoke |
| 839 |
the default <literal>PUBLIC</literal> privileges and then grant execute |
| 840 |
privilege selectively. To avoid having a window where the new function |
| 841 |
is accessible to all, create it and set the privileges within a single |
| 842 |
transaction. For example: |
| 843 |
</para> |
| 844 |
|
| 845 |
<programlisting> |
| 846 |
BEGIN; |
| 847 |
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER; |
| 848 |
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC; |
| 849 |
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins; |
| 850 |
COMMIT; |
| 851 |
</programlisting> |
| 852 |
|
| 853 |
</refsect1> |
| 854 |
|
| 855 |
<refsect1 id="sql-createfunction-compat"> |
| 856 |
<title>Compatibility</title> |
| 857 |
|
| 858 |
<para> |
| 859 |
A <command>CREATE FUNCTION</command> command is defined in the SQL |
| 860 |
standard. The <productname>PostgreSQL</productname> implementation can be |
| 861 |
used in a compatible way but has many extensions. Conversely, the SQL |
| 862 |
standard specifies a number of optional features that are not implemented |
| 863 |
in <productname>PostgreSQL</productname>. |
| 864 |
</para> |
| 865 |
|
| 866 |
<para> |
| 867 |
The following are important compatibility issues: |
| 868 |
|
| 869 |
<itemizedlist> |
| 870 |
<listitem> |
| 871 |
<para> |
| 872 |
<literal>OR REPLACE</literal> is a PostgreSQL extension. |
| 873 |
</para> |
| 874 |
</listitem> |
| 875 |
|
| 876 |
<listitem> |
| 877 |
<para> |
| 878 |
For compatibility with some other database systems, <replaceable |
| 879 |
class="parameter">argmode</replaceable> can be written either before or |
| 880 |
after <replaceable class="parameter">argname</replaceable>. But only |
| 881 |
the first way is standard-compliant. |
| 882 |
</para> |
| 883 |
</listitem> |
| 884 |
|
| 885 |
<listitem> |
| 886 |
<para> |
| 887 |
For parameter defaults, the SQL standard specifies only the syntax with |
| 888 |
the <literal>DEFAULT</literal> key word. The syntax with |
| 889 |
<literal>=</literal> is used in T-SQL and Firebird. |
| 890 |
</para> |
| 891 |
</listitem> |
| 892 |
|
| 893 |
<listitem> |
| 894 |
<para> |
| 895 |
The <literal>SETOF</literal> modifier is a PostgreSQL extension. |
| 896 |
</para> |
| 897 |
</listitem> |
| 898 |
|
| 899 |
<listitem> |
| 900 |
<para> |
| 901 |
Only <literal>SQL</literal> is standardized as a language. |
| 902 |
</para> |
| 903 |
</listitem> |
| 904 |
|
| 905 |
<listitem> |
| 906 |
<para> |
| 907 |
All other attributes except <literal>CALLED ON NULL INPUT</literal> and |
| 908 |
<literal>RETURNS NULL ON NULL INPUT</literal> are not standardized. |
| 909 |
</para> |
| 910 |
</listitem> |
| 911 |
|
| 912 |
<listitem> |
| 913 |
<para> |
| 914 |
For the body of <literal>LANGUAGE SQL</literal> functions, the SQL |
| 915 |
standard only specifies the <replaceable>sql_body</replaceable> form. |
| 916 |
</para> |
| 917 |
</listitem> |
| 918 |
</itemizedlist> |
| 919 |
</para> |
| 920 |
|
| 921 |
<para> |
| 922 |
Simple <literal>LANGUAGE SQL</literal> functions can be written in a way |
| 923 |
that is both standard-conforming and portable to other implementations. |
| 924 |
More complex functions using advanced features, optimization attributes, or |
| 925 |
other languages will necessarily be specific to PostgreSQL in a significant |
| 926 |
way. |
| 927 |
</para> |
| 928 |
</refsect1> |
| 929 |
|
| 930 |
|
| 931 |
<refsect1> |
| 932 |
<title>See Also</title> |
| 933 |
|
| 934 |
<simplelist type="inline"> |
| 935 |
<member><xref linkend="sql-alterfunction"/></member> |
| 936 |
<member><xref linkend="sql-dropfunction"/></member> |
| 937 |
<member><xref linkend="sql-grant"/></member> |
| 938 |
<member><xref linkend="sql-load"/></member> |
| 939 |
<member><xref linkend="sql-revoke"/></member> |
| 940 |
</simplelist> |
| 941 |
</refsect1> |
| 942 |
|
| 943 |
</refentry> |