Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitae1b7e2

Browse files
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 aplpgsql variable name if needed to avoid ambiguity. While there is much morework to be done in this area, this simple change removes one unnecessaryincompatibility with Oracle. Per discussion.
1 parent9f6f51d commitae1b7e2

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 parametername 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 columnname, 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_varnameopt_scrollableK_CURSOR
369-
{ plpgsql_ns_push(NULL); }
369+
{ plpgsql_ns_push($1.name); }
370370
decl_cursor_argsdecl_is_fordecl_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(constchar*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(constchar*name,constchar*label)
228227
{
229228
PLpgSQL_ns*ns;
230229
inti;
@@ -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
returnns->items[i];
245244
}
246245
returnNULL;/* 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
returnns->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
returnns->items[i];
270269
}
271270
if (ns_localmode)
@@ -288,14 +287,13 @@ plpgsql_ns_rename(char *oldname, char *newname)
288287
inti;
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
externvoidplpgsql_ns_init(void);
772772
externboolplpgsql_ns_setlocal(boolflag);
773-
externvoidplpgsql_ns_push(char*label);
773+
externvoidplpgsql_ns_push(constchar*label);
774774
externvoidplpgsql_ns_pop(void);
775775
externvoidplpgsql_ns_additem(intitemtype,intitemno,constchar*name);
776-
externPLpgSQL_nsitem*plpgsql_ns_lookup(char*name,char*nsname);
776+
externPLpgSQL_nsitem*plpgsql_ns_lookup(constchar*name,constchar*nsname);
777777
externvoidplpgsql_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
dropfunction sc_test();
25372537

2538+
-- test qualified variable names
2539+
2540+
createfunctionpl_qual_names (param1int) returns voidas $$
2541+
<<outerblock>>
2542+
declare
2543+
param1int :=1;
2544+
begin
2545+
<<innerblock>>
2546+
declare
2547+
param1int :=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+
dropfunction pl_qual_names(int);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp