snippetsqlMinor

How to get the column names of a RECORD?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thecolumnnamesrecordgethow

Problem

I have a function that declares:

DECLARE r RECORD;

The data on this record varies and at some point I need to know the column names.

How do I get the column names of a RECORD in another record ?

Solution

Generally PLpgSQL doesn't allow dynamic work over fields of composite types. This languages is pretty static - more similar to older compilied languages like Pascal, Ada, Modula.

Some modern version of PostgreSQL allows transformation record->json and Postgres has lot of functions for work with JSON values (I use row_to_json and json_each_row` functions):

do $
declare
  r record;
  key text;
  val text;
begin
  select 1 as a,3.4 as b into r;
  for key, val in
     select * from json_each_text(row_to_json(r))
  loop
    raise notice '% %', key, val;
  end loop;
end $;

NOTICE:  a 1
NOTICE:  b 3.4
DO

I don't know your case, but if you need this feature, probably you are using plpgsql not well. It is static language - mostly time you should to know, what fields are in record before you are execute query. The programming of stored procedures is pretty far to programming in dynamic languages like JavaScript or Python or Lua.

Code Snippets

do $$
declare
  r record;
  key text;
  val text;
begin
  select 1 as a,3.4 as b into r;
  for key, val in
     select * from json_each_text(row_to_json(r))
  loop
    raise notice '% %', key, val;
  end loop;
end $$;

NOTICE:  a 1
NOTICE:  b 3.4
DO

Context

StackExchange Database Administrators Q#217969, answer score: 4

Revisions (0)

No revisions yet.