30.5. Cancelling Queries in Progress

Creates a data structure containing the information needed to cancel a command issued through a particular database connection.

        PGcancel *PQgetCancel(PGconn *conn);
       

PQgetCancel creates a PGcancel object given a PGconn connection object. It will return NULL if the given conn is NULL or an invalid connection. The PGcancel object is an opaque structure that is not meant to be accessed directly by the application; it can only be passed to PQcancel or PQfreeCancel.

Frees a data structure created by PQgetCancel.

        void PQfreeCancel(PGcancel *cancel);
       

PQfreeCancel frees a data object previously created by PQgetCancel.

Requests that the server abandon processing of the current command.

        int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
       

The return value is 1 if the cancel request was successfully dispatched and 0 if not. If not, errbuf is filled with an error message explaining why not. errbuf must be a char array of size errbufsize (the recommended size is 256 bytes).

Successful dispatch is no guarantee that the request will have any effect, however. If the cancellation is effective, the current command will terminate early and return an error result. If the cancellation fails (say, because the server was already done processing the command), then there will be no visible result at all.

PQcancel can safely be invoked from a signal handler, if the errbuf is a local variable in the signal handler. The PGcancel object is PLACEABLE" >query and the loop body is executed for each row. Here is an example:

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    PERFORM cs_log('Refreshing materialized views...');

    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

        -- Now "mviews" has one record from cs_materialized_views

        PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
        EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query;
    END LOOP;

    PERFORM cs_log('Done refreshing materialized views.');
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

If the loop is terminated by an EXIT statement, the last assigned row value is still accessible after the loop.

The query used in this type of FOR statement can be any SQL command that returns rows to the caller: SELECT is the most common case, but you can also use INSERT, UPDATE, or DELETE with a RETURNING clause. Some utility commands such as EXPLAIN will work too.

PL/pgSQL variables are substituted into the query text, and the query plan is cached for possible re-use, as discussed in detail in Section 38.10.1 and Section 38.10.2.

The FOR-IN-EXECUTE statement is another way to iterate over rows:

[ <<label>> ]
FOR target IN EXECUTE