PL/pgSQL: SELECT INTO EXACT

From: Matt Miller <mattm(at)epx(dot)com>
To: pgsql-patches <pgsql-patches(at)postgresql(dot)org>
Subject: PL/pgSQL: SELECT INTO EXACT
Date: 2005-07-29 20:18:14
Message-ID: 1122668294.3600.29.camel@dbamm01-linux
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

This patch implements an optional EXACT keyword after the INTO keyword
of the PL/pgSQL SELECT INTO command. The motivation is to come closer
to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets,
raise an exception and leave the targets untouched if the query does not
return exactly one row. This patch does not go so far as to raise an
exception, but it can simplify porting efforts from PL/SQL. I also feel
that this EXACT behavior is overall a bit cleaner than the current
PL/pgSQL behavior. Maybe I've just been brainwashed by years of
Oracle'ing.

Here are three excerpts from the patched PL/pgSQL documentation:

"If the EXACT option is specified, then target will not be set unless
the query returns exactly one row"

"You can check the special FOUND variable after a SELECT INTO to
determine whether the statement was successful. ... an EXACT query is
successful only if exactly 1 row is returned."

"...GET DIAGNOSTICS (see Section 35.6.6) to retrieve ROW_COUNT. After a
SELECT INTO EXACT statement ROW_COUNT ... will be equal to 0, 1, or 2,
indicating no matching rows, exactly one matching row, or greater than
one matching row, respectively."

Attachment Content-Type Size
select_into_exact.patch text/x-patch 11.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2005-07-29 20:35:32 Re: [Testperf-general] dbt2 & opteron performance
Previous Message Mark Wong 2005-07-29 20:11:35 Re: [Testperf-general] dbt2 & opteron performance

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2005-07-29 21:46:11 Re: [HACKERS] Autovacuum loose ends
Previous Message Alvaro Herrera 2005-07-29 19:48:05 Re: [HACKERS] Autovacuum loose ends