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


From: Matt Miller <mattm(at)epx(dot)com>
To: pgsql-patches <pgsql-patches(at)postgresql(dot)org>
Date: 2005-07-29 20:18:14
Message-ID: 1122668294.3600.29.camel@dbamm01-linux (view raw, whole thread or download thread mbox)
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

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)


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-2017 The PostgreSQL Global Development Group