- publishing free software manuals
PostgreSQL Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
ISBN 0954612035
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 write

    key TEXT;
    delta INTEGER;
    UPDATE mytab SET val = val + delta WHERE id = key;

the query text seen by the main SQL engine will look like

UPDATE 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 SELECT 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 PERFORM statement:

PERFORM query;

This executes query and discards the result. Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM. 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 SELECT directly 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 INTO clause as discussed in the next section.

An example:

PERFORM create_mv('cs_session_page_requests_mv', my_query);
ISBN 0954612035PostgreSQL Reference Manual - Volume 2 - Programming GuideSee the print edition