1

I have a Postgresql PL/pgSQL function that has a double nested FOR loop, where I want to set column names dynamically. However I can't find a way of accessing RECORD columns programmatically.

I'll skip straight to an example with some code:

FOR loop_helper1 IN SELECT
  id, name1, name2, name3, nameN,
  FROM table1
  LOOP
    FOR loop_helper2 IN SELECT name FROM table2 LOOP
      -- I want to set values of columns in loop_helper1,
      -- with the column name given by loop_helper2.name
      -- An EXECUTE would not allow me to do this:
      EXECUTE 'loop_helper1.' || loop_helper2.name || ':= function_call(123);'
      -- (Eg. 'loop_helper1.name2 := function_call(123);')
      -- However, this produces: ERROR:  syntax error at or near "loop_helper1"
    END LOOP;
END LOOP;

Any ideas?

Surely there's gotta be a way to do this, but I just can't seem to find it. All help and suggestions are appreciated. Thanks.

ptrn
  • 4,902
  • 6
  • 28
  • 30

1 Answers1

1

I think you probably want the function we devised under this related question.
With this function, your code fragment might look like this:


FOR loop_helper1 IN
    SELECT id, name1, name2, name3, nameN,
    FROM   table1
LOOP
    FOR loop_helper2 IN
        SELECT name
        FROM   table2
    LOOP
       loop_helper1 := public.setfield (loop_helper1
                                      ,loop_helper2.name
                                      ,function_call(123));
    END LOOP;
END LOOP;

There may be simpler ways, especially if you want to assign a whole composite type ...

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • For new readers: there are simpler and much faster ways to do this with either `hstore` extension (PG 9.0) or using the new built-in `json` functions that came with PG 9.3/9.4. The new solutions (and benchmarks) are added to the question @Erwin linked to: http://stackoverflow.com/a/28673097/1914376 – Geir Bostad Mar 15 '15 at 16:18