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

Commita2a731d

Browse files
committed
Test and document the behavior of initialization cross-refs in plpgsql.
We had a test showing that a variable isn't referenceable in itsown initialization expression, nor in prior ones in the same block.It *is* referenceable in later expressions in the same block, butAFAICS there is no test case exercising that. Add one, and alsoadd some error cases.Also, document that this is possible, since the docs failed tocover the point.Per question from tomás at tuxteam. I don't feel any need toback-patch this, but we should ensure we don't break it in future.Discussion:https://postgr.es/m/20211029121435.GA5414@tuxteam.de
1 parent937aafd commita2a731d

File tree

3 files changed

+76
-20
lines changed

3 files changed

+76
-20
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -379,7 +379,17 @@ arow RECORD;
379379
<programlisting>
380380
quantity integer DEFAULT 32;
381381
url varchar := 'http://mysite.com';
382-
user_id CONSTANT integer := 10;
382+
transaction_time CONSTANT timestamp with time zone := now();
383+
</programlisting>
384+
</para>
385+
386+
<para>
387+
Once declared, a variable's value can be used in later initialization
388+
expressions in the same block, for example:
389+
<programlisting>
390+
DECLARE
391+
x integer := 1;
392+
y integer := x + 1;
383393
</programlisting>
384394
</para>
385395

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

Lines changed: 37 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -4637,24 +4637,50 @@ NOTICE: caught division by zero
46374637
NOTICE: caught division by zero
46384638
NOTICE: caught division by zero
46394639
-- Check variable scoping -- a var is not available in its own or prior
4640-
-- default expressions.
4641-
create function scope_test() returns int as $$
4640+
-- default expressions, but it is available in later ones.
4641+
do $$
4642+
declare x int := x + 1; -- error
4643+
begin
4644+
raise notice 'x = %', x;
4645+
end;
4646+
$$;
4647+
ERROR: column "x" does not exist
4648+
LINE 1: x + 1
4649+
^
4650+
QUERY: x + 1
4651+
CONTEXT: PL/pgSQL function inline_code_block line 3 during statement block local variable initialization
4652+
do $$
4653+
declare y int := x + 1; -- error
4654+
x int := 42;
4655+
begin
4656+
raise notice 'x = %, y = %', x, y;
4657+
end;
4658+
$$;
4659+
ERROR: column "x" does not exist
4660+
LINE 1: x + 1
4661+
^
4662+
QUERY: x + 1
4663+
CONTEXT: PL/pgSQL function inline_code_block line 4 during statement block local variable initialization
4664+
do $$
4665+
declare x int := 42;
4666+
y int := x + 1;
4667+
begin
4668+
raise notice 'x = %, y = %', x, y;
4669+
end;
4670+
$$;
4671+
NOTICE: x = 42, y = 43
4672+
do $$
46424673
declare x int := 42;
46434674
begin
46444675
declare y int := x + 1;
46454676
x int := x + 2;
4677+
z int := x * 10;
46464678
begin
4647-
return x * 100 + y;
4679+
raise notice 'x = %, y = %, z = %', x, y, z;
46484680
end;
46494681
end;
4650-
$$ language plpgsql;
4651-
select scope_test();
4652-
scope_test
4653-
------------
4654-
4443
4655-
(1 row)
4656-
4657-
drop function scope_test();
4682+
$$;
4683+
NOTICE: x = 44, y = 43, z = 440
46584684
-- Check handling of conflicts between plpgsql vars and table columns.
46594685
set plpgsql.variable_conflict = error;
46604686
create function conflict_test() returns setof int8_tbl as $$

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

Lines changed: 28 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -3795,22 +3795,42 @@ end;
37953795
$outer$;
37963796
37973797
-- Check variable scoping -- a var is not available in its own or prior
3798-
-- default expressions.
3798+
-- default expressions, but it is available in later ones.
37993799
3800-
create function scope_test() returns int as $$
3800+
do $$
3801+
declare x int := x + 1; -- error
3802+
begin
3803+
raise notice'x= %', x;
3804+
end;
3805+
$$;
3806+
3807+
do $$
3808+
declare y int := x + 1; -- error
3809+
x int := 42;
3810+
begin
3811+
raise notice'x= %, y= %', x, y;
3812+
end;
3813+
$$;
3814+
3815+
do $$
3816+
declare x int := 42;
3817+
y int := x + 1;
3818+
begin
3819+
raise notice'x= %, y= %', x, y;
3820+
end;
3821+
$$;
3822+
3823+
do $$
38013824
declare x int := 42;
38023825
begin
38033826
declare y int := x + 1;
38043827
x int := x + 2;
3828+
z int := x * 10;
38053829
begin
3806-
return x * 100 + y;
3830+
raise notice'x= %, y= %, z= %', x, y, z;
38073831
end;
38083832
end;
3809-
$$ language plpgsql;
3810-
3811-
select scope_test();
3812-
3813-
drop function scope_test();
3833+
$$;
38143834
38153835
-- Check handling of conflicts between plpgsql vars and table columns.
38163836

0 commit comments

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