The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 478 pages ISBN 9781906966065 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 2 Buy the book here! >>> learn more

9.5.5 Obtaining the Result Status

There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command, which has the form:

GET DIAGNOSTICS variable = item [ , ... ];

This command allows retrieval of system status indicators. Each item is a key word identifying a state value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are ROW_COUNT, the number of rows processed by the last SQL command sent to the SQL engine, and RESULT_OID, the OID of the last row inserted by the most recent SQL command. Note that RESULT_OID is only useful after an INSERT command into a table containing OIDs.

An example:

GET DIAGNOSTICS integer_var = ROW_COUNT;

The second method to determine the effects of a command is to check the special variable named FOUND, which is of type boolean. FOUND starts out false within each PL/pgSQL function call. It is set by each of the following types of statements:

• A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.
• A PERFORM statement sets FOUND true if it produces (and discards) one or more rows, false if no row is produced.
• UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.
• A FETCH statement sets FOUND true if it returns a row, false if no row is returned.
• A MOVE statement sets FOUND true if it successfully repositions the cursor, false otherwise.
• A FOR statement sets FOUND true if it iterates one or more times, else false. This applies to all four variants of the FOR statement (integer FOR loops, record-set FOR loops, dynamic record-set FOR loops, and cursor FOR loops). FOUND is set this way when the FOR loop exits; inside the execution of the loop, FOUND is not modified by the FOR statement, although it might be changed by the execution of other statements within the loop body.
• RETURN QUERY and RETURN QUERY EXECUTE statements set FOUND true if the query returns at least one row, false if no row is returned.

Other PL/pgSQL statements do not change the state of FOUND. Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

FOUND is a local variable within each PL/pgSQL function; any changes to it affect only the current function.