pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: digoal@126.com
Subject: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
Date: Thu, 05 Nov 2020 06:13:37 +0000
Message-ID: <16702-f82c1f74d5042413@postgresql.org> (raw)

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



view thread (6+ messages)  latest in thread

Message-ID: <16702-f82c1f74d5042413@postgresql.org>
Permalink:  ../16702-f82c1f74d5042413@postgresql.org/
Also on:    postgresql.org/message-id/16702-f82c1f74d5042413@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, digoal@126.com
  Subject: Re: BUG #16702: inline code and function : when use dynamic name for rowtype, there is some bug!
  In-Reply-To: <16702-f82c1f74d5042413@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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