From 309cd7cf183d7e194205d8d97186c09ef67f83ae Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 19 Jan 2010 01:35:31 +0000 Subject: Add "USING expressions" option to plpgsql's OPEN cursor FOR EXECUTE. This is the last EXECUTE-like plpgsql statement that was missing the capability of inserting parameter values via USING. Pavel Stehule, reviewed by Itagaki Takahiro --- doc/src/sgml/plpgsql.sgml | 21 +++++++++++++-------- 1 file changed, 13 insertions(+), 8 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 493e96e8662..af8e372fe83 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 @@ -1016,7 +1016,7 @@ END; EXECUTE statement is provided: -EXECUTE command-string INTO STRICT target USING expression , ... ; +EXECUTE command-string INTO STRICT target USING expression , ... ; where command-string is an expression @@ -1500,7 +1500,7 @@ RETURN expression; RETURN NEXT expression; RETURN QUERY query; -RETURN QUERY EXECUTE command-string USING expression , ... ; +RETURN QUERY EXECUTE command-string USING expression , ... ; @@ -2190,7 +2190,7 @@ $$ LANGUAGE plpgsql; rows: <<label>> -FOR target IN EXECUTE text_expression USING expression , ... LOOP +FOR target IN EXECUTE text_expression USING expression , ... LOOP statements END LOOP label ; @@ -2495,7 +2495,7 @@ OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey; <command>OPEN FOR EXECUTE</command> -OPEN unbound_cursorvar NO SCROLL FOR EXECUTE query_string; +OPEN unbound_cursorvar NO SCROLL FOR EXECUTE query_string USING expression , ... ; @@ -2507,7 +2507,8 @@ OPEN unbound_cursorvar NO ), and it also means that variable substitution is not done on the - command string. + command string. As with EXECUTE, parameter values + can be inserted into the dynamic command via USING. The SCROLL and NO SCROLL options have the same meanings as for a bound cursor. @@ -2516,8 +2517,12 @@ OPEN unbound_cursorvar NO An example: -OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1); +OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname) ' WHERE col1 = $1' USING keyvalue; + In this example, the table name is inserted into the query textually, + so use of quote_ident() is recommended to guard against + SQL injection. The comparison value for col1 is inserted + via a USING parameter, so it needs no quoting. @@ -2893,7 +2898,7 @@ END LOOP label ; raise errors. -RAISE level 'format' , expression , ... USING option = expression , ... ; +RAISE level 'format' , expression , ... USING option = expression , ... ; RAISE level condition_name USING option = expression , ... ; RAISE level SQLSTATE 'sqlstate' USING option = expression , ... ; RAISE level USING option = expression , ... ; -- cgit v1.2.3