pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
6+ messages / 4 participants
[nested] [flat]

* BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
@ 2020-11-05 06:13  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 2 replies; 6+ messages in thread

From: PG Bug reporting form @ 2020-11-05 06:13 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: digoal@126.com

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).



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
@ 2020-11-05 09:15  Pavel Stehule <pavel.stehule@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 0 replies; 6+ messages in thread

From: Pavel Stehule @ 2020-11-05 09:15 UTC (permalink / raw)
  To: 德哥 <digoal@126.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

čt 5. 11. 2020 v 9:36 odesílatel PG Bug reporting form <
noreply@postgresql.org> napsal:

> 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).
>

I don't see any bug - variable content cannot be used as a type specifier
anywhere. If you need dynamic type, then use "record" type instead.

declare r record;
begin
   execute format('select * from %I limit 1', a) into r;

Regards

Pavel

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
@ 2020-11-05 14:15  David G. Johnston <david.g.johnston@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 1 reply; 6+ messages in thread

From: David G. Johnston @ 2020-11-05 14:15 UTC (permalink / raw)
  To: digoal@126.com <digoal@126.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re:Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
@ 2020-11-09 13:49  德哥 <digoal@126.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 2 replies; 6+ messages in thread

From: 德哥 @ 2020-11-09 13:49 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>




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.
 

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
@ 2020-11-09 14:15  Pavel Stehule <pavel.stehule@gmail.com>
  parent: 德哥 <digoal@126.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Pavel Stehule @ 2020-11-09 14:15 UTC (permalink / raw)
  To: 德哥 <digoal@126.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Hi

po 9. 11. 2020 v 14:59 odesílatel 德哥 <digoal@126.com> napsal:

>
> 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.
>

I looking to Oracle documentation, and %rowtype can be used only for tables
and cursor variables. Unfortunately Postgres cannot support cursor
variables there - Postgres requires known structure, and it is not defined
in this moment. This syntax has more sense in Oracle, because it is much
more static - and the query structure is fixed. PL/pgSQL cannot to support
this feature. Instead you can use RECORD type. Although the PL/SQL and
PL/pgSQL looks simillary, the implementation is significantly different -
the work with cursors is very different. PL/pgSQL does not guarantee full
compatibility with  PL/SQL - and some features cannot be implemented there.

Regards

Pavel



> 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.
>
>
>

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
@ 2020-11-09 16:07  David G. Johnston <david.g.johnston@gmail.com>
  parent: 德哥 <digoal@126.com>
  1 sibling, 0 replies; 6+ messages in thread

From: David G. Johnston @ 2020-11-09 16:07 UTC (permalink / raw)
  To: 德哥 <digoal@126.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Mon, Nov 9, 2020 at 6:50 AM 德哥 <digoal@126.com> wrote:

> 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.
>

I've lost something in translation.  There is no bias (not sure what that
means in this context) or variability/ambiguity here.  The thing in front
of %RowType is always an identifier - specifically, a table.  That your
function also has a variable of the same name is immaterial to PostgreSQL
since none of the variables in the function are ever considered when it is
expecting/required-to-have an identifier to fulfill the syntax.  This is
well-defined in PostgreSQL pl/pgsql and so the observed behavior is not
buggy.

"select tbl%rowtype into a%rowtype" is effectively (cast tbl%rowtype as
a%rowtype) which is a well-defined and the user is responsible for ensuring
the two rowtypes are compatible. "tbl" and "a" are compatible, "b" and "a"
are not, which your original post demonstrates.

David J.

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2020-11-09 16:07 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-05 06:13 BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug! PG Bug reporting form <noreply@postgresql.org>
2020-11-05 09:15 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-11-05 14:15 ` David G. Johnston <david.g.johnston@gmail.com>
2020-11-09 13:49   ` 德哥 <digoal@126.com>
2020-11-09 14:15     ` Pavel Stehule <pavel.stehule@gmail.com>
2020-11-09 16:07     ` David G. Johnston <david.g.johnston@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox