These values will be substituted in the query. A list of actual argument value expressions must appear if and only if the cursor was declared to take arguments. This form of OPEN is used to open a cursor variable whose query was bound to it when it was declared. The comparison value for col1 is inserted via a USING parameter, so it needs no quoting. In this example, the table name is inserted into the query via format(). OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue The SCROLL and NO SCROLL options have the same meanings as for a bound cursor. As with EXECUTE, parameter values can be inserted into the dynamic command via format() and USING. As usual, this gives flexibility so the query plan can vary from one run to the next (see Section 43.11.2), and it also means that variable substitution is not done on the command string.
The query is specified as a string expression, in the same way as in the EXECUTE command. The cursor cannot be open already, and it must have been declared as an unbound cursor variable (that is, as a simple refcursor variable). The cursor variable is opened and given the specified query to execute. OPEN unbound_cursorvar SCROLL ] FOR EXECUTE query_string The implementation of SCROLL assumes that re-reading the query's output will give consistent results, which a volatile function might not do. Also, it is best to use NO SCROLL with a query that involves volatile functions. The SCROLL option cannot be used when the cursor's query uses FOR UPDATE/SHARE. ( key will be replaced by an integer parameter value when the cursor is opened.) The variable curs1 is said to be unbound since it is not bound to any particular query. The actual values to substitute for these names will be specified later, when the cursor is opened.Ĭurs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key Īll three of these variables have the data type refcursor, but the first can be used with any query, while the second has a fully specified query already bound to it, and the last has a parameterized query bound to it. arguments, if specified, is a comma-separated list of pairs name datatype that define names to be replaced by parameter values in the given query. ( FOR can be replaced by IS for Oracle compatibility.) If SCROLL is specified, the cursor will be capable of scrolling backward if NO SCROLL is specified, backward fetches will be rejected if neither specification appears, it is query-dependent whether backward fetches will be allowed. Another way is to use the cursor declaration syntax, which in general is: name SCROLL ] CURSOR FOR query One way to create a cursor variable is just to declare it as a variable of type refcursor. All access to cursors in PL/pgSQL goes through cursor variables, which are always of the special data type refcursor.
0 kommentar(er)
