Database Reference
In-Depth Information
Other parameters
There is more than one way to get data into and out of a function. We can also de-
clare IN/OUT parameters, return a table, return a set of records, and use cursors for
both input and output.
This brings us to a special data type called
ANY
. It allows the parameter type to be
undefined, and will allow any basic data type to be passed to the function. It is then
up to the function to decide what to do with the data from there.
More control
Once you have your function written the way you need, PostgreSQL gives you addi-
tional control over how the function executes. You can control what data the function
can access and how PostgreSQL will interpret the expense of running the function.
There are two statements that provide a security context for your functions. The first
one is
Security Invoker
, which is the default security context. In the default context,
the privileges of the calling user are respected by the function.
The other context is
Security Definer
. In this context, the user privileges of the cre-
ator of the function are respected during the execution of the function. Generally, this
is used to temporarily escalate user rights for a specific purpose.
Cost
can also be defined for the function. This cost will help the query planner es-
timate how expensive it is to call your function. Higher orders of cost will cause
the query planner to change the access path so your function will be called as few
times as possible. The PostgreSQL documentation shows these numbers to be a
factor of
cpu_operator_cost
. That's more than a little misleading. The numbers
have no direct correlation to CPU cycles. They are only relevant in comparison with
one another. It's more like how some national money compares with the rest of the
European Union. Some Euros are more equal than others.
To estimate your own function's complexity, start with the language you are imple-
menting it in. For C, the default would be
1 * number of records returned
.
For Python,
1.5
. For scripting languages such as PHP, a more appropriate number
might be
100
. For plsh, you might want to use 150 or more depending on how many
