access to record's field in dynamic SQL doesn't work

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: access to record's field in dynamic SQL doesn't work
Date: 2021-10-16 17:39:31
Message-ID: CAFj8pRCKy2hMdHRO+eHSAo+-AYOctsX5gWDKfgTOFEr2Sog11g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

I played with one dynamic access to record's fields. I was surprised so I
cannot to access to record field from dynamic SQL. Is there some reason why
it is not possible? Today all composite types in PL/pgSQL are records:

do $$
declare r record; _relname varchar;
begin
for r in select * from pg_class limit 3
loop
execute 'select ($1).relname' using r into _relname;
raise notice '%', _relname;
end loop;
end;
$$;
ERROR: could not identify column "relname" in record data type
LINE 1: select ($1).relname
^
QUERY: select ($1).relname
CONTEXT: PL/pgSQL function inline_code_block line 6 at EXECUTE

but:
do $$
declare r record; _relname varchar;
begin
for r in select * from pg_class limit 3
loop
--execute 'select ($1).relname' using r into _relname;
raise notice '%', r.relname;
end loop;
end;
$$;
NOTICE: pg_statistic
NOTICE: pg_type
NOTICE: pg_toast_1255

and

postgres=# do $$
declare r pg_class; _relname varchar;
begin
for r in select * from pg_class limit 3
loop
execute 'select ($1).relname' using r into _relname;
raise notice '%', _relname;
end loop;
end;
$$;
NOTICE: pg_statistic
NOTICE: pg_type
NOTICE: pg_toast_1255

it is working too.

Why there is difference between typed composite and record type although
internally should be same?

Regards

Pavel

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2021-10-16 21:17:35 Refactoring pg_dump's getTables()
Previous Message rir 2021-10-16 17:11:49 Re: Trivial doc patch