PL/pgSQL: SELECT INTO EXACT
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."
Attachments:
select_into_exact.patchtext/x-patch; charset=UTF-8; name=select_into_exact.patchDownload
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /var/local/pgcvs/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.75
diff -c -r1.75 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 2 Jul 2005 08:59:47 -0000 1.75
--- doc/src/sgml/plpgsql.sgml 29 Jul 2005 19:19:56 -0000
***************
*** 1067,1073 ****
variable, or list of scalar variables. This is done by:
<synopsis>
! SELECT INTO <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
</synopsis>
where <replaceable>target</replaceable> can be a record variable, a row
--- 1067,1073 ----
variable, or list of scalar variables. This is done by:
<synopsis>
! SELECT INTO <optional>EXACT</optional> <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
</synopsis>
where <replaceable>target</replaceable> can be a record variable, a row
***************
*** 1108,1126 ****
</para>
<para>
! If the query returns zero rows, null values are assigned to the
! target(s). If the query returns multiple rows, the first
! row is assigned to the target(s) and the rest are discarded.
! (Note that <quote>the first row</> is not well-defined unless you've
! used <literal>ORDER BY</>.)
</para>
<para>
! You can check the special <literal>FOUND</literal> variable (see
! <xref linkend="plpgsql-statements-diagnostics">) after a
! <command>SELECT INTO</command> statement to determine whether the
! assignment was successful, that is, at least one row was was returned by
! the query. For example:
<programlisting>
SELECT INTO myrec * FROM emp WHERE empname = myname;
--- 1108,1130 ----
</para>
<para>
! If the <literal>EXACT</literal> option is specified, then
! <replaceable>target</replaceable> will not be set unless the query
! returns exactly one row. If <literal>EXACT</literal> is not
! specified then <replaceable>target</replaceable> will be set
! regardless of the number of rows returned by the query. In the
! non-<literal>EXACT</literal> case, null values are assigned if the
! query returns zero rows, and the first row is assigned if the query
! returns more than 1 row. (Note that <quote>the first row</> is not
! well-defined unless you've used <literal>ORDER BY</>.)
</para>
<para>
! You can check the special <literal>FOUND</literal> variable after a
! <command>SELECT INTO</command> to determine whether the statement was
! successful. A non-<literal>EXACT</literal> query is considered successful
! if any rows are returned, and an <literal>EXACT</literal> query is
! successful only if exactly 1 row is returned. For example:
<programlisting>
SELECT INTO myrec * FROM emp WHERE empname = myname;
***************
*** 1128,1141 ****
RAISE EXCEPTION 'employee % not found', myname;
END IF;
</programlisting>
</para>
<para>
! To test for whether a record/row result is null, you can use the
! <literal>IS NULL</literal> conditional. There is, however, no
! way to tell whether any additional rows might have been
! discarded. Here is an example that handles the case where no
! rows have been returned:
<programlisting>
DECLARE
users_rec RECORD;
--- 1132,1196 ----
RAISE EXCEPTION 'employee % not found', myname;
END IF;
</programlisting>
+
+ <programlisting>
+ SELECT INTO EXACT myrec * FROM emp WHERE empname = myname;
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'employee % not found or not unique', myname;
+ END IF;
+ </programlisting>
+ </para>
+
+ <para>
+ When using the <literal>EXACT</literal> option you can distinguish the
+ not-found case from the not-unique case by using
+ <command>GET DIAGNOSTICS</command> (see
+ <xref linkend="plpgsql-statements-diagnostics">) to retrieve
+ <literal>ROW_COUNT</literal>. After a
+ <command>SELECT INTO EXACT</command> statement
+ <literal>ROW_COUNT</literal> will not necessarily be equal to the total
+ number of rows matching the query, but will be equal to 0, 1, or 2,
+ indicating no matching rows, exactly one matching row, or greater than one
+ matching row, respectively. For example:
+
+ <programlisting>
+ SELECT INTO EXACT myrec * FROM emp WHERE empname = myname;
+ IF NOT FOUND THEN
+ GET DIAGNOSTICS rowcount = ROW_COUNT;
+ IF rowcount == 0 THEN
+ RAISE EXCEPTION 'employee % not found', myname;
+ ELSE
+ RAISE EXCEPTION 'employee % not unique', myname;
+ END IF;
+ END IF;
+ </programlisting>
</para>
<para>
! To test for whether a record/row result is null, you can use the
! <literal>IS NULL</literal> conditional. Keep in mind that a result
! might be null for any of a number of reasons:
! <itemizedlist>
! <listitem>
! <para>
! The query could return a row with a null value.
! </para>
! </listitem>
! <listitem>
! <para>
! A non-<literal>EXACT</literal> query could return no rows and set all targets
! to null.
! </para>
! </listitem>
! <listitem>
! <para>
! An <literal>EXACT</literal> query could return either zero or multiple rows,
! leaving all targets untouched. If a target was null to begin with it will be
! null after the <command>SELECT INTO EXACT</command>
! </para>
! </listitem>
! </itemizedlist>
! Here is an <literal>IS NULL</literal> example:
<programlisting>
DECLARE
users_rec RECORD;
***************
*** 1143,1149 ****
SELECT INTO users_rec * FROM users WHERE user_id=3;
IF users_rec.homepage IS NULL THEN
! -- user entered no homepage, return "http://"
RETURN 'http://';
END IF;
END;
--- 1198,1204 ----
SELECT INTO users_rec * FROM users WHERE user_id=3;
IF users_rec.homepage IS NULL THEN
! -- user entered no homepage or user_id 3 not found, return "http://"
RETURN 'http://';
END IF;
END;
***************
*** 1403,1411 ****
<itemizedlist>
<listitem>
<para>
! A <command>SELECT INTO</command> statement sets
! <literal>FOUND</literal> true if it returns a row, false if no
! row is returned.
</para>
</listitem>
<listitem>
--- 1458,1468 ----
<itemizedlist>
<listitem>
<para>
! A <command>SELECT INTO</command> statement without the
! <literal>EXACT</literal> option sets <literal>FOUND</literal>
! true if it finds at least one row. A
! <command>SELECT INTO EXACT</command> sets
! <literal>FOUND</literal> true if it finds exactly one row.
</para>
</listitem>
<listitem>
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.80
diff -c -r1.80 gram.y
*** src/pl/plpgsql/src/gram.y 2 Jul 2005 17:01:59 -0000 1.80
--- src/pl/plpgsql/src/gram.y 29 Jul 2005 19:43:41 -0000
***************
*** 171,176 ****
--- 171,177 ----
%token K_ELSE
%token K_ELSIF
%token K_END
+ %token K_EXACT
%token K_EXCEPTION
%token K_EXECUTE
%token K_EXIT
***************
*** 1886,1891 ****
--- 1887,1893 ----
PLpgSQL_rec *rec = NULL;
int tok;
bool have_into = false;
+ bool have_exact = false;
plpgsql_dstring_init(&ds);
plpgsql_dstring_append(&ds, "SELECT ");
***************
*** 1913,1918 ****
--- 1915,1925 ----
errmsg("INTO specified more than once")));
}
tok = yylex();
+ if (tok == K_EXACT)
+ {
+ have_exact = true;
+ tok = yylex();
+ }
switch (tok)
{
case T_ROW:
***************
*** 1996,2001 ****
--- 2003,2011 ----
select->row = row;
select->query = expr;
+ if (have_exact)
+ select->exact = true;
+
return (PLpgSQL_stmt *)select;
}
else
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.151
diff -c -r1.151 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 28 Jul 2005 07:51:13 -0000 1.151
--- src/pl/plpgsql/src/pl_exec.c 29 Jul 2005 19:19:56 -0000
***************
*** 1650,1670 ****
/*
* Run the query
*/
! exec_run_select(estate, stmt->query, 1, NULL);
tuptab = estate->eval_tuptable;
n = estate->eval_processed;
/*
! * If the query didn't return any rows, set the target to NULL and
! * return.
*/
! if (n == 0)
{
- exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
exec_eval_cleanup(estate);
return PLPGSQL_RC_OK;
}
/*
* Put the result into the target and set found to true
--- 1650,1683 ----
/*
* Run the query
+ * If SELECT ... INTO EXACT was specified, bring back up to 2 rows to
+ * ensure query was exact
*/
! exec_run_select(estate, stmt->query, stmt->exact ? 2 : 1, NULL);
tuptab = estate->eval_tuptable;
n = estate->eval_processed;
/*
! * If SELECT ... INTO EXACT specified, and the query didn't find exactly
! * 1 row then return. If EXACT was not specified, then allow the query
! * to find any number of rows.
*/
! if (stmt->exact && n != 1)
{
exec_eval_cleanup(estate);
return PLPGSQL_RC_OK;
}
+ else if (n == 0) /* stmt->exact is false */
+ {
+ /*
+ * null the target
+ */
+ exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
+
+ exec_eval_cleanup(estate);
+
+ return PLPGSQL_RC_OK;
+ }
/*
* Put the result into the target and set found to true
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.64
diff -c -r1.64 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h 22 Jun 2005 01:35:02 -0000 1.64
--- src/pl/plpgsql/src/plpgsql.h 29 Jul 2005 19:19:56 -0000
***************
*** 449,454 ****
--- 449,455 ----
{ /* SELECT ... INTO statement */
int cmd_type;
int lineno;
+ bool exact;
PLpgSQL_rec *rec;
PLpgSQL_row *row;
PLpgSQL_expr *query;
Index: src/pl/plpgsql/src/scan.l
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.42
diff -c -r1.42 scan.l
*** src/pl/plpgsql/src/scan.l 26 Jun 2005 19:16:07 -0000 1.42
--- src/pl/plpgsql/src/scan.l 29 Jul 2005 19:19:56 -0000
***************
*** 149,154 ****
--- 149,155 ----
elseif { return K_ELSIF; }
elsif { return K_ELSIF; }
end { return K_END; }
+ exact { return K_EXACT; }
exception { return K_EXCEPTION; }
execute { return K_EXECUTE; }
exit { return K_EXIT; }
Matt Miller <mattm@epx.com> writes:
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.
Uh, what's the point of being only sort-of compatible? Why not throw
the exception?
I dislike the choice of "EXACT", too, as it (a) adds a new reserved word
and (b) doesn't seem to convey quite what is happening anyway. Not sure
about a better word though ... anyone?
regards, tom lane
On 7/29/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matt Miller <mattm@epx.com> writes:
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.Uh, what's the point of being only sort-of compatible? Why not throw
the exception?I dislike the choice of "EXACT", too, as it (a) adds a new reserved word
and (b) doesn't seem to convey quite what is happening anyway. Not sure
about a better word though ... anyone?regards, tom lane
just wonder, why that is not the default behavior of the SELECT INTO?
at least, the first time i think the function was right until i found
that the first row of a set of rows was assigned...
i mean, when you do that code you are expecting just one row from your
query, doesn't you?
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote:
Matt Miller <mattm@epx.com> writes:
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
Uh, what's the point of being only sort-of compatible? Why not throw
the exception?
I guess my hesitation is that the PL/SQL notion of the exception as a
program flow control technique is a bit at odds with the PL/pgSQL notion
of the exception as a transaction control mechanism. Maybe these
notions could be reconciled by a new NOSAVE option to the EXCEPTION
block definition, to suppress the savepoint and the exception-induced
rollback for that BEGIN ... END block. Then an automatically-thrown
exception would not be so expensive.
I dislike the choice of "EXACT", too, as it (a) adds a new
reserved word and (b) doesn't seem to convey quite what is
happening anyway
The motivation is that EXACTly one row must be returned.
Maybe UNIQUE instead of EXACT?
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.
why that is not the default behavior of the SELECT INTO?
...
i mean, when you do that code you are expecting just one row from your
query
I agree. I suppose I was fearful of breaking existing stuff, so I added
a new keyword.
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?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Sorry, patch removed from the queue. I now see the later discussion.
---------------------------------------------------------------------------
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?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote:
Matt Miller <mattm@epx.com> writes:
This patch implements an optional EXACT keyword after the INTO keyword
of the PL/pgSQL SELECT INTO command. ... when SELECTing INTO ...
leave the targets untouched if the query does not
return exactly one row.I dislike the choice of "EXACT", too, as it (a) adds a new reserved word
and (b) doesn't seem to convey quite what is happening anyway. Not sure
about a better word though ... anyone?
I don't know how to avoid adding a keyword, unless the proposed EXACT
behavior just replaces the current behavior, potentially breaking
existing code. Is there a precedent for language-specific GUC vars?
I think the EXACT behavior is more reasonable overall, and maybe a
stepped approach can replace the current behavior with the EXACT flavor.
To that end the option could support either EXACT or NOEXACT, with
NOEXACT initially being the default. Eventually EXACT could become the
default, and finally the NOEXACT option could be dropped altogether. At
that point the EXACT keyword would be dropped as well.
I can attach a patch that supports [EXACT | NOEXACT].
Matt Miller <mattm@epx.com> writes:
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote:
I dislike the choice of "EXACT", too, as it (a) adds a new reserved word
and (b) doesn't seem to convey quite what is happening anyway. Not sure
about a better word though ... anyone?
I can attach a patch that supports [EXACT | NOEXACT].
Somehow, proposing two new reserved words instead of one doesn't seem
very responsive to my gripe :-(.
If you think that this should be a global option instead of a
per-statement one, something like the (undocumented) #option hack might
be a good way to specify it; that would give it per-function scope,
which seems reasonable.
create function myfn(...) returns ... as $$
#option select_into_1_row
declare ...
$$ language plpgsql;
regards, tom lane
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
Matt Miller <mattm@epx.com> writes:
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote:
I dislike the choice of "EXACT", too, as it (a) adds a new reserved word
and (b) doesn't seem to convey quite what is happening anyway. Not sure
about a better word though ... anyone?I can attach a patch that supports [EXACT | NOEXACT].
Somehow, proposing two new reserved words instead of one doesn't seem
very responsive to my gripe :-(.
My intention was to introduce the idea that the current behavior should
be changed, and to then suggest a path that eventually eliminates all
the new reserved words.
If you think that this should be a global option instead of a
per-statement one, something like the (undocumented) #option hack might
be a good way to specify it; that would give it per-function scope,
which seems reasonable.create function myfn(...) returns ... as $$
#option select_into_1_row
declare ...
$$ language plpgsql;
Thanks, I'll take a look at this.
Matt Miller <mattm@epx.com> writes:
I can attach a patch that supports [EXACT | NOEXACT].
Somehow, proposing two new reserved words instead of one doesn't seem
very responsive to my gripe :-(.
My intention was to introduce the idea that the current behavior should
be changed, and to then suggest a path that eventually eliminates all
the new reserved words.
Once you put 'em in, you can't ever really get rid of 'em :-( ... so I'd
prefer to investigate a path that doesn't use that syntax in the first
place.
regards, tom lane
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
If you think that this should be a global option instead of a
per-statement one, something like the (undocumented) #option hack might
be a good way to specify it; that would give it per-function scope,
which seems reasonable.create function myfn(...) returns ... as $$
#option select_into_1_row
declare ...
$$ language plpgsql;
Attached is a patch that implements the "#option select_into_1_row"
directive as suggested.
Is it time to document this directive?
Attachments:
select_into_1_row.patchtext/x-patch; charset=UTF-8; name=select_into_1_row.patchDownload
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.80
diff -c -r1.80 gram.y
*** src/pl/plpgsql/src/gram.y 2 Jul 2005 17:01:59 -0000 1.80
--- src/pl/plpgsql/src/gram.y 8 Aug 2005 22:53:36 -0000
***************
*** 224,229 ****
--- 224,230 ----
%token O_OPTION
%token O_DUMP
+ %token O_SELECT_INTO_1_ROW
%%
***************
*** 249,254 ****
--- 250,259 ----
{
plpgsql_DumpExecTree = true;
}
+ | O_OPTION O_SELECT_INTO_1_ROW
+ {
+ plpgsql_SelectInto1Row = true;
+ }
;
opt_semi :
Index: src/pl/plpgsql/src/pl_comp.c
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.92
diff -c -r1.92 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c 6 Jul 2005 16:42:10 -0000 1.92
--- src/pl/plpgsql/src/pl_comp.c 9 Aug 2005 14:32:43 -0000
***************
*** 78,83 ****
--- 78,84 ----
int plpgsql_error_lineno;
char *plpgsql_error_funcname;
bool plpgsql_DumpExecTree = false;
+ bool plpgsql_SelectInto1Row = false;
bool plpgsql_check_syntax = false;
PLpgSQL_function *plpgsql_curr_compile;
***************
*** 309,314 ****
--- 310,316 ----
plpgsql_ns_init();
plpgsql_ns_push(NULL);
plpgsql_DumpExecTree = false;
+ plpgsql_SelectInto1Row = false;
datums_alloc = 128;
plpgsql_nDatums = 0;
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.151
diff -c -r1.151 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 28 Jul 2005 07:51:13 -0000 1.151
--- src/pl/plpgsql/src/pl_exec.c 9 Aug 2005 14:49:45 -0000
***************
*** 1649,1665 ****
elog(ERROR, "unsupported target");
/*
! * Run the query
*/
! exec_run_select(estate, stmt->query, 1, NULL);
tuptab = estate->eval_tuptable;
n = estate->eval_processed;
/*
! * If the query didn't return any rows, set the target to NULL and
! * return.
*/
! if (n == 0)
{
exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
exec_eval_cleanup(estate);
--- 1649,1672 ----
elog(ERROR, "unsupported target");
/*
! * Run the query, bringing back up to 2 rows if necessary
*/
! exec_run_select(estate, stmt->query, plpgsql_SelectInto1Row ? 2 : 1, NULL);
tuptab = estate->eval_tuptable;
n = estate->eval_processed;
/*
! * If the "#option select_into_1_row" directive was specified, and the query didn't
! * find exactly 1 row, then exit without setting the target. If this directive was
! * not specified then set the target, either to NULL if no rows were found or to
! * the value of the "first" row found.
*/
! if (plpgsql_SelectInto1Row && n != 1)
! {
! exec_eval_cleanup(estate);
! return PLPGSQL_RC_OK;
! }
! else if (n == 0)
{
exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
exec_eval_cleanup(estate);
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.64
diff -c -r1.64 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h 22 Jun 2005 01:35:02 -0000 1.64
--- src/pl/plpgsql/src/plpgsql.h 8 Aug 2005 22:53:36 -0000
***************
*** 644,649 ****
--- 644,650 ----
**********************************************************************/
extern bool plpgsql_DumpExecTree;
+ extern bool plpgsql_SelectInto1Row;
extern bool plpgsql_SpaceScanned;
extern int plpgsql_nDatums;
extern PLpgSQL_datum **plpgsql_Datums;
Index: src/pl/plpgsql/src/scan.l
===================================================================
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.42
diff -c -r1.42 scan.l
*** src/pl/plpgsql/src/scan.l 26 Jun 2005 19:16:07 -0000 1.42
--- src/pl/plpgsql/src/scan.l 8 Aug 2005 22:53:36 -0000
***************
*** 186,191 ****
--- 186,192 ----
^#option { return O_OPTION; }
dump { return O_DUMP; }
+ select_into_1_row { return O_SELECT_INTO_1_ROW; }
/* ----------
On Tue, 2005-08-09 at 15:01 +0000, Matt Miller wrote:
Attached is a patch that implements the "#option select_into_1_row"
directive as suggested.
Is this patch good-to-go? Can it be queued?
Matt Miller wrote:
On Tue, 2005-08-09 at 15:01 +0000, Matt Miller wrote:
Attached is a patch that implements the "#option select_into_1_row"
directive as suggested.Is this patch good-to-go? Can it be queued?
We are in feature freeze, so unless there is overwhelming community
support, it will be held for 8.2.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073This has been saved for the 8.2 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Tom Lane wrote:
Matt Miller <mattm@epx.com> writes:
I can attach a patch that supports [EXACT | NOEXACT].
Somehow, proposing two new reserved words instead of one doesn't seem
very responsive to my gripe :-(.My intention was to introduce the idea that the current behavior should
be changed, and to then suggest a path that eventually eliminates all
the new reserved words.Once you put 'em in, you can't ever really get rid of 'em :-( ... so I'd
prefer to investigate a path that doesn't use that syntax in the first
place.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073This has been saved for the 8.2 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Matt Miller wrote:
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
If you think that this should be a global option instead of a
per-statement one, something like the (undocumented) #option hack might
be a good way to specify it; that would give it per-function scope,
which seems reasonable.create function myfn(...) returns ... as $$
#option select_into_1_row
declare ...
$$ language plpgsql;Attached is a patch that implements the "#option select_into_1_row"
directive as suggested.Is it time to document this directive?
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Fri, 2005-08-12 at 21:53 -0400, Bruce Momjian wrote:
This has been saved for the 8.2 release:
Just to clarify: the "SELECT INTO EXACT" patch was abandoned in favor of
the "#option select_into_1_row" patch. I submitted both patches as part
of the same -patches thread, but the latter solution, the "#option
select_into_1_row" patch, superseded the "SELECT INTO EXACT" idea.
The correct patch is at
http://archives.postgresql.org/pgsql-patches/2005-08/msg00070.php This
should be the only patch that gets applied.
Matt Miller wrote:
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
If you think that this should be a global option instead of a
per-statement one, something like the (undocumented) #option hack might
be a good way to specify it; that would give it per-function scope,
which seems reasonable.create function myfn(...) returns ... as $$
#option select_into_1_row
declare ...
$$ language plpgsql;Attached is a patch that implements the "#option select_into_1_row"
directive as suggested.
Are we agreed this is the way we want to control this?
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Are we agreed this is the way we want to control this?
AFAICT, no one except Matt wants this feature at all, so I'd much prefer
to drop the whole idea. I think it's a insufficiently justified kluge.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Are we agreed this is the way we want to control this?
AFAICT, no one except Matt wants this feature at all, so I'd much prefer
to drop the whole idea. I think it's a insufficiently justified kluge.
Fine. Unless I hear from the non-kludge croud, it is dropped. ;-)
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com
+ If your life is a hard drive, Christ can be your backup. +
I did some work on your patch:
ftp://candle.pha.pa.us/pub/postgresql/mypatches/first
I switched the name of the option flag to FIRST (already a reserved
word), making the default behavior PL/SQL-compatible. I also added the
proper execptions to match PL/SQL. My Oracle 9 PL/SQL manual has for
SELECT INTO:
When you use a SELECT INTO statement without the BULK COLLECT clause, it
should return only one row. If it returns more than one row, PL/SQL
raises the predefined exception TOO_MANY_ROWS.
However, if no rows are returned, PL/SQL raises NO_DATA_FOUND unless the
SELECT statement called a SQL aggregate function such as AVG or SUM.
(SQL aggregate functions always return a value or a null. So, a SELECT
INTO statement that calls an aggregate function never raises
NO_DATA_FOUND.)
The big problem is that a lot of applications use the SELECT INTO ... IF
NOT FOUND test, and I don't see any good way to keep those applications
working without being modified.
The #option keyword seems as bad as just giving up on being PL/SQL
compatibile and using the keyword STRICT (already a reserved word) when
you want PL/SQL functionality.
I don't think a GUC is going to work because it will affect all
functions stored in the database, and their might be functions expecting
different behaviors. Setting the GUC in the function that needs it also
will not work because it will spill into functions called by that
function.
I think we set up SELECT INTO this way originally because we didn't have
execeptions, but now that we have them, I don't see a clean way to move
to the PL/SQL behavior. Perhaps STRICT is the best option.
Comments?
---------------------------------------------------------------------------
Matt Miller wrote:
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
Matt Miller <mattm@epx.com> writes:
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote:
I dislike the choice of "EXACT", too, as it (a) adds a new reserved word
and (b) doesn't seem to convey quite what is happening anyway. Not sure
about a better word though ... anyone?I can attach a patch that supports [EXACT | NOEXACT].
Somehow, proposing two new reserved words instead of one doesn't seem
very responsive to my gripe :-(.My intention was to introduce the idea that the current behavior should
be changed, and to then suggest a path that eventually eliminates all
the new reserved words.If you think that this should be a global option instead of a
per-statement one, something like the (undocumented) #option hack might
be a good way to specify it; that would give it per-function scope,
which seems reasonable.create function myfn(...) returns ... as $$
#option select_into_1_row
declare ...
$$ language plpgsql;Thanks, I'll take a look at this.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
I have update the patch at:
ftp://candle.pha.pa.us/pub/postgresql/mypatches/strict
I re-did it to use STRICT for Oracle PL/SQL syntax. I don't think we
are going to be able to do any better than that, even in future
versions. I added documentation that should help too.
---------------------------------------------------------------------------
Bruce Momjian wrote:
I did some work on your patch:
ftp://candle.pha.pa.us/pub/postgresql/mypatches/first
I switched the name of the option flag to FIRST (already a reserved
word), making the default behavior PL/SQL-compatible. I also added the
proper execptions to match PL/SQL. My Oracle 9 PL/SQL manual has for
SELECT INTO:When you use a SELECT INTO statement without the BULK COLLECT clause, it
should return only one row. If it returns more than one row, PL/SQL
raises the predefined exception TOO_MANY_ROWS.However, if no rows are returned, PL/SQL raises NO_DATA_FOUND unless the
SELECT statement called a SQL aggregate function such as AVG or SUM.
(SQL aggregate functions always return a value or a null. So, a SELECT
INTO statement that calls an aggregate function never raises
NO_DATA_FOUND.)The big problem is that a lot of applications use the SELECT INTO ... IF
NOT FOUND test, and I don't see any good way to keep those applications
working without being modified.The #option keyword seems as bad as just giving up on being PL/SQL
compatibile and using the keyword STRICT (already a reserved word) when
you want PL/SQL functionality.I don't think a GUC is going to work because it will affect all
functions stored in the database, and their might be functions expecting
different behaviors. Setting the GUC in the function that needs it also
will not work because it will spill into functions called by that
function.I think we set up SELECT INTO this way originally because we didn't have
execeptions, but now that we have them, I don't see a clean way to move
to the PL/SQL behavior. Perhaps STRICT is the best option.Comments?
---------------------------------------------------------------------------
Matt Miller wrote:
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
Matt Miller <mattm@epx.com> writes:
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote:
I dislike the choice of "EXACT", too, as it (a) adds a new reserved word
and (b) doesn't seem to convey quite what is happening anyway. Not sure
about a better word though ... anyone?I can attach a patch that supports [EXACT | NOEXACT].
Somehow, proposing two new reserved words instead of one doesn't seem
very responsive to my gripe :-(.My intention was to introduce the idea that the current behavior should
be changed, and to then suggest a path that eventually eliminates all
the new reserved words.If you think that this should be a global option instead of a
per-statement one, something like the (undocumented) #option hack might
be a good way to specify it; that would give it per-function scope,
which seems reasonable.create function myfn(...) returns ... as $$
#option select_into_1_row
declare ...
$$ language plpgsql;Thanks, I'll take a look at this.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Patch applied. Thanks.
---------------------------------------------------------------------------
Bruce Momjian wrote:
I have update the patch at:
ftp://candle.pha.pa.us/pub/postgresql/mypatches/strict
I re-did it to use STRICT for Oracle PL/SQL syntax. I don't think we
are going to be able to do any better than that, even in future
versions. I added documentation that should help too.---------------------------------------------------------------------------
Bruce Momjian wrote:
I did some work on your patch:
ftp://candle.pha.pa.us/pub/postgresql/mypatches/first
I switched the name of the option flag to FIRST (already a reserved
word), making the default behavior PL/SQL-compatible. I also added the
proper execptions to match PL/SQL. My Oracle 9 PL/SQL manual has for
SELECT INTO:When you use a SELECT INTO statement without the BULK COLLECT clause, it
should return only one row. If it returns more than one row, PL/SQL
raises the predefined exception TOO_MANY_ROWS.However, if no rows are returned, PL/SQL raises NO_DATA_FOUND unless the
SELECT statement called a SQL aggregate function such as AVG or SUM.
(SQL aggregate functions always return a value or a null. So, a SELECT
INTO statement that calls an aggregate function never raises
NO_DATA_FOUND.)The big problem is that a lot of applications use the SELECT INTO ... IF
NOT FOUND test, and I don't see any good way to keep those applications
working without being modified.The #option keyword seems as bad as just giving up on being PL/SQL
compatibile and using the keyword STRICT (already a reserved word) when
you want PL/SQL functionality.I don't think a GUC is going to work because it will affect all
functions stored in the database, and their might be functions expecting
different behaviors. Setting the GUC in the function that needs it also
will not work because it will spill into functions called by that
function.I think we set up SELECT INTO this way originally because we didn't have
execeptions, but now that we have them, I don't see a clean way to move
to the PL/SQL behavior. Perhaps STRICT is the best option.Comments?
---------------------------------------------------------------------------
Matt Miller wrote:
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
Matt Miller <mattm@epx.com> writes:
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote:
I dislike the choice of "EXACT", too, as it (a) adds a new reserved word
and (b) doesn't seem to convey quite what is happening anyway. Not sure
about a better word though ... anyone?I can attach a patch that supports [EXACT | NOEXACT].
Somehow, proposing two new reserved words instead of one doesn't seem
very responsive to my gripe :-(.My intention was to introduce the idea that the current behavior should
be changed, and to then suggest a path that eventually eliminates all
the new reserved words.If you think that this should be a global option instead of a
per-statement one, something like the (undocumented) #option hack might
be a good way to specify it; that would give it per-function scope,
which seems reasonable.create function myfn(...) returns ... as $$
#option select_into_1_row
declare ...
$$ language plpgsql;Thanks, I'll take a look at this.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +