|PostgreSQL Reference Manual - Volume 2 - Programming Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
RRP £19.95 ($34.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
9.6.2 Executing a Query With No Result
For any SQL query that does not return rows, for example
INSERT without a
RETURNING clause, you can
execute the query within a PL/pgSQL function
just by writing the query.
Any PL/pgSQL variable name appearing in the query text is replaced by a parameter symbol, and then the current value of the variable is provided as the parameter value at run time. This allows the same textual query to do different things in different calls of the function.
Note: This two-step process allows PL/pgSQL to plan the query just once and re-use the plan on subsequent executions. As an example, if you writeDECLARE key TEXT; delta INTEGER; BEGIN ... UPDATE mytab SET val = val + delta WHERE id = key;
the query text seen by the main SQL engine will look likeUPDATE mytab SET val = val + $1 WHERE id = $2;
Although you don't normally have to think about this, it's helpful to know it when you need to make sense of syntax-error messages.
Caution: PL/pgSQL will substitute for any identifier matching one of the function's declared variables; it is not bright enough to know whether that's what you meant! Thus, it is a bad idea to use a variable name that is the same as any table or column name that you need to reference in queries within the function. Sometimes you can work around this by using qualified names in the query: PL/pgSQL will not substitute in a qualified name foo.bar, even if foo or bar is a declared variable name.
Sometimes it is useful to evaluate an expression or
query but discard the result, for example when calling a function
that has side-effects but no useful result value. To do
this in PL/pgSQL, use the
This executes query and discards the
result. Write the query the same
way you would write an SQL
SELECT command, but replace the
PL/pgSQL variables will be
substituted into the query as usual. Also, the special variable
FOUND is set to true if the query produced at
least one row, or false if it produced no rows.
Note: One might expect that writing
SELECTdirectly would accomplish this result, but at present the only accepted way to do it is
PERFORM. A SQL command that can return rows, such as
SELECT, will be rejected as an error unless it has an
INTOclause as discussed in the next section.
PERFORM create_mv('cs_session_page_requests_mv', my_query);
|ISBN 0954612035||PostgreSQL Reference Manual - Volume 2 - Programming Guide||See the print edition|