PostgreSQL obtaining the Result Status

There is several ways to determine the effect of a command. The first method is for use the GET
Diagnostics command, which has the form:
GET [Current] diagnostics Variable {= |: =} Item [, ...];
This command allows retrieval of system status indicators. Each item is a key word identifying a
Status value to being assigned to the specified variable (which should is of the right data type to receive
IT). The currently available status items is 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 are only useful after an INSERT command into a table
Containing OIDs. Colon-equal (: =) can is used instead of Sql-standard = for GET diagnostics.
An example:
GET Diagnostics Integer_var = Row_count;
The second method to determine the effects of a command was 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 was 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 or FOREACH statement sets FOUND true if it iterates one or more times, else false. FOUND is
Set this is when the loop exits; Inside the execution of the loop, FOUND is isn't modified by the
Loop 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 don't change the state of FOUND. Note in particular that EXECUTE
Changes the output of the GET diagnostics, but does is not a change FOUND.
FOUND is a local variable within each pl/pgsql function; Any changes to it affect
function.

Executing a Query with a single-row Result

The result of a SQL command yielding a single row (possibly of multiple columns) can is assigned to
A record variable, row-type variable, or list of scalar variables. This is do by writing the base SQL
command and adding an into clause. For example,
SELECT select_expressions into [STRICT] target from ...;
INSERT ... Returning expressions into [STRICT] target;
UPDATE ... Returning expressions into [STRICT] target;
DELETE ... Returning expressions into [STRICT] target;
Where target can is a record variable, a row variable, or a comma-separated list of simple variables
and Record/row fields. Pl/pgsql variables'll be substituted into the rest of the query, and the plan
is cached, just as described above for commands that does not return rows. This works for SELECT,
Insert/update/delete with returning, and utility commands that return row-set results (such as
EXPLAIN). Except for the TO clause, the SQL command is the same as it would be written outside
Pl/pgsql.

Tip:note that this interpretation for SELECT with to is quite different from PostgreSQL ' s regular
SELECT into command, wherein the to target is a newly created table. If you want to create
A table from a SELECT result inside a pl/pgsql function, use the syntax CREATE table ... As
SELECT.

If a row or a variable list is used as target, the query ' s result columns must exactly match the structure
of the target as to number and data types, or else a run-time error occurs. When a record variable is
The target, it automatically configures itself to the row type of the query result columns.
The TO clause can appear almost anywhere in the SQL command. Customarily it is written either
Just before or just after the list of select_expressions in a select command, or at the end of the
command for the other command types. It is recommended so follow this Convention the
Pl/pgsql parser becomes stricter in the future versions.
If STRICT is not specified in the-clause, then target'll be set to the first row returned by the
Query, or to nulls if the query returned no rows. (Note that "the first row" was not well-defined unless
You ' ve used ORDER by.) Any result of rows after the first row is discarded. You can check the special
FOUND variable (see sections 40.5.5) to determine whether a row is returned:
SELECT * into Myrec from emp WHERE empname = myname;
IF not FOUND Then
RAISE EXCEPTION ' Employee% not found ', myname;
END IF;

If The STRICT option is specified, the query must return exactly one row or a run-time error would be
reported, either No_data_found (NO rows) or too_many_rows (more than one row). You can use
An exception block if you wish to catch the error, for example:
BEGIN
SELECT * into STRICT myrec from emp WHERE empname = myname;
EXCEPTION
When No_data_found Then
RAISE EXCEPTION ' Employee% not found ', myname;
When Too_many_rows Then
RAISE EXCEPTION ' Employee% not unique ', myname;
END;
Successful execution of a command with STRICT all sets FOUND to true.
For Insert/update/delete with returning, Pl/pgsql reports a error for more than one returned
Row, even when STRICT was not specified. This is because there was no option such as ORDER by
with which to determine which affected row should is returned.
If Print_strict_params is enabled for the function, and then when a error is thrown because the
Requirements of STRICT is not met, the DETAIL part of the error message would include information
About the parameters passed to the query. You can change the Print_strict_params setting
Functions by setting Plpgsql.print_strict_params, though only subsequent function compilations
would be affected. can also enable it on a per-function basis by using a compiler option, for
Example

CREATE FUNCTION Get_userid (username text) RETURNS int
As $$
#print_strict_params on
DECLARE
UserID int;
BEGIN

SELECT Users.userid into STRICT userid
From users WHERE users.username = get_userid.username;
RETURN userid;
END
$$ LANGUAGE Plpgsql;
On failure, this function might produce an error message such as
Error:query returned no rows
Detail:parameters: $ = ' Nosuchuser '
Context:pl/pgsql function Get_userid (text) line 6 at SQL statement
Note:the STRICT option matches the behavior of Oracle PL/SQL ' s SELECT into and related
Statements.
To handle cases where you need to process multiple result rows from a SQL query, see section 40.6.4.

PostgreSQL obtaining the Result Status