Hello
This patch allows dynamic queries in RETURN QUERY statement.
http://archives.postgresql.org/pgsql-hackers/2008-02/msg01180.php
Sample:
CREATE OR REPLACE FUNCTION test(tab varchar, b integer)
RETURNS SETOF integer AS $$
BEGIN
RETURN QUERY
EXECUTE 'SELECT a FROM ' || tab 'WHERE b = $1'
USING b;
RETURN;
END;
$$ LANGUAGE plpgsql;
Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-04-05 12:19:24.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml 2008-04-05 12:32:51.000000000 +0200
***************
*** 1451,1457 ****
</sect3>
<sect3>
! <title><command>RETURN NEXT</> and <command>RETURN QUERY</command></title>
<indexterm>
<primary>RETURN NEXT</primary>
<secondary>in PL/PgSQL</secondary>
--- 1451,1457 ----
</sect3>
<sect3>
! <title><command>RETURN NEXT</>, <command>RETURN QUERY</command>, <command>RETURN QUERY EXECUTE</command></title>
<indexterm>
<primary>RETURN NEXT</primary>
<secondary>in PL/PgSQL</secondary>
***************
*** 1460,1469 ****
--- 1460,1474 ----
<primary>RETURN QUERY</primary>
<secondary>in PL/PgSQL</secondary>
</indexterm>
+ <indexterm>
+ <primary>RETURN QUERY EXECUTE</primary>
+ <secondary>in PL/PgSQL</secondary>
+ </indexterm>
<synopsis>
RETURN NEXT <replaceable>expression</replaceable>;
RETURN QUERY <replaceable>query</replaceable>;
+ RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
</synopsis>
<para>
***************
*** 1471,1477 ****
<literal>SETOF <replaceable>sometype</></literal>, the procedure
to follow is slightly different. In that case, the individual
items to return are specified by a sequence of <command>RETURN
! NEXT</command> or <command>RETURN QUERY</command> commands, and
then a final <command>RETURN</command> command with no argument
is used to indicate that the function has finished executing.
<command>RETURN NEXT</command> can be used with both scalar and
--- 1476,1483 ----
<literal>SETOF <replaceable>sometype</></literal>, the procedure
to follow is slightly different. In that case, the individual
items to return are specified by a sequence of <command>RETURN
! NEXT</command>, <command>RETURN QUERY</command> commands, or
! <command>RETURN QUERY EXECUTE</command> and
then a final <command>RETURN</command> command with no argument
is used to indicate that the function has finished executing.
<command>RETURN NEXT</command> can be used with both scalar and
***************
*** 1479,1497 ****
<quote>table</quote> of results will be returned.
<command>RETURN QUERY</command> appends the results of executing
a query to the function's result set. <command>RETURN
! NEXT</command> and <command>RETURN QUERY</command> can be freely
intermixed in a single set-returning function, in which case
their results will be concatenated.
</para>
<para>
! <command>RETURN NEXT</command> and <command>RETURN
! QUERY</command> do not actually return from the function —
they simply append zero or more rows to the function's result
set. Execution then continues with the next statement in the
<application>PL/pgSQL</> function. As successive
<command>RETURN NEXT</command> or <command>RETURN
! QUERY</command> commands are executed, the result set is built
up. A final <command>RETURN</command>, which should have no
argument, causes control to exit the function (or you can just
let control reach the end of the function).
--- 1485,1506 ----
<quote>table</quote> of results will be returned.
<command>RETURN QUERY</command> appends the results of executing
a query to the function's result set. <command>RETURN
! NEXT</command>, <command>RETURN QUERY</command> and
! <command>RETURN QUERY EXECUTE</command> can be freely
intermixed in a single set-returning function, in which case
their results will be concatenated.
</para>
<para>
! <command>RETURN NEXT</command>, <command>RETURN
! QUERY</command> and <command>RETURN QUERY EXECUTE</command>
! do not actually return from the function —
they simply append zero or more rows to the function's result
set. Execution then continues with the next statement in the
<application>PL/pgSQL</> function. As successive
<command>RETURN NEXT</command> or <command>RETURN
! QUERY</command> or <command>RETURN QUERY EXECUTE</command>
! commands are executed, the result set is built
up. A final <command>RETURN</command>, which should have no
argument, causes control to exit the function (or you can just
let control reach the end of the function).
***************
*** 1538,1553 ****
SELECT * FROM getallfoo();
</programlisting>
! Note that functions using <command>RETURN NEXT</command> or
! <command>RETURN QUERY</command> must be called as a table source in
! a <literal>FROM</literal> clause.
!
</para>
<note>
<para>
! The current implementation of <command>RETURN NEXT</command>
! and <command>RETURN QUERY</command> stores the entire result set
before returning from the function, as discussed above. That
means that if a <application>PL/pgSQL</> function produces a
very large result set, performance might be poor: data will be
--- 1547,1562 ----
SELECT * FROM getallfoo();
</programlisting>
! Note that functions using <command>RETURN NEXT</command>,
! <command>RETURN QUERY</command> or <command>RETURN QUERY EXECUTE</command>
! must be called as a table source in a <literal>FROM</literal> clause.
</para>
<note>
<para>
! The current implementation of <command>RETURN NEXT</command>,
! <command>RETURN QUERY</command> and <command>RETURN QUERY EXECUTE
! </command> stores the entire result set
before returning from the function, as discussed above. That
means that if a <application>PL/pgSQL</> function produces a
very large result set, performance might be poor: data will be
*** ./src/pl/plpgsql/src/gram.y.orig 2008-04-05 10:42:03.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y 2008-04-05 11:39:21.000000000 +0200
***************
*** 2375,2380 ****
--- 2375,2382 ----
static PLpgSQL_stmt *
make_return_query_stmt(int lineno)
{
+ int tok;
+
PLpgSQL_stmt_return_query *new;
if (!plpgsql_curr_compile->fn_retset)
***************
*** 2383,2390 ****
new = palloc0(sizeof(PLpgSQL_stmt_return_query));
new->cmd_type = PLPGSQL_STMT_RETURN_QUERY;
new->lineno = lineno;
- new->query = read_sql_stmt("");
return (PLpgSQL_stmt *) new;
}
--- 2385,2415 ----
new = palloc0(sizeof(PLpgSQL_stmt_return_query));
new->cmd_type = PLPGSQL_STMT_RETURN_QUERY;
new->lineno = lineno;
+ /* check RETURN QUERY EXECUTE USING */
+ if ((tok = yylex()) != K_EXECUTE)
+ {
+ plpgsql_push_back_token(tok);
+ new->query = read_sql_stmt("");
+ }
+ else
+ {
+ int term;
+ /* Dynamic SQL: RETURN QUERY EXECUTE USING */
+ new->dynquery = read_sql_expression2(';',
+ K_USING, "; or USING", &term);
+
+ if (term == K_USING)
+ {
+ do
+ {
+ PLpgSQL_expr *expr;
+
+ expr = read_sql_expression2(',', ';', ", or ;", &term);
+ new->params = lappend(new->params, expr);
+ } while (term == ',');
+ }
+ }
return (PLpgSQL_stmt *) new;
}
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2008-04-05 10:54:53.000000000 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2008-04-05 11:44:20.000000000 +0200
***************
*** 192,197 ****
--- 192,199 ----
static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *estate,
List *params);
static void free_params_data(PreparedParamsData *ppd);
+ static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate,
+ PLpgSQL_expr *query, List *params);
/* ----------
***************
*** 2213,2219 ****
if (estate->tuple_store == NULL)
exec_init_tuple_store(estate);
! exec_run_select(estate, stmt->query, 0, &portal);
if (!compatible_tupdesc(estate->rettupdesc, portal->tupDesc))
ereport(ERROR,
--- 2215,2228 ----
if (estate->tuple_store == NULL)
exec_init_tuple_store(estate);
! /* if not RETURN QUERY EXECUTE */
! if (stmt->query != NULL)
! exec_run_select(estate, stmt->query, 0, &portal);
! else
! {
! Assert(stmt->dynquery != NULL);
! portal = exec_dynquery_with_params(estate, stmt->dynquery, stmt->params);
! }
if (!compatible_tupdesc(estate->rettupdesc, portal->tupDesc))
ereport(ERROR,
***************
*** 2881,2890 ****
static int
exec_stmt_dynfors(PLpgSQL_execstate *estate, PLpgSQL_stmt_dynfors *stmt)
{
- Datum query;
- bool isnull;
- Oid restype;
- char *querystr;
PLpgSQL_rec *rec = NULL;
PLpgSQL_row *row = NULL;
SPITupleTable *tuptab;
--- 2890,2895 ----
***************
*** 2902,2952 ****
else
elog(ERROR, "unsupported target");
- /*
- * Evaluate the string expression after the EXECUTE keyword. It's result
- * is the querystring we have to execute.
- */
- query = exec_eval_expr(estate, stmt->query, &isnull, &restype);
- if (isnull)
- ereport(ERROR,
- (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
- errmsg("cannot EXECUTE a null querystring")));
-
- /* Get the C-String representation */
- querystr = convert_value_to_string(query, restype);
! exec_eval_cleanup(estate);
!
! /*
! * Open an implicit cursor for the query. We use SPI_cursor_open_with_args
! * even when there are no params, because this avoids making and freeing
! * one copy of the plan.
! */
! if (stmt->params)
! {
! PreparedParamsData *ppd;
!
! ppd = exec_eval_using_params(estate, stmt->params);
! portal = SPI_cursor_open_with_args(NULL,
! querystr,
! ppd->nargs, ppd->types,
! ppd->values, ppd->nulls,
! estate->readonly_func, 0);
! free_params_data(ppd);
! }
! else
! {
! portal = SPI_cursor_open_with_args(NULL,
! querystr,
! 0, NULL,
! NULL, NULL,
! estate->readonly_func, 0);
! }
!
! if (portal == NULL)
! elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
! pfree(querystr);
/*
* Fetch the initial 10 tuples
--- 2907,2914 ----
else
elog(ERROR, "unsupported target");
! portal = exec_dynquery_with_params(estate, stmt->query, stmt->params);
/*
* Fetch the initial 10 tuples
***************
*** 5223,5225 ****
--- 5185,5249 ----
pfree(ppd);
}
+
+ /*
+ * Open portal for dynamic query
+ */
+ static Portal
+ exec_dynquery_with_params(PLpgSQL_execstate *estate, PLpgSQL_expr *dynquery, List *params)
+ {
+ Datum query;
+ bool isnull;
+ Oid restype;
+ char *querystr;
+ Portal portal;
+
+
+ /*
+ * Evaluate the string expression after the EXECUTE keyword. It's result
+ * is the querystring we have to execute.
+ */
+ query = exec_eval_expr(estate, dynquery, &isnull, &restype);
+ if (isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("cannot EXECUTE a null querystring")));
+
+ /* Get the C-String representation */
+ querystr = convert_value_to_string(query, restype);
+
+ exec_eval_cleanup(estate);
+
+ /*
+ * Open an implicit cursor for the query. We use SPI_cursor_open_with_args
+ * even when there are no params, because this avoids making and freeing
+ * one copy of the plan.
+ */
+ if (params)
+ {
+ PreparedParamsData *ppd;
+
+ ppd = exec_eval_using_params(estate, params);
+ portal = SPI_cursor_open_with_args(NULL,
+ querystr,
+ ppd->nargs, ppd->types,
+ ppd->values, ppd->nulls,
+ estate->readonly_func, 0);
+ free_params_data(ppd);
+ }
+ else
+ {
+ portal = SPI_cursor_open_with_args(NULL,
+ querystr,
+ 0, NULL,
+ NULL, NULL,
+ estate->readonly_func, 0);
+ }
+
+ if (portal == NULL)
+ elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
+ querystr, SPI_result_code_string(SPI_result));
+ pfree(querystr);
+
+ return portal;
+ }
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2008-04-05 10:54:58.000000000 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c 2008-04-05 11:56:06.000000000 +0200
***************
*** 972,980 ****
dump_return_query(PLpgSQL_stmt_return_query *stmt)
{
dump_ind();
! printf("RETURN QUERY ");
! dump_expr(stmt->query);
! printf("\n");
}
static void
--- 972,1008 ----
dump_return_query(PLpgSQL_stmt_return_query *stmt)
{
dump_ind();
! if (stmt->query)
! {
! printf("RETURN QUERY ");
! dump_expr(stmt->query);
! printf("\n");
! }
! else
! {
! printf("RETURN QUERY EXECUTE ");
! dump_expr(stmt->dynquery);
! printf("\n");
! if (stmt->params != NIL)
! {
! ListCell *lc;
! int i;
!
! dump_indent += 2;
! dump_ind();
! printf(" USING\n");
! dump_indent += 2;
! i = 1;
! foreach(lc, stmt->params)
! {
! dump_ind();
! printf(" parameter $%d: ", i++);
! dump_expr((PLpgSQL_expr *) lfirst(lc));
! printf("\n");
! }
! dump_indent -= 4;
! }
! }
}
static void
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2008-04-05 10:54:47.000000000 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2008-04-05 11:39:08.000000000 +0200
***************
*** 519,524 ****
--- 519,526 ----
int cmd_type;
int lineno;
PLpgSQL_expr *query;
+ PLpgSQL_expr *dynquery; /* RETURN QUERY EXECUTE expression */
+ List *params; /* USING arguments */
} PLpgSQL_stmt_return_query;
typedef struct
*** ./src/test/regress/expected/plpgsql.out.orig 2008-04-05 12:06:59.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out 2008-04-05 12:05:51.000000000 +0200
***************
*** 3251,3253 ****
--- 3251,3272 ----
drop function case_test(int);
drop function catch();
+ -- return query execute
+ create or replace function return_dquery()
+ returns setof int as $$
+ begin
+ return query select * from (values(10),(20)) f;
+ return query execute 'select * from (values($1),($2)) f' using 40,50;
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_dquery();
+ return_dquery
+ ---------------
+ 10
+ 20
+ 40
+ 50
+ (4 rows)
+
+ drop function return_dquery();
*** ./src/test/regress/sql/plpgsql.sql.orig 2008-04-05 12:01:18.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2008-04-05 12:04:53.000000000 +0200
***************
*** 2647,2649 ****
--- 2647,2662 ----
drop function case_test(int);
drop function catch();
+ -- return query execute
+ create or replace function return_dquery()
+ returns setof int as $$
+ begin
+ return query select * from (values(10),(20)) f;
+ return query execute 'select * from (values($1),($2)) f' using 40,50;
+ return;
+ end;
+ $$ language plpgsql;
+
+ select * from return_dquery();
+
+ drop function return_dquery();
--
Sent via pgsql-patches mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
