![]() ![]() ![]() Omitting direction is the same as specifying NEXT. The direction clause can be any of the variants allowed in the SQL FETCH command except the ones that can fetch more than one row namely, it can be NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, or BACKWARD. As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not. If there is no next row, the target is set to NULL(s). For example, another way to get the same effect as the curs3 example above isĬurs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key įETCH cursor INTO target įETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. In either case the value to be passed is determined at the time of the OPEN. However, only variables declared before the bound cursor was declared will be substituted into it. Similar to calling functions, described in Section 4.3, it is also allowed to mix positional and named notation.Įxamples (these use the cursor declaration examples above):īecause variable substitution is done on a bound cursor's query, there are really two ways to pass values into the cursor: either with an explicit argument to OPEN, or implicitly by referencing a PL/pgSQL variable in the query. In named notation, each argument's name is specified using := to separate it from the argument expression. In positional notation, all arguments are specified in order. Notice that SCROLL and NO SCROLL cannot be specified in OPEN, as the cursor's scrolling behavior was already determined.Īrgument values can be passed using either positional or named notation. The query plan for a bound cursor is always considered cacheable there is no equivalent of EXECUTE in this case. 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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |