Postgres - Dynamically referencing columns from record variable

Question

I'm having trouble referencing record variable type columns dynamically. I found loads of tricks online, but with regards to triggers mostly and I really hope the answer isn't "it can't be done"... I've got a very specific and simple need, see my example code below;

First I have an array containing a list of column names called "lCols". I loop through a record variable to traverse my data, replacing values in a paragraph which exactly match my column names.

DECLARE lTotalRec RECORD;        
DECLARE lSQL text;
DECLARE lCols varchar[];

p_paragraph:= 'I am [names] and my surname is [surname]';

lSQL := 
    'select         
        p.names,
        p.surname
    from 
        person p
    ';

FOR lTotalRec IN
    execute lSQL        
LOOP
    -- Loop through the already created array of columns to replace the values in the paragraph
    FOREACH lVal IN ARRAY lCols
    LOOP
        p_paragraph := replace(p_paragraph,'[' || lVal || ']',lTotalRec.lVal); -- This is where my problem is, because lVal is not a column of lTotalRec directly like this      
    END LOOP;

    RETURN NEXT;
END LOOP;

My return value is the paragraph amended for each record in "lTotalRec"

Nick Barnes · Accepted Answer · 2015-12-22 10:55:25Z

1

You could convert your record to a json value using the row_to_json() function. Once in this format, you can extract columns by name, using the -> and ->> operators.

In Postgres 9.4 and up, you can also make use of the more efficient jsonb type.

DECLARE lJsonRec jsonb;

...

FOR lTotalRec IN
    execute lSQL        
LOOP
    lJsonRec := row_to_json(lTotalRec)::jsonb;
    FOREACH lVal IN ARRAY lCols
    LOOP
        p_paragraph := replace(p_paragraph, '[' || lVal || ']', lJsonRec->>lVal);
    END LOOP;

    RETURN NEXT;
END LOOP;

See the documentation for more details.

answered Dec 22, 2015 at 10:55

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rudi Louw Over a year ago

Works like a charm, I was on the right track but just didn't get to the point where you can use JSON inline like this, great answer, thanks

Andomar · Accepted Answer · 2015-12-22 11:02:19Z

0

You can convert a row to JSON using row_to_json(), and then retrieve the column names using json_object_keys().

Here's an example:

drop table if exists TestTable;

create table TestTable (col1 text, col2 text);
insert into TestTable values ('a1', 'b1'), ('a2', 'b2');

do $$declare
    sql text;
    rec jsonb;
    col text;
    val text;
begin
sql := 'select row_to_json(row) from (select * from TestTable) row';
for rec in execute sql loop
    for col in select * from jsonb_object_keys(rec) loop
        val := rec->>col;
        raise notice 'col=% val=%', col, val;
    end loop;
end loop;
end$$;

This prints:

NOTICE:  col=col1 val=a1
NOTICE:  col=col2 val=b1
NOTICE:  col=col1 val=a2
NOTICE:  col=col2 val=b2
DO

edited Dec 22, 2015 at 11:02

answered Dec 22, 2015 at 10:56

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

Postgres - Dynamically referencing columns from record variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related