Search code examples
postgresqlvariablesrecordplpgsqldescriptor

Is there in plpgsql a possibility to get record item names and use them?


I need to report record items using "raise notice", but including item names.

raise notice 'rec=%',r; -- is not very good

Is there a possibility to do someting like:

declare
  r record;
  itemname varchar(200);
begin
  select * into r from my_table_unknown_structure;

  for itemname in pg_record_item_names(r)
  loop
    raise notice '%=%,itemname,pg_record_item_value(r,itemname);
  end loop;
end;

Solution

  • The answer is based on the CRAIG RINGER's comment, using "hstore" extension.
    I would like to promote his comment as the answer.

    declare
        r record;
        h hstore;
    begin
        select * into r from more_joined_tables;
        select * into h from hstore(r);
        raise notice '%',h;
    end;
    

    OR

    
    declare
        r record;
        h record;
    begin
        select * into r from price_list;
        for h in select * from each(hstore(r))
        loop
            raise notice '%',h;
        end loop;
    end;