Home Explore Blog CI



postgresql

16th chunk of `doc/src/sgml/plpgsql.sgml`
02378911155a472ed5525453ab32f559a41db9ca697d41a70000000100000fa1
 just as described above,
     and the plan is cached in the same way.
     This works for <command>SELECT</command>,
     <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command>
     with <literal>RETURNING</literal>, and certain utility commands
     that return row sets, such as <command>EXPLAIN</command>.
     Except for the <literal>INTO</literal> clause, the SQL command is the same
     as it would be written outside <application>PL/pgSQL</application>.
    </para>

   <tip>
    <para>
     Note that this interpretation of <command>SELECT</command> with <literal>INTO</literal>
     is quite different from <productname>PostgreSQL</productname>'s regular
     <command>SELECT INTO</command> command, wherein the <literal>INTO</literal>
     target is a newly created table.  If you want to create a table from a
     <command>SELECT</command> result inside a
     <application>PL/pgSQL</application> function, use the syntax
     <command>CREATE TABLE ... AS SELECT</command>.
    </para>
   </tip>

    <para>
     If a row variable or a variable list is used as target,
     the command'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 command's result columns.
    </para>

    <para>
     The <literal>INTO</literal> clause can appear almost anywhere in the SQL
     command.  Customarily it is written either just before or just after
     the list of <replaceable>select_expressions</replaceable> in a
     <command>SELECT</command> command, or at the end of the command for other
     command types.  It is recommended that you follow this convention
     in case the <application>PL/pgSQL</application> parser becomes
     stricter in future versions.
    </para>

    <para>
     If <literal>STRICT</literal> is not specified in the <literal>INTO</literal>
     clause, then <replaceable>target</replaceable> will be set to the first
     row returned by the command, or to nulls if the command returned no rows.
     (Note that <quote>the first row</quote> is not
     well-defined unless you've used <literal>ORDER BY</literal>.)  Any result rows
     after the first row are discarded.
     You can check the special <literal>FOUND</literal> variable (see
     <xref linkend="plpgsql-statements-diagnostics"/>) to
     determine whether a row was returned:

<programlisting>
SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;
</programlisting>

     If the <literal>STRICT</literal> option is specified, the command must
     return exactly one row or a run-time error will be reported, either
     <literal>NO_DATA_FOUND</literal> (no rows) or <literal>TOO_MANY_ROWS</literal>
     (more than one row). You can use an exception block if you wish
     to catch the error, for example:

<programlisting>
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;
</programlisting>
     Successful execution of a command with <literal>STRICT</literal>
     always sets <literal>FOUND</literal> to true.
    </para>

    <para>
     For <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command> with
     <literal>RETURNING</literal>, <application>PL/pgSQL</application> reports
     an error for more than one returned row, even when
     <literal>STRICT</literal> is not specified.  This is because there
     is no option such as <literal>ORDER BY</literal> with which to determine
     which affected row should be returned.
    </para>

    <para>
     If <literal>print_strict_params</literal>

Title: SELECT INTO Clause Details, STRICT Option, and Error Handling
Summary
The INTO clause in PL/pgSQL differs from the standard PostgreSQL SELECT INTO. It requires CREATE TABLE ... AS SELECT to create a table. Result columns must match the target's structure. INTO can be placed almost anywhere in the SQL command. STRICT ensures exactly one row is returned, raising NO_DATA_FOUND or TOO_MANY_ROWS errors. Without STRICT, the target gets the first row or nulls, and FOUND indicates success. INSERT/UPDATE/DELETE/MERGE with RETURNING raises an error if more than one row is returned, even without STRICT.