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

Re: PL/pgSQL: SELECT INTO EXACT

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Matt Miller <mattm(at)epx(dot)com>
Cc: pgsql-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: PL/pgSQL: SELECT INTO EXACT
Date: 2005-07-30 04:23:57
Message-ID: 200507300423.j6U4NvI22516@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
This has been saved for the 8.2 release:

	http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Matt Miller wrote:
> 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, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-07-30 04:24:25
Subject: Re: PL/pgSQL: SELECT INTO EXACT
Previous:From: Bruce MomjianDate: 2005-07-30 04:20:44
Subject: Updated open items

pgsql-patches by date

Next:From: Bruce MomjianDate: 2005-07-30 04:24:25
Subject: Re: PL/pgSQL: SELECT INTO EXACT
Previous:From: Bruce MomjianDate: 2005-07-30 03:39:43
Subject: Re: AIX FAQ Updates

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