SELECT statements that return a
single result row can also be executed using EXEC SQL directly. To handle result sets with
multiple rows, an application has to use a cursor; see Section 33.3.2 below. (As
a special case, an application can fetch multiple rows at once
into an array host variable; see Section
33.4.4.3.1.)

Single-row select:

EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';

Also, a configuration parameter can be retrieved with the
SHOW command:

EXEC SQL SHOW search_path INTO :var;

The tokens of the form :something are host variables, that is, they refer to
variables in the C program. They are explained in Section 33.4.

To retrieve a result set holding multiple rows, an
application has to declare a cursor and fetch each row from the
cursor. The steps to use a cursor are the following: declare a
cursor, open it, fetch a row from the cursor, repeat, and
finally close it.

For more details about declaration of the cursor, see
DECLARE, and see FETCH for FETCH
command details.

Note: The ECPG DECLARE
command does not actually cause a statement to be sent to
the PostgreSQL backend. The cursor is opened in the backend
(using the backend's DECLARE
command) at the point when the OPEN command is executed.

In the default mode, statements are committed only when
EXEC SQL COMMIT is issued. The
embedded SQL interface also supports autocommit of transactions
(similar to libpq behavior)
via the -t command-line option to
ecpg (see ecpg) or
via the EXEC SQL SET AUTOCOMMIT TO ON
statement. In autocommit mode, each command is automatically
committed unless it is inside an explicit transaction block.
This mode can be explicitly turned off using EXEC SQL SET AUTOCOMMIT TO OFF.

If a statement returns a single row, the application can
call EXECUTE after PREPARE to execute the statement, supplying the
actual values for the placeholders with a USING clause:

EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1;

If a statement returns multiple rows, the application can
use a cursor declared based on the prepared statement. To bind
input parameters, the cursor must be opened with a USING clause:

EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";
EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;
/* when end of result set reached, break out of while loop */
EXEC SQL WHENEVER NOT FOUND DO BREAK;
EXEC SQL OPEN foo_bar USING 100;
...
while (1)
{
EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
...
}
EXEC SQL CLOSE foo_bar;

When you don't need the prepared statement anymore, you
should deallocate it:

EXEC SQL DEALLOCATE PREPARE name;

For more details about PREPARE, see
PREPARE. Also see Section 33.5 for more details about
using placeholders and input parameters.