Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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: select_into_exact.patch
Description: text/x-patch (11.4 KB)

Responses

pgsql-hackers by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group