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 (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches