Index: doc/src/sgml/plpgsql.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.8 diff -c -r1.8 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 21 Sep 2002 18:32:53 -0000 1.8 --- doc/src/sgml/plpgsql.sgml 8 Nov 2002 02:37:23 -0000 *************** *** 102,108 **** If you execute the above function, it will reference the OID for my_function() in the query plan produced for ! the PERFORM statement. Later, if you drop and re-create my_function(), then populate() will not be able to find my_function() anymore. You would then have to --- 102,108 ---- If you execute the above function, it will reference the OID for my_function() in the query plan produced for ! the PERFORM statement. Later, if you drop and re-create my_function(), then populate() will not be able to find my_function() anymore. You would then have to *************** *** 117,133 **** same tables and fields on every execution; that is, you cannot use a parameter as the name of a table or field in a query. To get around this restriction, you can construct dynamic queries using ! the PL/pgSQL EXECUTE statement --- at ! the price of constructing a new query plan on every execution. ! The PL/pgSQL EXECUTE statement is not ! related to the EXECUTE statement supported by the PostgreSQL backend. The backend ! EXECUTE statement cannot be used within PL/pgSQL functions (and ! is not needed). --- 117,135 ---- same tables and fields on every execution; that is, you cannot use a parameter as the name of a table or field in a query. To get around this restriction, you can construct dynamic queries using ! the PL/pgSQL EXECUTE ! statement --- at the price of constructing a new query plan on ! every execution. ! The PL/pgSQL ! EXECUTE statement is not related to the ! EXECUTE statement supported by the PostgreSQL backend. The backend ! EXECUTE statement cannot be used within ! PL/pgSQL functions (and is not needed). *************** *** 173,185 **** ! That means that your client application must send each ! query to the database server, wait for it to process it, ! receive the results, do some computation, then send ! other queries to the server. All this incurs inter-process communication ! and may also incur network ! overhead if your client is on a different machine than ! the database server. --- 175,186 ---- ! That means that your client application must send each query to ! the database server, wait for it to process it, receive the ! results, do some computation, then send other queries to the ! server. All this incurs inter-process communication and may also ! incur network overhead if your client is on a different machine ! than the database server. *************** *** 753,766 **** The mutable nature of record variables presents a problem in this ! connection. When fields of a record variable are used in expressions or ! statements, the data types of the ! fields must not change between calls of one and the same expression, ! since the expression will be planned using the data type that is present ! when the expression is first reached. ! Keep this in mind when writing trigger procedures that handle events ! for more than one table. (EXECUTE can be used to get around this ! problem when necessary.) --- 754,767 ---- The mutable nature of record variables presents a problem in this ! connection. When fields of a record variable are used in ! expressions or statements, the data types of the fields must not ! change between calls of one and the same expression, since the ! expression will be planned using the data type that is present ! when the expression is first reached. Keep this in mind when ! writing trigger procedures that handle events for more than one ! table. (EXECUTE can be used to get around ! this problem when necessary.) *************** *** 904,913 **** Executing an expression or query with no result ! Sometimes one wishes to evaluate an expression or query but discard ! the result (typically because one is calling a function that has ! useful side-effects but no useful result value). To do this in ! PL/pgSQL, use the PERFORM statement: PERFORM query; --- 905,915 ---- Executing an expression or query with no result ! Sometimes one wishes to evaluate an expression or query but ! discard the result (typically because one is calling a function ! that has useful side-effects but no useful result value). To do ! this in PL/pgSQL, use the ! PERFORM statement: PERFORM query; *************** *** 922,932 **** ! ! One might expect that SELECT with no INTO clause would accomplish ! this result, but at present the only accepted way to do it is PERFORM. ! ! An example: --- 924,935 ---- ! ! One might expect that SELECT with no INTO ! clause would accomplish this result, but at present the only ! accepted way to do it is PERFORM. ! ! An example: *************** *** 940,952 **** Executing dynamic queries ! Oftentimes you will want to generate dynamic queries inside ! your PL/pgSQL functions, that is, ! queries that will involve different tables or different data types ! each time they are executed. PL/pgSQL's normal attempts to cache plans for queries will not work in such ! scenarios. To handle this sort of problem, the EXECUTE statement ! is provided: EXECUTE query-string; --- 943,955 ---- Executing dynamic queries ! Oftentimes you will want to generate dynamic queries inside your ! PL/pgSQL functions, that is, queries ! that will involve different tables or different data types each ! time they are executed. PL/pgSQL's normal attempts to cache plans for queries will not work in such ! scenarios. To handle this sort of problem, the ! EXECUTE statement is provided: EXECUTE query-string; *************** *** 973,992 **** Unlike all other queries in PL/pgSQL, a ! query run by an EXECUTE statement is ! not prepared and saved just once during the life of the server. ! Instead, the query is prepared each ! time the statement is run. The ! query-string can be dynamically ! created within the procedure to perform actions on variable ! tables and fields. ! The results from SELECT queries are discarded by EXECUTE, and ! SELECT INTO is not currently supported within EXECUTE. So, the ! only way to extract a result from a dynamically-created SELECT is ! to use the FOR-IN-EXECUTE form described later. --- 976,997 ---- Unlike all other queries in PL/pgSQL, a ! query run by an ! EXECUTE statement is not prepared and saved ! just once during the life of the server. Instead, the ! query is prepared each time the ! statement is run. The query-string can ! be dynamically created within the procedure to perform actions on ! variable tables and fields. ! The results from SELECT queries are discarded ! by EXECUTE, and SELECT INTO ! is not currently supported within EXECUTE. ! So, the only way to extract a result from a dynamically-created ! SELECT is to use the FOR-IN-EXECUTE form ! described later. *************** *** 1017,1023 **** ! Here is a much larger example of a dynamic query and EXECUTE: CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS ' DECLARE --- 1022,1029 ---- ! Here is a much larger example of a dynamic query and ! EXECUTE: CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS ' DECLARE *************** *** 1159,1167 **** RETURN expression; ! RETURN with an expression is used to return from a ! PL/pgSQL function that does not return a set. ! The function terminates and the value of expression is returned to the caller. --- 1165,1173 ---- RETURN expression; ! RETURN with an expression is used to return ! from a PL/pgSQL function that does not return a ! set. The function terminates and the value of expression is returned to the caller. *************** *** 1176,1197 **** ! The return value of a function cannot be left undefined. If control ! reaches the end of the top-level block of ! the function without hitting a RETURN statement, a run-time error ! will occur. When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual ! items to return are specified in RETURN NEXT commands, and then a ! final RETURN command with no arguments is used to indicate that ! the function has finished executing. RETURN NEXT can be used with ! both scalar and composite data types; in the later case, an ! entire "table" of results will be returned. Functions that use ! RETURN NEXT should be called in the following fashion: SELECT * FROM some_func(); --- 1182,1205 ---- ! The return value of a function cannot be left undefined. If ! control reaches the end of the top-level block of the function ! without hitting a RETURN statement, a run-time ! error will occur. When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual ! items to return are specified in RETURN NEXT ! commands, and then a final RETURN command with ! no arguments is used to indicate that the function has finished ! executing. RETURN NEXT can be used with both ! scalar and composite data types; in the later case, an entire ! "table" of results will be returned. Functions that use ! RETURN NEXT should be called in the following ! fashion: SELECT * FROM some_func(); *************** *** 1203,1221 **** RETURN NEXT expression; ! RETURN NEXT does not actually return from the function; it simply ! saves away the value of the expression (or record or row variable, ! as appropriate for the data type being returned). ! Execution then continues with the next statement in the ! PL/pgSQL function. As successive RETURN NEXT ! commands are executed, the result set is built up. A final ! RETURN, which need have no argument, causes control to exit ! the function. ! The current implementation of RETURN NEXT for PL/pgSQL stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, --- 1211,1229 ---- RETURN NEXT expression; ! RETURN NEXT does not actually return from the ! function; it simply saves away the value of the expression (or ! record or row variable, as appropriate for the data type being ! returned). Execution then continues with the next statement in ! the PL/pgSQL function. As successive ! RETURN NEXT commands are executed, the result ! set is built up. A final RETURN, which need ! have no argument, causes control to exit the function. ! The current implementation of RETURN NEXT for PL/pgSQL stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, *************** *** 1586,1597 **** statements END LOOP; ! This is like the previous form, except that the source SELECT ! statement is specified as a string expression, which is evaluated ! and re-planned on each entry to the FOR loop. This allows the ! programmer to choose the speed of a pre-planned query or the ! flexibility of a dynamic query, just as with a plain EXECUTE ! statement. --- 1594,1605 ---- statements END LOOP; ! This is like the previous form, except that the source ! SELECT statement is specified as a string ! expression, which is evaluated and re-planned on each entry to ! the FOR loop. This allows the programmer to choose the speed of ! a pre-planned query or the flexibility of a dynamic query, just ! as with a plain EXECUTE statement. *************** *** 1700,1717 **** OPEN FOR EXECUTE ! OPEN unbound-cursor FOR EXECUTE query-string; ! The cursor variable is opened and given the specified query ! to execute. The cursor cannot be open already, and it must ! have been declared as an unbound cursor (that is, as a simple ! refcursor variable). The query is specified as a ! string expression in the same way as in the EXECUTE command. ! As usual, this gives flexibility so the query can vary ! from one run to the next. OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1); --- 1708,1725 ---- OPEN FOR EXECUTE ! OPEN unbound-cursor FOR EXECUTE query-string; ! The cursor variable is opened and given the specified query to ! execute. The cursor cannot be open already, and it must have been ! declared as an unbound cursor (that is, as a simple ! refcursor variable). The query is specified as a string ! expression in the same way as in the EXECUTE ! command. As usual, this gives flexibility so the query can vary ! from one run to the next. OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1); *************** *** 1722,1740 **** Opening a bound cursor ! OPEN bound-cursor ( argument_values ) ; ! This form of OPEN is used to open a cursor variable whose query ! was bound to it when it was declared. ! The cursor cannot be open already. A list of actual argument ! value expressions must appear if and only if the cursor was ! declared to take arguments. These values will be substituted ! in the query. ! The query plan for a bound cursor is always considered ! cacheable --- there is no equivalent of EXECUTE in this case. OPEN curs2; --- 1730,1747 ---- Opening a bound cursor ! OPEN bound-cursor ( argument_values ) ; ! This form of OPEN is used to open a cursor ! variable whose query was bound to it when it was declared. The ! cursor cannot be open already. A list of actual argument value ! expressions must appear if and only if the cursor was declared to ! take arguments. These values will be substituted in the query. ! The query plan for a bound cursor is always considered cacheable ! --- there is no equivalent of EXECUTE in this case. OPEN curs2; *************** *** 1771,1786 **** FETCH ! FETCH cursor INTO target; ! FETCH retrieves the next row from the cursor into a target, ! which may be a row variable, a record variable, or a comma-separated ! list of simple variables, just like SELECT INTO. As with ! SELECT INTO, the special variable FOUND may be ! checked to see whether a row was obtained or not. FETCH curs1 INTO rowvar; --- 1778,1794 ---- FETCH ! FETCH cursor INTO target; ! FETCH retrieves the next row from the ! cursor into a target, which may be a row variable, a record ! variable, or a comma-separated list of simple variables, just like ! SELECT INTO. As with SELECT ! INTO, the special variable FOUND may ! be checked to see whether a row was obtained or not. FETCH curs1 INTO rowvar; Index: src/pl/plpgsql/src/gram.y =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/pl/plpgsql/src/gram.y,v retrieving revision 1.39 diff -c -r1.39 gram.y *** src/pl/plpgsql/src/gram.y 1 Nov 2002 22:52:34 -0000 1.39 --- src/pl/plpgsql/src/gram.y 7 Nov 2002 21:41:19 -0000 *************** *** 728,741 **** stmt_perform : K_PERFORM lno expr_until_semi { ! PLpgSQL_stmt_assign *new; ! new = malloc(sizeof(PLpgSQL_stmt_assign)); ! memset(new, 0, sizeof(PLpgSQL_stmt_assign)); ! new->cmd_type = PLPGSQL_STMT_ASSIGN; new->lineno = $2; - new->varno = -1; new->expr = $3; $$ = (PLpgSQL_stmt *)new; --- 728,740 ---- stmt_perform : K_PERFORM lno expr_until_semi { ! PLpgSQL_stmt_perform *new; ! new = malloc(sizeof(PLpgSQL_stmt_perform)); ! memset(new, 0, sizeof(PLpgSQL_stmt_perform)); ! new->cmd_type = PLPGSQL_STMT_PERFORM; new->lineno = $2; new->expr = $3; $$ = (PLpgSQL_stmt *)new; Index: src/pl/plpgsql/src/pl_exec.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/pl/plpgsql/src/pl_exec.c,v retrieving revision 1.65 diff -c -r1.65 pl_exec.c *** src/pl/plpgsql/src/pl_exec.c 19 Oct 2002 22:10:58 -0000 1.65 --- src/pl/plpgsql/src/pl_exec.c 8 Nov 2002 02:45:28 -0000 *************** *** 73,78 **** --- 73,80 ---- PLpgSQL_stmt * stmt); static int exec_stmt_assign(PLpgSQL_execstate * estate, PLpgSQL_stmt_assign * stmt); + static int exec_stmt_perform(PLpgSQL_execstate * estate, + PLpgSQL_stmt_perform * stmt); static int exec_stmt_getdiag(PLpgSQL_execstate * estate, PLpgSQL_stmt_getdiag * stmt); static int exec_stmt_if(PLpgSQL_execstate * estate, *************** *** 890,895 **** --- 892,901 ---- rc = exec_stmt_assign(estate, (PLpgSQL_stmt_assign *) stmt); break; + case PLPGSQL_STMT_PERFORM: + rc = exec_stmt_perform(estate, (PLpgSQL_stmt_perform *) stmt); + break; + case PLPGSQL_STMT_GETDIAG: rc = exec_stmt_getdiag(estate, (PLpgSQL_stmt_getdiag *) stmt); break; *************** *** 973,1015 **** /* ---------- * exec_stmt_assign Evaluate an expression and * put the result into a variable. - * - * For no very good reason, this is also used for PERFORM statements. * ---------- */ static int exec_stmt_assign(PLpgSQL_execstate * estate, PLpgSQL_stmt_assign * stmt) { ! PLpgSQL_expr *expr = stmt->expr; ! if (stmt->varno >= 0) ! exec_assign_expr(estate, estate->datums[stmt->varno], expr); ! else ! { ! /* ! * PERFORM: evaluate query and discard result (but set FOUND ! * depending on whether at least one row was returned). ! * ! * This cannot share code with the assignment case since we do not ! * wish to constrain the discarded result to be only one ! * row/column. ! */ ! int rc; ! /* ! * If not already done create a plan for this expression ! */ ! if (expr->plan == NULL) ! exec_prepare_plan(estate, expr); ! rc = exec_run_select(estate, expr, 0, NULL); ! if (rc != SPI_OK_SELECT) ! elog(ERROR, "query \"%s\" didn't return data", expr->query); ! exec_set_found(estate, (estate->eval_processed != 0)); ! exec_eval_cleanup(estate); ! } return PLPGSQL_RC_OK; } --- 979,1021 ---- /* ---------- * exec_stmt_assign Evaluate an expression and * put the result into a variable. * ---------- */ static int exec_stmt_assign(PLpgSQL_execstate * estate, PLpgSQL_stmt_assign * stmt) { ! Assert(stmt->varno >= 0); ! exec_assign_expr(estate, estate->datums[stmt->varno], stmt->expr); ! return PLPGSQL_RC_OK; ! } ! /* ---------- ! * exec_stmt_perform Evaluate query and discard result (but set ! * FOUND depending on whether at least one row ! * was returned). ! * ---------- ! */ ! static int ! exec_stmt_perform(PLpgSQL_execstate * estate, PLpgSQL_stmt_perform * stmt) ! { ! PLpgSQL_expr *expr = stmt->expr; ! int rc; ! /* ! * If not already done create a plan for this expression ! */ ! if (expr->plan == NULL) ! exec_prepare_plan(estate, expr); ! ! rc = exec_run_select(estate, expr, 0, NULL); ! if (rc != SPI_OK_SELECT) ! elog(ERROR, "query \"%s\" didn't return data", expr->query); ! exec_set_found(estate, (estate->eval_processed != 0)); ! ! exec_eval_cleanup(estate); return PLPGSQL_RC_OK; } *************** *** 1579,1590 **** return PLPGSQL_RC_RETURN; } ! /* ! * Notes: ! * - the tuple store must be created in a sufficiently long-lived ! * memory context, as the same store must be used within the executor ! * after the PL/PgSQL call returns. At present, the code uses ! * TopTransactionContext. */ static int exec_stmt_return_next(PLpgSQL_execstate * estate, --- 1585,1595 ---- return PLPGSQL_RC_RETURN; } ! /* ---------- ! * exec_stmt_return_next Evaluate an expression and add it to the ! * list of tuples returned by the current ! * SRF. ! * ---------- */ static int exec_stmt_return_next(PLpgSQL_execstate * estate, *************** *** 1731,1737 **** estate->rettupdesc = rsi->expectedDesc; } - /* ---------- * exec_stmt_raise Build a message and throw it with --- 1736,1741 ---- Index: src/pl/plpgsql/src/plpgsql.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/pl/plpgsql/src/plpgsql.h,v retrieving revision 1.28 diff -c -r1.28 plpgsql.h *** src/pl/plpgsql/src/plpgsql.h 12 Sep 2002 00:24:09 -0000 1.28 --- src/pl/plpgsql/src/plpgsql.h 7 Nov 2002 21:41:16 -0000 *************** *** 100,106 **** PLPGSQL_STMT_GETDIAG, PLPGSQL_STMT_OPEN, PLPGSQL_STMT_FETCH, ! PLPGSQL_STMT_CLOSE }; --- 100,107 ---- PLPGSQL_STMT_GETDIAG, PLPGSQL_STMT_OPEN, PLPGSQL_STMT_FETCH, ! PLPGSQL_STMT_CLOSE, ! PLPGSQL_STMT_PERFORM }; *************** *** 288,293 **** --- 289,300 ---- PLpgSQL_expr *expr; } PLpgSQL_stmt_assign; + typedef struct + { /* PERFORM statement */ + int cmd_type; + int lineno; + PLpgSQL_expr *expr; + } PLpgSQL_stmt_perform; typedef struct { /* Get Diagnostics item */ Index: src/test/regress/expected/plpgsql.out =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/plpgsql.out,v retrieving revision 1.11 diff -c -r1.11 plpgsql.out *** src/test/regress/expected/plpgsql.out 1 Sep 2002 16:28:06 -0000 1.11 --- src/test/regress/expected/plpgsql.out 8 Nov 2002 02:43:25 -0000 *************** *** 1733,1735 **** --- 1733,1786 ---- 50 | 5 | xxx (1 row) + -- + -- test PERFORM + -- + create table perform_test ( + a INT, + b INT + ); + create function simple_func(int) returns boolean as ' + BEGIN + IF $1 < 20 THEN + INSERT INTO perform_test VALUES ($1, $1 + 10); + RETURN TRUE; + ELSE + RETURN FALSE; + END IF; + END;' language 'plpgsql'; + create function perform_test_func() returns void as ' + BEGIN + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + PERFORM simple_func(5); + + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + PERFORM simple_func(50); + + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + RETURN; + END;' language 'plpgsql'; + SELECT perform_test_func(); + perform_test_func + ------------------- + + (1 row) + + SELECT * FROM perform_test; + a | b + -----+----- + 5 | 15 + 100 | 100 + 100 | 100 + (3 rows) + + drop table perform_test; Index: src/test/regress/sql/plpgsql.sql =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/plpgsql.sql,v retrieving revision 1.10 diff -c -r1.10 plpgsql.sql *** src/test/regress/sql/plpgsql.sql 1 Sep 2002 16:28:06 -0000 1.10 --- src/test/regress/sql/plpgsql.sql 8 Nov 2002 02:39:53 -0000 *************** *** 1559,1561 **** --- 1559,1606 ---- SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int); SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text); + + -- + -- test PERFORM + -- + + create table perform_test ( + a INT, + b INT + ); + + create function simple_func(int) returns boolean as ' + BEGIN + IF $1 < 20 THEN + INSERT INTO perform_test VALUES ($1, $1 + 10); + RETURN TRUE; + ELSE + RETURN FALSE; + END IF; + END;' language 'plpgsql'; + + create function perform_test_func() returns void as ' + BEGIN + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + PERFORM simple_func(5); + + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + PERFORM simple_func(50); + + IF FOUND then + INSERT INTO perform_test VALUES (100, 100); + END IF; + + RETURN; + END;' language 'plpgsql'; + + SELECT perform_test_func(); + SELECT * FROM perform_test; + + drop table perform_test; \ No newline at end of file