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

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

> The following bug has been logged on the website:
>
> Bug reference: 16702
> Logged by: Zhou Digoal
> Email address: digoal(at)126(dot)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