From e2a8804330a1d3a043fecc49833b47d0937bf7b6 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 1 Apr 2008 03:51:09 +0000 Subject: Support EXECUTE USING in plpgsql. Pavel Stehule, with some improvements by myself. --- doc/src/sgml/plpgsql.sgml | 64 +++++++++++++++++++++++++++++++++++++++++------ 1 file changed, 57 insertions(+), 7 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index f7b94798d87..512cb7657ce 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -1005,20 +1005,23 @@ END; EXECUTE statement is provided: -EXECUTE command-string INTO STRICT target ; +EXECUTE command-string INTO STRICT target USING expression , ... ; where command-string is an expression yielding a string (of type text) containing the - command to be executed and target is a - record variable, row variable, or a comma-separated list of - simple variables and record/row fields. + command to be executed. The optional target + is a record variable, a row variable, or a comma-separated list of + simple variables and record/row fields, into which the results of + the command will be stored. The optional USING expressions + supply values to be inserted into the command. No substitution of PL/pgSQL variables is done on the computed command string. Any required variable values must be inserted - in the command string as it is constructed. + in the command string as it is constructed; or you can use parameters + as described below. @@ -1046,6 +1049,51 @@ EXECUTE command-string INT If the STRICT option is given, an error is reported unless the query produces exactly one row. + + + The command string can use parameter values, which are referenced + in the command as $1, $2, etc. + These symbols refer to values supplied in the USING + clause. This method is often preferable to inserting data values + into the command string as text: it avoids run-time overhead of + converting the values to text and back, and it is much less prone + to SQL-injection attacks since there is no need for quoting or escaping. + An example is: + +EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' + INTO c + USING checked_user, checked_date; + + + Note that parameter symbols can only be used for data values + — if you want to use dynamically determined table or column + names, you must insert them into the command string textually. + For example, if the preceding query needed to be done against a + dynamically selected table, you could do this: + +EXECUTE 'SELECT count(*) FROM ' + || tabname::regclass + || ' WHERE inserted_by = $1 AND inserted <= $2' + INTO c + USING checked_user, checked_date; + + + + + An EXECUTE with a simple constant command string and some + USING parameters, as in the first example above, is + functionally equivalent to just writing the command directly in + PL/pgSQL and allowing replacement of + PL/pgSQL variables to happen automatically. + The important difference is that EXECUTE will re-plan + the command on each execution, generating a plan that is specific + to the current parameter values; whereas + PL/pgSQL normally creates a generic plan + and caches it for re-use. In situations where the best plan depends + strongly on the parameter values, EXECUTE can be + significantly faster; while when the plan is not sensitive to parameter + values, re-planning will be a waste. + SELECT INTO is not currently supported within @@ -1997,7 +2045,7 @@ $$ LANGUAGE plpgsql; rows: <<label>> -FOR target IN EXECUTE text_expression LOOP +FOR target IN EXECUTE text_expression USING expression , ... LOOP statements END LOOP label ; @@ -2006,6 +2054,8 @@ END LOOP label ; on each entry to the FOR loop. This allows the programmer to choose the speed of a preplanned query or the flexibility of a dynamic query, just as with a plain EXECUTE statement. + As with EXECUTE, parameter values can be inserted + into the dynamic command via USING. -- cgit v1.2.3