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?
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; }
/* ----------
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
