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

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

From
Pavel Stehule
Date:
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