Thread: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
The following bug has been logged on the website: Bug reference: 16702 Logged by: Zhou Digoal Email address: digoal@126.com PostgreSQL version: 12.4 Operating system: CentOS 7.7 x64 Description: postgresql 12, when i use dynamic name for rowtype, there is some bug! ``` postgres=> \d b Table "public.b" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | c1 | jsonb | | | Indexes: "idx_b_1" gin (c1) postgres=> \d a Table "public.a" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | bigint | | not null | gid | integer | | | score | integer | | | info | text | | | Indexes: "a_pkey" PRIMARY KEY, btree (id) "idx_a_1" btree (gid, score) "idx_a_2" btree (gid) postgres=> \d tbl Table "public.tbl" Column | Type | Collation | Nullable | Default ----------+-----------------------------+-----------+----------+--------- col | integer | | | c1 | integer | | | c2 | integer | | | mod_time | timestamp without time zone | | | postgres=> select * from a limit 1; id | gid | score | info ----+--------+-------+---------------------------------- 1 | 112736 | 393 | 3d41b33b5e739b30eebfa15109e2db9f (1 row) postgres=> select * from tbl limit 1; col | c1 | c2 | mod_time ------+----+----+---------------------------- 9150 | 32 | 47 | 2020-10-31 17:06:28.452212 (1 row) postgres=> do language plpgsql $$ declare y text := 'tbl'; b y%rowtype; begin select tbl.* into b from tbl limit 1; raise notice '%', b; end; $$; ERROR: relation "y" does not exist CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 4 postgres=> do language plpgsql $$ declare a text := 'tbl'; b a%rowtype; begin select tbl.* into b from tbl limit 1; raise notice '%', b; end; $$; NOTICE: (9150,32,47,"2020-10-31 17:06:28.452212") DO postgres=> create or replace function f(a text) returns void as $$ declare v a%rowtype; begin execute format('select * from %I limit 1', a) into v; raise notice '%: %', a, v; end; $$ language plpgsql strict; CREATE FUNCTION postgres=> select * from f('tbl'); NOTICE: tbl: (9150,32,47,"2020-10-31 17:06:28.452212") f --- (1 row) postgres=> select * from f('b'); ERROR: invalid input syntax for type integer: "[{"a": 1, "b": 2}, {"c": 2, "d": 4}]" CONTEXT: PL/pgSQL function f(text) line 4 at EXECUTE ``` i know there only check the name(variable name) is exists , but when execute , it use the dynamic name(variable value) for it , not the static name(variable name).
Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
The following bug has been logged on the website:
Bug reference: 16702
Logged by: Zhou Digoal
Email address: digoal@126.com
PostgreSQL version: 12.4
Operating system: CentOS 7.7 x64
Description:
postgresql 12, when i use dynamic name for rowtype, there is some bug!
```
postgres=> \d b
Table "public.b"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
c1 | jsonb | | |
Indexes:
"idx_b_1" gin (c1)
postgres=> \d a
Table "public.a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | bigint | | not null |
gid | integer | | |
score | integer | | |
info | text | | |
Indexes:
"a_pkey" PRIMARY KEY, btree (id)
"idx_a_1" btree (gid, score)
"idx_a_2" btree (gid)
postgres=> \d tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
col | integer | | |
c1 | integer | | |
c2 | integer | | |
mod_time | timestamp without time zone | | |
postgres=> select * from a limit 1;
id | gid | score | info
----+--------+-------+----------------------------------
1 | 112736 | 393 | 3d41b33b5e739b30eebfa15109e2db9f
(1 row)
postgres=> select * from tbl limit 1;
col | c1 | c2 | mod_time
------+----+----+----------------------------
9150 | 32 | 47 | 2020-10-31 17:06:28.452212
(1 row)
postgres=> do language plpgsql $$
declare
y text := 'tbl';
b y%rowtype;
begin
select tbl.* into b from tbl limit 1;
raise notice '%', b;
end;
$$;
ERROR: relation "y" does not exist
CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 4
postgres=> do language plpgsql $$
declare
a text := 'tbl';
b a%rowtype;
begin
select tbl.* into b from tbl limit 1;
raise notice '%', b;
end;
$$;
NOTICE: (9150,32,47,"2020-10-31 17:06:28.452212")
DO
postgres=> create or replace function f(a text) returns void as $$
declare v a%rowtype;
begin
execute format('select * from %I limit 1', a) into v;
raise notice '%: %', a, v;
end;
$$ language plpgsql strict;
CREATE FUNCTION
postgres=> select * from f('tbl');
NOTICE: tbl: (9150,32,47,"2020-10-31 17:06:28.452212")
f
---
(1 row)
postgres=> select * from f('b');
ERROR: invalid input syntax for type integer: "[{"a": 1, "b": 2}, {"c": 2,
"d": 4}]"
CONTEXT: PL/pgSQL function f(text) line 4 at EXECUTE
```
i know there only check the name(variable name) is exists , but when execute
, it use the dynamic name(variable value) for it , not the static
name(variable name).
Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
The following bug has been logged on the website:
Bug reference: 16702
Logged by: Zhou Digoal
Email address: digoal@126.com
PostgreSQL version: 12.4
Operating system: CentOS 7.7 x64
Description:
postgresql 12, when i use dynamic name for rowtype, there is some bug!
b y%rowtype;
Re:Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
However, in PostgreSQL with the variable % RowType, compilation can pass if the variable has the same name as an existing table. The table name corresponding to the value of the variable name is used as rowType.
However, when the variable name cannot find the table name with the same name, the compilation error is directly reported, which is inconsistent with Oracle.
--公益是一辈子的事,I'm Digoal,Just Do It.
在 2020-11-05 22:15:45,"David G. Johnston" <david.g.johnston@gmail.com> 写道:
On Wednesday, November 4, 2020, PG Bug reporting form <noreply@postgresql.org> wrote:The following bug has been logged on the website:
Bug reference: 16702
Logged by: Zhou Digoal
Email address: digoal@126.com
PostgreSQL version: 12.4
Operating system: CentOS 7.7 x64
Description:
postgresql 12, when i use dynamic name for rowtype, there is some bug!
b y%rowtype;Rowtype gets attached to an identifier, which must also be a relation. In PostgreSQL, identifiers cannot be variable.David J.
Re: Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
The customer originally used Oracle. Oracle supports using the table name in the variable name instead, while using the rowType corresponding to the table name of the variable name.
However, in PostgreSQL with the variable % RowType, compilation can pass if the variable has the same name as an existing table. The table name corresponding to the value of the variable name is used as rowType.
However, when the variable name cannot find the table name with the same name, the compilation error is directly reported, which is inconsistent with Oracle.
I think this is a bug, the reason has nothing to do with Oracle, but the execution behavior is biased, either do not replace the value of the variable during the execution, but directly use the table name corresponding to the variable name.
Best regards,digoal
--公益是一辈子的事,I'm Digoal,Just Do It.
在 2020-11-05 22:15:45,"David G. Johnston" <david.g.johnston@gmail.com> 写道:
On Wednesday, November 4, 2020, PG Bug reporting form <noreply@postgresql.org> wrote:The following bug has been logged on the website:
Bug reference: 16702
Logged by: Zhou Digoal
Email address: digoal@126.com
PostgreSQL version: 12.4
Operating system: CentOS 7.7 x64
Description:
postgresql 12, when i use dynamic name for rowtype, there is some bug!
b y%rowtype;Rowtype gets attached to an identifier, which must also be a relation. In PostgreSQL, identifiers cannot be variable.David J.
Re: Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
but the execution behavior is biased, either do not replace the value of the variable during the execution, but directly use the table name corresponding to the variable name.