Re: Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug! - Mailing list pgsql-bugs

From Pavel Stehule
Subject Re: Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
Date
Msg-id CAFj8pRCj2mxh2KsWeDFtXSoUajg9snzbSB7L+9kPojreF_dwvA@mail.gmail.com
Whole thread Raw
In response to Re:Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!  (德哥 <digoal@126.com>)
List pgsql-bugs
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.
 

pgsql-bugs by date:

Previous
From: 德哥
Date:
Subject: Re:Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
Next
From: Tom Lane
Date:
Subject: Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint