1

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 ?

2

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.

| improve this answer | |
1

hstore

You can use hstore for this with it's akeys to return the keys as an array.

CREATE EXTENSION hstore;

SELECT akeys(hstore(t))
FROM ( VALUES (1,2,3) ) AS t(a,b,c);
  akeys  
---------
 {a,b,c}

Or use skeys to return a set

SELECT skeys
FROM ( VALUES (1,2,3) ) AS t(a,b,c)
CROSS JOIN LATERAL skeys(hstore(t));
 skeys 
-------
 a
 b
 c
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.