Skip to content

Commit ae1b7e2

Browse files
tglsfdctglsfdc
committed
Allow plpgsql function parameter names to be qualified with the function's
name. With this patch, it is always possible for the user to qualify a plpgsql variable name if needed to avoid ambiguity. While there is much more work to be done in this area, this simple change removes one unnecessary incompatibility with Oracle. Per discussion.
1 parent 9f6f51d commit ae1b7e2

File tree

7 files changed

+104
-28
lines changed

7 files changed

+104
-28
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 34 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.114 2007/07/15 00:45:16 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.115 2007/07/16 17:01:10 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -249,10 +249,23 @@ $$ LANGUAGE plpgsql;
249249
</programlisting>
250250
</para>
251251

252+
<note>
253+
<para>
254+
There is actually a hidden <quote>outer block</> surrounding the body
255+
of any <application>PL/pgSQL</> function. This block provides the
256+
declarations of the function's parameters (if any), as well as some
257+
special variables such as <literal>FOUND</literal> (see
258+
<xref linkend="plpgsql-statements-diagnostics">). The outer block is
259+
labeled with the function's name, meaning that parameters and special
260+
variables can be qualified with the function's name.
261+
</para>
262+
</note>
263+
252264
<para>
253265
It is important not to confuse the use of
254266
<command>BEGIN</>/<command>END</> for grouping statements in
255-
<application>PL/pgSQL</> with the database commands for transaction
267+
<application>PL/pgSQL</> with the similarly-named SQL commands
268+
for transaction
256269
control. <application>PL/pgSQL</>'s <command>BEGIN</>/<command>END</>
257270
are only for grouping; they do not start or end a transaction.
258271
Functions and trigger procedures are always executed within a transaction
@@ -370,6 +383,19 @@ BEGIN
370383
END;
371384
$$ LANGUAGE plpgsql;
372385
</programlisting>
386+
</para>
387+
388+
<note>
389+
<para>
390+
These two examples are not perfectly equivalent. In the first case,
391+
<literal>subtotal</> could be referenced as
392+
<literal>sales_tax.subtotal</>, but in the second case it could not.
393+
(Had we attached a label to the block, <literal>subtotal</> could
394+
be qualified with that label, instead.)
395+
</para>
396+
</note>
397+
398+
<para>
373399
Some more examples:
374400
<programlisting>
375401
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
@@ -3618,12 +3644,12 @@ a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
36183644

36193645
<listitem>
36203646
<para>
3621-
You cannot use parameter names that are the same as columns
3622-
that are referenced in the function. Oracle allows you to do this
3623-
if you qualify the parameter name using
3624-
<literal>function_name.parameter_name</>.
3625-
In <application>PL/pgSQL</>, you can instead avoid a conflict by
3626-
qualifying the column or table name.
3647+
If a name used in a SQL command could be either a column name of a
3648+
table or a reference to a variable of the function,
3649+
<application>PL/SQL</> treats it as a column name, while
3650+
<application>PL/pgSQL</> treats it as a variable name. It's best
3651+
to avoid such ambiguities in the first place, but if necessary you
3652+
can fix them by properly qualifying the ambiguous name.
36273653
(See <xref linkend="plpgsql-var-subst">.)
36283654
</para>
36293655
</listitem>

‎src/pl/plpgsql/src/gram.y

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.103 2007/07/15 02:15:04 tgl Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.104 2007/07/16 17:01:10 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -366,7 +366,7 @@ decl_statement : decl_varname decl_const decl_datatype decl_notnull decl_defval
366366
plpgsql_ns_rename($2, $4);
367367
}
368368
| decl_varname opt_scrollable K_CURSOR
369-
{ plpgsql_ns_push(NULL); }
369+
{ plpgsql_ns_push($1.name); }
370370
decl_cursor_args decl_is_for decl_cursor_query
371371
{
372372
PLpgSQL_var *new;

‎src/pl/plpgsql/src/pl_comp.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.116 2007/06/26 16:48:09 alvherre Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.117 2007/07/16 17:01:10 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -306,10 +306,12 @@ do_compile(FunctionCallInfo fcinfo,
306306
error_context_stack = &plerrcontext;
307307

308308
/*
309-
* Initialize the compiler
309+
* Initialize the compiler, particularly the namespace stack. The
310+
* outermost namespace contains function parameters and other special
311+
* variables (such as FOUND), and is named after the function itself.
310312
*/
311313
plpgsql_ns_init();
312-
plpgsql_ns_push(NULL);
314+
plpgsql_ns_push(NameStr(procStruct->proname));
313315
plpgsql_DumpExecTree = false;
314316

315317
datums_alloc = 128;

‎src/pl/plpgsql/src/pl_funcs.c

Lines changed: 10 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.60 2007/07/15 02:15:04 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.61 2007/07/16 17:01:11 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -147,15 +147,14 @@ plpgsql_ns_setlocal(bool flag)
147147
* ----------
148148
*/
149149
void
150-
plpgsql_ns_push(char *label)
150+
plpgsql_ns_push(const char *label)
151151
{
152152
PLpgSQL_ns *new;
153153

154154
if (label == NULL)
155155
label = "";
156156

157-
new = palloc(sizeof(PLpgSQL_ns));
158-
memset(new, 0, sizeof(PLpgSQL_ns));
157+
new = palloc0(sizeof(PLpgSQL_ns));
159158
new->upper = ns_current;
160159
ns_current = new;
161160

@@ -224,7 +223,7 @@ plpgsql_ns_additem(int itemtype, int itemno, const char *name)
224223
* ----------
225224
*/
226225
PLpgSQL_nsitem *
227-
plpgsql_ns_lookup(char *name, char *label)
226+
plpgsql_ns_lookup(const char *name, const char *label)
228227
{
229228
PLpgSQL_ns *ns;
230229
int i;
@@ -236,11 +235,11 @@ plpgsql_ns_lookup(char *name, char *label)
236235
{
237236
for (ns = ns_current; ns != NULL; ns = ns->upper)
238237
{
239-
if (!strcmp(ns->items[0]->name, label))
238+
if (strcmp(ns->items[0]->name, label) == 0)
240239
{
241240
for (i = 1; i < ns->items_used; i++)
242241
{
243-
if (!strcmp(ns->items[i]->name, name))
242+
if (strcmp(ns->items[i]->name, name) == 0)
244243
return ns->items[i];
245244
}
246245
return NULL; /* name not found in specified label */
@@ -254,7 +253,7 @@ plpgsql_ns_lookup(char *name, char *label)
254253
*/
255254
for (ns = ns_current; ns != NULL; ns = ns->upper)
256255
{
257-
if (!strcmp(ns->items[0]->name, name))
256+
if (strcmp(ns->items[0]->name, name) == 0)
258257
return ns->items[0];
259258
}
260259

@@ -265,7 +264,7 @@ plpgsql_ns_lookup(char *name, char *label)
265264
{
266265
for (i = 1; i < ns->items_used; i++)
267266
{
268-
if (!strcmp(ns->items[i]->name, name))
267+
if (strcmp(ns->items[i]->name, name) == 0)
269268
return ns->items[i];
270269
}
271270
if (ns_localmode)
@@ -288,14 +287,13 @@ plpgsql_ns_rename(char *oldname, char *newname)
288287
int i;
289288

290289
/*
291-
* Lookup name in the namestack; do the lookup in the current namespace
292-
* only.
290+
* Lookup name in the namestack
293291
*/
294292
for (ns = ns_current; ns != NULL; ns = ns->upper)
295293
{
296294
for (i = 1; i < ns->items_used; i++)
297295
{
298-
if (!strcmp(ns->items[i]->name, oldname))
296+
if (strcmp(ns->items[i]->name, oldname) == 0)
299297
{
300298
newitem = palloc(sizeof(PLpgSQL_nsitem) + strlen(newname));
301299
newitem->itemtype = ns->items[i]->itemtype;

‎src/pl/plpgsql/src/plpgsql.h

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.89 2007/07/15 02:15:04 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.90 2007/07/16 17:01:11 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -770,10 +770,10 @@ extern char *plpgsql_dstring_get(PLpgSQL_dstring *ds);
770770
*/
771771
extern void plpgsql_ns_init(void);
772772
extern bool plpgsql_ns_setlocal(bool flag);
773-
extern void plpgsql_ns_push(char *label);
773+
extern void plpgsql_ns_push(const char *label);
774774
extern void plpgsql_ns_pop(void);
775775
extern void plpgsql_ns_additem(int itemtype, int itemno, const char *name);
776-
extern PLpgSQL_nsitem *plpgsql_ns_lookup(char *name, char *nsname);
776+
extern PLpgSQL_nsitem *plpgsql_ns_lookup(const char *name, const char *nsname);
777777
extern void plpgsql_ns_rename(char *oldname, char *newname);
778778

779779
/* ----------

‎src/test/regress/expected/plpgsql.out

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3051,3 +3051,31 @@ select * from sc_test();
30513051
(3 rows)
30523052

30533053
drop function sc_test();
3054+
-- test qualified variable names
3055+
create function pl_qual_names (param1 int) returns void as $$
3056+
<<outerblock>>
3057+
declare
3058+
param1 int := 1;
3059+
begin
3060+
<<innerblock>>
3061+
declare
3062+
param1 int := 2;
3063+
begin
3064+
raise notice 'param1 = %', param1;
3065+
raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
3066+
raise notice 'outerblock.param1 = %', outerblock.param1;
3067+
raise notice 'innerblock.param1 = %', innerblock.param1;
3068+
end;
3069+
end;
3070+
$$ language plpgsql;
3071+
select pl_qual_names(42);
3072+
NOTICE: param1 = 2
3073+
NOTICE: pl_qual_names.param1 = 42
3074+
NOTICE: outerblock.param1 = 1
3075+
NOTICE: innerblock.param1 = 2
3076+
pl_qual_names
3077+
---------------
3078+
3079+
(1 row)
3080+
3081+
drop function pl_qual_names(int);

‎src/test/regress/sql/plpgsql.sql

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2535,3 +2535,25 @@ select * from sc_test();
25352535

25362536
drop function sc_test();
25372537

2538+
-- test qualified variable names
2539+
2540+
create function pl_qual_names (param1 int) returns void as $$
2541+
<<outerblock>>
2542+
declare
2543+
param1 int := 1;
2544+
begin
2545+
<<innerblock>>
2546+
declare
2547+
param1 int := 2;
2548+
begin
2549+
raise notice 'param1 = %', param1;
2550+
raise notice 'pl_qual_names.param1 = %', pl_qual_names.param1;
2551+
raise notice 'outerblock.param1 = %', outerblock.param1;
2552+
raise notice 'innerblock.param1 = %', innerblock.param1;
2553+
end;
2554+
end;
2555+
$$ language plpgsql;
2556+
2557+
select pl_qual_names(42);
2558+
2559+
drop function pl_qual_names(int);

0 commit comments

Comments
 (0)