diff options
| -rw-r--r-- | doc/src/sgml/config.sgml | 6 | ||||
| -rw-r--r-- | doc/src/sgml/plpgsql.sgml | 385 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/gram.y | 22 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_comp.c | 12 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_handler.c | 24 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_scanner.c | 6 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/plpgsql.h | 10 | ||||
| -rw-r--r-- | src/test/regress/expected/plpgsql.out | 38 | ||||
| -rw-r--r-- | src/test/regress/sql/plpgsql.sql | 28 |
9 files changed, 316 insertions, 215 deletions
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 2de93e956c6..e4a79782ae3 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.232 2009/10/21 20:38:58 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.233 2009/11/13 22:43:39 tgl Exp $ --> <chapter Id="runtime-config"> <title>Server Configuration</title> @@ -5162,8 +5162,8 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' when using custom variables: <programlisting> -custom_variable_classes = 'plr,plperl' -plr.path = '/usr/lib/R' +custom_variable_classes = 'plpgsql,plperl' +plpgsql.variable_conflict = use_variable plperl.use_strict = true plruby.use_strict = true # generates error: unknown class name </programlisting> diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 72558041461..6a3c8fa404b 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.146 2009/11/10 14:22:45 momjian Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.147 2009/11/13 22:43:39 tgl Exp $ --> <chapter id="plpgsql"> <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title> @@ -217,11 +217,11 @@ END <optional> <replaceable>label</replaceable> </optional>; </para> <para> - There are two types of comments in <application>PL/pgSQL</>. A double - dash (<literal>--</literal>) starts a comment that extends to the end of - the line. A <literal>/*</literal> starts a block comment that extends to - the next occurrence of <literal>*/</literal>. Block comments nest, - just as in ordinary SQL. + Comments work the same way in <application>PL/pgSQL</> code as in + ordinary SQL. A double dash (<literal>--</literal>) starts a comment + that extends to the end of the line. A <literal>/*</literal> starts a + block comment that extends to the matching occurrence of + <literal>*/</literal>. Block comments nest. </para> <para> @@ -327,8 +327,9 @@ arow RECORD; to the variable when the block is entered. If the <literal>DEFAULT</> clause is not given then the variable is initialized to the <acronym>SQL</acronym> null value. - The <literal>CONSTANT</> option prevents the variable from being assigned to, - so that its value remains constant for the duration of the block. + The <literal>CONSTANT</> option prevents the variable from being + assigned to, so that its value will remain constant for the duration of + the block. If <literal>NOT NULL</> is specified, an assignment of a null value results in a run-time error. All variables declared as <literal>NOT NULL</> @@ -727,7 +728,7 @@ SELECT <replaceable>expression</replaceable> <programlisting> IF x < y THEN ... </programlisting> - what happens behind the scenes is + what happens behind the scenes is equivalent to <programlisting> PREPARE <replaceable>statement_name</>(integer, integer) AS SELECT $1 < $2; </programlisting> @@ -761,13 +762,17 @@ PREPARE <replaceable>statement_name</>(integer, integer) AS SELECT $1 < $2; <para> An assignment of a value to a <application>PL/pgSQL</application> - variable or row/record field is written as: + variable is written as: <synopsis> <replaceable>variable</replaceable> := <replaceable>expression</replaceable>; </synopsis> - As explained above, the expression in such a statement is evaluated + As explained previously, the expression in such a statement is evaluated by means of an SQL <command>SELECT</> command sent to the main - database engine. The expression must yield a single value. + database engine. The expression must yield a single value (possibly + a row value, if the variable is a row or record variable). The target + variable can be a simple variable (optionally qualified with a block + name), a field of a row or record variable, or an element of an array + that is a simple variable or field. </para> <para> @@ -802,38 +807,12 @@ my_record.user_id := 20; <para> Any <application>PL/pgSQL</application> variable name appearing - in the command text is replaced by a parameter symbol, and then the + in the command text is treated as a parameter, and then the current value of the variable is provided as the parameter value at run time. This is exactly like the processing described earlier for expressions; for details see <xref linkend="plpgsql-var-subst">. - As an example, if you write: -<programlisting> -DECLARE - key TEXT; - delta INTEGER; -BEGIN - ... - UPDATE mytab SET val = val + delta WHERE id = key; -</programlisting> - the command text seen by the main SQL engine will look like: -<programlisting> - UPDATE mytab SET val = val + $1 WHERE id = $2; -</programlisting> - Although you don't normally have to think about this, it's helpful - to know it when you need to make sense of syntax-error messages. </para> - <caution> - <para> - <application>PL/pgSQL</application> will substitute for any identifier - matching one of the function's declared variables; it is not bright - enough to know whether that's what you meant! Thus, it is a bad idea - to use a variable name that is the same as any table, column, or - function name that you need to reference in commands within the - function. For more discussion see <xref linkend="plpgsql-var-subst">. - </para> - </caution> - <para> When executing a SQL command in this way, <application>PL/pgSQL</application> plans the command just once @@ -940,7 +919,7 @@ DELETE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRIC <para> If a row or a variable list is used as target, the query's result columns must exactly match the structure of the target as to number and data - types, or a run-time error + types, or else a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the query result columns. </para> @@ -1089,7 +1068,9 @@ EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= INTO c USING checked_user, checked_date; </programlisting> + </para> + <para> 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. @@ -1102,6 +1083,11 @@ EXECUTE 'SELECT count(*) FROM ' INTO c USING checked_user, checked_date; </programlisting> + Another restriction on parameter symbols is that they only work in + <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>, and + <command>DELETE</> commands. In other statement + types (generically called utility statements), you must insert + values textually even if they are just data values. </para> <para> @@ -1303,6 +1289,7 @@ GET DIAGNOSTICS integer_var = ROW_COUNT; type <type>boolean</type>. <literal>FOUND</literal> starts out false within each <application>PL/pgSQL</application> function call. It is set by each of the following types of statements: + <itemizedlist> <listitem> <para> @@ -1363,11 +1350,17 @@ GET DIAGNOSTICS integer_var = ROW_COUNT; </listitem> </itemizedlist> + Other <application>PL/pgSQL</application> statements do not change + the state of <literal>FOUND</literal>. + Note in particular that <command>EXECUTE</command> + changes the output of <command>GET DIAGNOSTICS</command>, but + does not change <literal>FOUND</literal>. + </para> + + <para> <literal>FOUND</literal> is a local variable within each <application>PL/pgSQL</application> function; any changes to it - affect only the current function. <literal>EXECUTE</literal> - changes the output of <command>GET DIAGNOSTICS</command>, but - does not change the state of <literal>FOUND</literal>. + affect only the current function. </para> </sect2> @@ -1450,7 +1443,7 @@ RETURN <replaceable>expression</replaceable>; <command>RETURN</command> with an expression terminates the function and returns the value of <replaceable>expression</replaceable> to the caller. This form - is to be used for <application>PL/pgSQL</> functions that do + is used for <application>PL/pgSQL</> functions that do not return a set. </para> @@ -2699,7 +2692,7 @@ DELETE FROM <replaceable>table</replaceable> WHERE CURRENT OF <replaceable>curso or deleted using the cursor to identify the row. There are restrictions on what the cursor's query can be (in particular, no grouping) and it's best to use <literal>FOR UPDATE</> in the - cursor. For additional information see the + cursor. For more information see the <xref linkend="sql-declare" endterm="sql-declare-title"> reference page. </para> @@ -3173,9 +3166,10 @@ RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id; <listitem> <para> Data type array of <type>text</type>; the arguments from - the <command>CREATE TRIGGER</command> statement. + the <command>CREATE TRIGGER</command> statement. The index counts from 0. Invalid - indices (less than 0 or greater than or equal to <varname>tg_nargs</>) result in a null value. + indexes (less than 0 or greater than or equal to <varname>tg_nargs</>) + result in a null value. </para> </listitem> </varlistentry> @@ -3485,139 +3479,170 @@ SELECT * FROM sales_summary_bytime; <title>Variable Substitution</title> <para> - When <application>PL/pgSQL</> prepares a SQL statement or expression - for execution, any <application>PL/pgSQL</application> variable name - appearing in the statement or expression is replaced by a parameter symbol, - <literal>$<replaceable>n</replaceable></literal>. The current value - of the variable is then provided as the value for the parameter whenever - the statement or expression is executed. As an example, consider the - function + SQL statements and expressions within a <application>PL/pgSQL</> function + can refer to variables and parameters of the function. Behind the scenes, + <application>PL/pgSQL</> substitutes query parameters for such references. + Parameters will only be substituted in places where a parameter or + column reference is syntactically allowed. As an extreme case, consider + this example of poor programming style: <programlisting> -CREATE FUNCTION logfunc(logtxt text) RETURNS void AS $$ - DECLARE - curtime timestamp := now(); - BEGIN - INSERT INTO logtable VALUES (logtxt, curtime); - END; -$$ LANGUAGE plpgsql; -</programlisting> - The <command>INSERT</> statement will effectively be processed as -<programlisting> -PREPARE <replaceable>statement_name</>(text, timestamp) AS - INSERT INTO logtable VALUES ($1, $2); + INSERT INTO foo (foo) VALUES (foo); </programlisting> - followed on each execution by <command>EXECUTE</> with the current - actual values of the two variables. (Note: here we are speaking of - the main SQL engine's - <xref linkend="sql-execute" endterm="sql-execute-title"> command, - not <application>PL/pgSQL</application>'s <command>EXECUTE</>.) + The first occurrence of <literal>foo</> must syntactically be a table + name, so it will not be substituted, even if the function has a variable + named <literal>foo</>. The second occurrence must be the name of a + column of the table, so it will not be substituted either. Only the + third occurrence is a candidate to be a reference to the function's + variable. </para> + <note> + <para> + <productname>PostgreSQL</productname> versions before 8.5 would try + to substitute the variable in all three cases, leading to syntax errors. + </para> + </note> + <para> - <emphasis>The substitution mechanism will replace any token that matches a - known variable's name.</> This poses various traps for the unwary. - For example, it is a bad idea - to use a variable name that is the same as any table or column name - that you need to reference in queries within the function, because - what you think is a table or column name will still get replaced. - In the above example, suppose that <structname>logtable</> has - column names <structfield>logtxt</> and <structfield>logtime</>, - and we try to write the <command>INSERT</> as -<programlisting> - INSERT INTO logtable (logtxt, logtime) VALUES (logtxt, curtime); -</programlisting> - This will be fed to the main SQL parser as + Since the names of variables are syntactically no different from the names + of table columns, there can be ambiguity in statements that also refer to + tables: is a given name meant to refer to a table column, or a variable? + Let's change the previous example to <programlisting> - INSERT INTO logtable ($1, logtime) VALUES ($1, $2); + INSERT INTO dest (col) SELECT foo + bar FROM src; </programlisting> - resulting in a syntax error like this: -<screen> -ERROR: syntax error at or near "$1" -LINE 1: INSERT INTO logtable ( $1 , logtime) VALUES ( $1 , $2 ) - ^ -QUERY: INSERT INTO logtable ( $1 , logtime) VALUES ( $1 , $2 ) -CONTEXT: SQL statement in PL/PgSQL function "logfunc2" near line 5 -</screen> + Here, <literal>dest</> and <literal>src</> must be table names, and + <literal>col</> must be a column of <literal>dest</>, but <literal>foo</> + and <literal>bar</> might reasonably be either variables of the function + or columns of <literal>src</>. </para> <para> - This example is fairly easy to diagnose, since it leads to an - obvious syntax error. Much nastier are cases where the substitution - is syntactically permissible, since the only symptom may be misbehavior - of the function. In one case, a user wrote something like this: -<programlisting> - DECLARE - val text; - search_key integer; - BEGIN - ... - FOR val IN SELECT val FROM table WHERE key = search_key LOOP ... -</programlisting> - and wondered why all his table entries seemed to be NULL. Of course - what happened here was that the query became -<programlisting> - SELECT $1 FROM table WHERE key = $2 -</programlisting> - and thus it was just an expensive way of assigning <literal>val</>'s - current value back to itself for each row. + By default, <application>PL/pgSQL</> will report an error if a name + in a SQL statement could refer to either a variable or a table column. + You can fix such a problem by renaming the variable or column, + or by qualifying the ambiguous reference, or by telling + <application>PL/pgSQL</> which interpretation to prefer. </para> <para> - A commonly used coding rule for avoiding such traps is to use a + The simplest solution is to rename the variable or column. + A common coding rule is to use a different naming convention for <application>PL/pgSQL</application> - variables than you use for table and column names. For example, - if all your variables are named + variables than you use for column names. For example, + if you consistently name function variables <literal>v_<replaceable>something</></literal> while none of your - table or column names start with <literal>v_</>, you're pretty safe. + column names start with <literal>v_</>, no conflicts will occur. </para> <para> - Another workaround is to use qualified (dotted) names for SQL entities. - For instance we could safely have written the above example as + Alternatively you can qualify ambiguous references to make them clear. + In the above example, <literal>src.foo</> would be an unambiguous reference + to the table column. To create an unambiguous reference to a variable, + declare it in a labeled block and use the block's label + (see <xref linkend="plpgsql-structure">). For example, <programlisting> - FOR val IN SELECT table.val FROM table WHERE key = search_key LOOP ... + <<block>> + DECLARE + foo int; + BEGIN + foo := ...; + INSERT INTO dest (col) SELECT block.foo + bar FROM src; </programlisting> - because <application>PL/pgSQL</application> will not substitute a - variable for a trailing component of a qualified name. - However this solution does not work in every case — you can't - qualify a name in an <command>INSERT</>'s column name list, for instance. - Another point is that record and row variable names will be matched to - the first components of qualified names, so a qualified SQL name is - still vulnerable in some cases. - In such cases choosing a non-conflicting variable name is the only way. + Here <literal>block.foo</> means the variable even if there is a column + <literal>foo</> in <literal>src</>. Function parameters, as well as + special variables such as <literal>FOUND</>, can be qualified by the + function's name, because they are implicitly declared in an outer block + labeled with the function's name. </para> <para> - Another technique you can use is to attach a label to the block in - which your variables are declared, and then qualify the variable names - in your SQL commands (see <xref linkend="plpgsql-structure">). - For example, + Sometimes it is impractical to fix all the ambiguous references in a + large body of <application>PL/pgSQL</> code. In such cases you can + specify that <application>PL/pgSQL</> should resolve ambiguous references + as the variable (which is compatible with <application>PL/pgSQL</>'s + behavior before <productname>PostgreSQL</productname> 8.5), or as the + table column (which is compatible with some other systems such as + <productname>Oracle</productname>). + </para> + + <indexterm> + <primary><varname>plpgsql.variable_conflict</> configuration parameter</primary> + </indexterm> + + <para> + To change this behavior on a system-wide basis, set the configuration + parameter <literal>plpgsql.variable_conflict</> to one of + <literal>error</>, <literal>use_variable</>, or + <literal>use_column</> (where <literal>error</> is the factory default). + This parameter affects subsequent compilations + of statements in <application>PL/pgSQL</> functions, but not statements + already compiled in the current session. To set the parameter before + <application>PL/pgSQL</> has been loaded, it is necessary to have added + <quote><literal>plpgsql</></> to the <xref + linkend="guc-custom-variable-classes"> list in + <filename>postgresql.conf</filename>. Because changing this setting + can cause unexpected changes in the behavior of <application>PL/pgSQL</> + functions, it can only be changed by a superuser. + </para> + + <para> + You can also set the behavior on a function-by-function basis, by + inserting one of these special commands at the start of the function + text: +<programlisting> +#variable_conflict error +#variable_conflict use_variable +#variable_conflict use_column +</programlisting> + These commands affect only the function they are written in, and override + the setting of <literal>plpgsql.variable_conflict</>. An example is <programlisting> - <<pl>> +CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ + #variable_conflict use_variable DECLARE - val text; + curtime timestamp := now(); BEGIN - ... - UPDATE table SET col = pl.val WHERE ... + UPDATE users SET last_modified = curtime, comment = comment + WHERE users.id = id; + END; +$$ LANGUAGE plpgsql; +</programlisting> + In the <literal>UPDATE</> command, <literal>curtime</>, <literal>comment</>, + and <literal>id</> will refer to the function's variable and parameters + whether or not <literal>users</> has columns of those names. Notice + that we had to qualify the reference to <literal>users.id</> in the + <literal>WHERE</> clause to make it refer to the table column. + But we did not have to qualify the reference to <literal>comment</> + as a target in the <literal>UPDATE</> list, because syntactically + that must be a column of <literal>users</>. We could write the same + function without depending on the <literal>variable_conflict</> setting + in this way: +<programlisting> +CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ + <<fn>> + DECLARE + curtime timestamp := now(); + BEGIN + UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment + WHERE users.id = stamp_user.id; + END; +$$ LANGUAGE plpgsql; </programlisting> - This is not in itself a solution to the problem of conflicts, - since an unqualified name in a SQL command is still at risk of being - interpreted the <quote>wrong</> way. But it is useful for clarifying - the intent of potentially-ambiguous code. </para> <para> Variable substitution does not happen in the command string given to <command>EXECUTE</> or one of its variants. If you need to insert a varying value into such a command, do so as part of - constructing the string value, as illustrated in + constructing the string value, or use <literal>USING</>, as illustrated in <xref linkend="plpgsql-statements-executing-dyn">. </para> <para> Variable substitution currently works only in <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>, and <command>DELETE</> commands, - because the main SQL engine allows parameter symbols only in these + because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the utility statement as a string and <command>EXECUTE</> it. @@ -3660,47 +3685,17 @@ CONTEXT: SQL statement in PL/PgSQL function "logfunc2" near line 5 </para> <para> - Once <application>PL/pgSQL</> has made an execution plan for a particular - command in a function, it will reuse that plan for the life of the - database connection. This is usually a win for performance, but it - can cause some problems if you dynamically - alter your database schema. For example: - -<programlisting> -CREATE FUNCTION populate() RETURNS integer AS $$ -DECLARE - -- declarations -BEGIN - PERFORM my_function(); -END; -$$ LANGUAGE plpgsql; -</programlisting> - - If you execute the above function, it will reference the OID for - <function>my_function()</function> in the execution plan produced for - the <command>PERFORM</command> statement. Later, if you - drop and recreate <function>my_function()</function>, then - <function>populate()</function> will not be able to find - <function>my_function()</function> anymore. You would then have to - start a new database session so that <function>populate()</function> - will be compiled afresh, before it will work again. You can avoid - this problem by using <command>CREATE OR REPLACE FUNCTION</command> - when updating the definition of - <function>my_function</function>, since when a function is - <quote>replaced</quote>, its OID is not changed. + A saved plan will be re-planned automatically if there is any schema + change to any table used in the query, or if any user-defined function + used in the query is redefined. This makes the re-use of prepared plans + transparent in most cases, but there are corner cases where a stale plan + might be re-used. An example is that dropping and re-creating a + user-defined operator won't affect already-cached plans; they'll continue + to call the original operator's underlying function, if that has not been + changed. When necessary, the cache can be flushed by starting a fresh + database session. </para> - <note> - <para> - In <productname>PostgreSQL</productname> 8.3 and later, saved plans - will be replaced whenever any schema changes have occurred to any - tables they reference. This eliminates one of the major disadvantages - of saved plans. However, there is no such mechanism for function - references, and thus the above example involving a reference to a - deleted function is still valid. - </para> - </note> - <para> Because <application>PL/pgSQL</application> saves execution plans in this way, SQL commands that appear directly in a @@ -4048,26 +4043,17 @@ a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ <itemizedlist> <listitem> <para> - There are no default values for parameters in <productname>PostgreSQL</>. - </para> - </listitem> - - <listitem> - <para> - You can overload function names in <productname>PostgreSQL</>. This is - often used to work around the lack of default parameters. - </para> - </listitem> - - <listitem> - <para> If a name used in a SQL command could be either a column name of a table or a reference to a variable of the function, - <application>PL/SQL</> treats it as a column name, while - <application>PL/pgSQL</> treats it as a variable name. It's best - to avoid such ambiguities in the first place, but if necessary you - can fix them by properly qualifying the ambiguous name. - (See <xref linkend="plpgsql-var-subst">.) + <application>PL/SQL</> treats it as a column name. This corresponds + to <application>PL/pgSQL</>'s + <literal>plpgsql.variable_conflict</> = <literal>use_column</> + behavior, which is not the default, + as explained in <xref linkend="plpgsql-var-subst">. + It's often best to avoid such ambiguities in the first place, + but if you have to port a large amount of code that depends on + this behavior, setting <literal>variable_conflict</> may be the + best solution. </para> </listitem> @@ -4537,7 +4523,8 @@ $$ LANGUAGE plpgsql; The exception names supported by <application>PL/pgSQL</> are different from Oracle's. The set of built-in exception names is much larger (see <xref linkend="errcodes-appendix">). There - is not currently a way to declare user-defined exception names. + is not currently a way to declare user-defined exception names, + although you can throw user-chosen SQLSTATE values instead. </para> </callout> </calloutlist> diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y index 6edd01c4d8d..08d7b9162d3 100644 --- a/src/pl/plpgsql/src/gram.y +++ b/src/pl/plpgsql/src/gram.y @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.135 2009/11/12 00:13:00 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.136 2009/11/13 22:43:40 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -256,6 +256,7 @@ static List *read_raise_options(void); %token <keyword> K_ELSIF %token <keyword> K_END %token <keyword> K_ERRCODE +%token <keyword> K_ERROR %token <keyword> K_EXCEPTION %token <keyword> K_EXECUTE %token <keyword> K_EXIT @@ -301,7 +302,10 @@ static List *read_raise_options(void); %token <keyword> K_THEN %token <keyword> K_TO %token <keyword> K_TYPE +%token <keyword> K_USE_COLUMN +%token <keyword> K_USE_VARIABLE %token <keyword> K_USING +%token <keyword> K_VARIABLE_CONFLICT %token <keyword> K_WARNING %token <keyword> K_WHEN %token <keyword> K_WHILE @@ -322,6 +326,18 @@ comp_option : '#' K_OPTION K_DUMP { plpgsql_DumpExecTree = true; } + | '#' K_VARIABLE_CONFLICT K_ERROR + { + plpgsql_curr_compile->resolve_option = PLPGSQL_RESOLVE_ERROR; + } + | '#' K_VARIABLE_CONFLICT K_USE_VARIABLE + { + plpgsql_curr_compile->resolve_option = PLPGSQL_RESOLVE_VARIABLE; + } + | '#' K_VARIABLE_CONFLICT K_USE_COLUMN + { + plpgsql_curr_compile->resolve_option = PLPGSQL_RESOLVE_COLUMN; + } ; opt_semi : @@ -1969,6 +1985,7 @@ unreserved_keyword : | K_DETAIL | K_DUMP | K_ERRCODE + | K_ERROR | K_FIRST | K_FORWARD | K_HINT @@ -1991,6 +2008,9 @@ unreserved_keyword : | K_SCROLL | K_SQLSTATE | K_TYPE + | K_USE_COLUMN + | K_USE_VARIABLE + | K_VARIABLE_CONFLICT | K_WARNING ; diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c index bc0cde6c767..6cfc5195fac 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.145 2009/11/12 00:13:00 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.146 2009/11/13 22:43:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -351,7 +351,7 @@ do_compile(FunctionCallInfo fcinfo, function->fn_is_trigger = is_trigger; function->fn_cxt = func_cxt; function->out_param_varno = -1; /* set up for no OUT param */ - function->resolve_option = PLPGSQL_RESOLVE_BEFORE; + function->resolve_option = plpgsql_variable_conflict; /* * Initialize the compiler, particularly the namespace stack. The @@ -782,7 +782,7 @@ plpgsql_compile_inline(char *proc_source) function->fn_is_trigger = false; function->fn_cxt = func_cxt; function->out_param_varno = -1; /* set up for no OUT param */ - function->resolve_option = PLPGSQL_RESOLVE_BEFORE; + function->resolve_option = plpgsql_variable_conflict; plpgsql_ns_init(); plpgsql_ns_push(func_name); @@ -948,7 +948,7 @@ plpgsql_pre_column_ref(ParseState *pstate, ColumnRef *cref) { PLpgSQL_expr *expr = (PLpgSQL_expr *) pstate->p_ref_hook_state; - if (expr->func->resolve_option == PLPGSQL_RESOLVE_BEFORE) + if (expr->func->resolve_option == PLPGSQL_RESOLVE_VARIABLE) return resolve_column_ref(expr, cref); else return NULL; @@ -963,10 +963,10 @@ plpgsql_post_column_ref(ParseState *pstate, ColumnRef *cref, Node *var) PLpgSQL_expr *expr = (PLpgSQL_expr *) pstate->p_ref_hook_state; Node *myvar; - if (expr->func->resolve_option == PLPGSQL_RESOLVE_BEFORE) + if (expr->func->resolve_option == PLPGSQL_RESOLVE_VARIABLE) return NULL; /* we already found there's no match */ - if (expr->func->resolve_option == PLPGSQL_RESOLVE_AFTER && var != NULL) + if (expr->func->resolve_option == PLPGSQL_RESOLVE_COLUMN && var != NULL) return NULL; /* there's a table column, prefer that */ myvar = resolve_column_ref(expr, cref); diff --git a/src/pl/plpgsql/src/pl_handler.c b/src/pl/plpgsql/src/pl_handler.c index 7741308f287..4bd03298686 100644 --- a/src/pl/plpgsql/src/pl_handler.c +++ b/src/pl/plpgsql/src/pl_handler.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_handler.c,v 1.47 2009/11/04 22:26:07 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_handler.c,v 1.48 2009/11/13 22:43:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -26,6 +26,17 @@ PG_MODULE_MAGIC; +/* Custom GUC variable */ +static const struct config_enum_entry variable_conflict_options[] = { + {"error", PLPGSQL_RESOLVE_ERROR, false}, + {"use_variable", PLPGSQL_RESOLVE_VARIABLE, false}, + {"use_column", PLPGSQL_RESOLVE_COLUMN, false}, + {NULL, 0, false} +}; + +int plpgsql_variable_conflict = PLPGSQL_RESOLVE_ERROR; + +/* Hook for plugins */ PLpgSQL_plugin **plugin_ptr = NULL; @@ -45,6 +56,17 @@ _PG_init(void) pg_bindtextdomain(TEXTDOMAIN); + DefineCustomEnumVariable("plpgsql.variable_conflict", + gettext_noop("Sets handling of conflicts between PL/pgSQL variable names and table column names."), + NULL, + &plpgsql_variable_conflict, + PLPGSQL_RESOLVE_ERROR, + variable_conflict_options, + PGC_SUSET, 0, + NULL, NULL); + + EmitWarningsOnPlaceholders("plpgsql"); + plpgsql_HashTableInit(); RegisterXactCallback(plpgsql_xact_cb, NULL); RegisterSubXactCallback(plpgsql_subxact_cb, NULL); diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c index 523a0952cb1..fb4abc1454f 100644 --- a/src/pl/plpgsql/src/pl_scanner.c +++ b/src/pl/plpgsql/src/pl_scanner.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_scanner.c,v 1.1 2009/11/12 00:13:00 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_scanner.c,v 1.2 2009/11/13 22:43:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -113,6 +113,7 @@ static const ScanKeyword unreserved_keywords[] = { PG_KEYWORD("detail", K_DETAIL, UNRESERVED_KEYWORD) PG_KEYWORD("dump", K_DUMP, UNRESERVED_KEYWORD) PG_KEYWORD("errcode", K_ERRCODE, UNRESERVED_KEYWORD) + PG_KEYWORD("error", K_ERROR, UNRESERVED_KEYWORD) PG_KEYWORD("first", K_FIRST, UNRESERVED_KEYWORD) PG_KEYWORD("forward", K_FORWARD, UNRESERVED_KEYWORD) PG_KEYWORD("hint", K_HINT, UNRESERVED_KEYWORD) @@ -135,6 +136,9 @@ static const ScanKeyword unreserved_keywords[] = { PG_KEYWORD("scroll", K_SCROLL, UNRESERVED_KEYWORD) PG_KEYWORD("sqlstate", K_SQLSTATE, UNRESERVED_KEYWORD) PG_KEYWORD("type", K_TYPE, UNRESERVED_KEYWORD) + PG_KEYWORD("use_column", K_USE_COLUMN, UNRESERVED_KEYWORD) + PG_KEYWORD("use_variable", K_USE_VARIABLE, UNRESERVED_KEYWORD) + PG_KEYWORD("variable_conflict", K_VARIABLE_CONFLICT, UNRESERVED_KEYWORD) PG_KEYWORD("warning", K_WARNING, UNRESERVED_KEYWORD) }; diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index 1c05f5d4c2f..25d1c036999 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.124 2009/11/12 00:13:00 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.125 2009/11/13 22:43:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -147,9 +147,9 @@ enum */ typedef enum { - PLPGSQL_RESOLVE_BEFORE, /* prefer plpgsql var to table column */ - PLPGSQL_RESOLVE_AFTER, /* prefer table column to plpgsql var */ - PLPGSQL_RESOLVE_ERROR /* throw error if ambiguous */ + PLPGSQL_RESOLVE_ERROR, /* throw error if ambiguous */ + PLPGSQL_RESOLVE_VARIABLE, /* prefer plpgsql var to table column */ + PLPGSQL_RESOLVE_COLUMN /* prefer table column to plpgsql var */ } PLpgSQL_resolve_option; @@ -794,6 +794,8 @@ typedef struct * Global variable declarations **********************************************************************/ +extern int plpgsql_variable_conflict; + extern bool plpgsql_check_syntax; extern bool plpgsql_DumpExecTree; extern bool plpgsql_LookupIdentifiers; diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 877ed329a67..e126f616fe1 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -4004,6 +4004,7 @@ select scope_test(); drop function scope_test(); -- Check handling of conflicts between plpgsql vars and table columns. +set plpgsql.variable_conflict = error; create function conflict_test() returns setof int8_tbl as $$ declare r record; q1 bigint := 42; @@ -4014,6 +4015,23 @@ begin end; $$ language plpgsql; select * from conflict_test(); +ERROR: column reference "q1" is ambiguous +LINE 1: select q1,q2 from int8_tbl + ^ +DETAIL: It could refer to either a PL/pgSQL variable or a table column. +QUERY: select q1,q2 from int8_tbl +CONTEXT: PL/pgSQL function "conflict_test" line 4 at FOR over SELECT rows +create or replace function conflict_test() returns setof int8_tbl as $$ +#variable_conflict use_variable +declare r record; + q1 bigint := 42; +begin + for r in select q1,q2 from int8_tbl loop + return next r; + end loop; +end; +$$ language plpgsql; +select * from conflict_test(); q1 | q2 ----+------------------- 42 | 456 @@ -4023,6 +4041,26 @@ select * from conflict_test(); 42 | -4567890123456789 (5 rows) +create or replace function conflict_test() returns setof int8_tbl as $$ +#variable_conflict use_column +declare r record; + q1 bigint := 42; +begin + for r in select q1,q2 from int8_tbl loop + return next r; + end loop; +end; +$$ language plpgsql; +select * from conflict_test(); + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + drop function conflict_test(); -- Check that an unreserved keyword can be used as a variable name create function unreserved_test() returns int as $$ diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index f0a77469d92..79756f6a01c 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -3176,6 +3176,8 @@ drop function scope_test(); -- Check handling of conflicts between plpgsql vars and table columns. +set plpgsql.variable_conflict = error; + create function conflict_test() returns setof int8_tbl as $$ declare r record; q1 bigint := 42; @@ -3188,6 +3190,32 @@ $$ language plpgsql; select * from conflict_test(); +create or replace function conflict_test() returns setof int8_tbl as $$ +#variable_conflict use_variable +declare r record; + q1 bigint := 42; +begin + for r in select q1,q2 from int8_tbl loop + return next r; + end loop; +end; +$$ language plpgsql; + +select * from conflict_test(); + +create or replace function conflict_test() returns setof int8_tbl as $$ +#variable_conflict use_column +declare r record; + q1 bigint := 42; +begin + for r in select q1,q2 from int8_tbl loop + return next r; + end loop; +end; +$$ language plpgsql; + +select * from conflict_test(); + drop function conflict_test(); -- Check that an unreserved keyword can be used as a variable name |