How to get the value of a dynamically generated field name in PL/pgSQL

Question

Sample code trimmed down the the bare essentials to demonstrate question:

CREATE OR REPLACE FUNCTION mytest4() RETURNS TEXT AS $$
DECLARE
   wc_row wc_files%ROWTYPE;
   fieldName TEXT;
BEGIN
    SELECT * INTO wc_row FROM wc_files WHERE "fileNumber" = 17117;
 -- RETURN wc_row."fileTitle"; -- This works. I get the contents of the field.
    fieldName := 'fileTitle';
 -- RETURN format('wc_row.%I',fieldName); -- This returns 'wc_row."fileTitle"'
                                          -- but I need the value of it instead.
    RETURN EXECUTE format('wc_row.%I',fieldName); -- This gives a syntax error.
 END;
$$ LANGUAGE plpgsql;

How can I get the value of a dynamically generated field name in this situation?

The right way to using the `EXECUTE` statement in such cases: `execute format('select $1.%I',fieldName) into result using wc_row;` and then `return result;` (variable `result` should be declared) — Abelisto, Oct 01 '16 at 16:41
Fantastic! I struggled with this for many hours. THANK YOU!!! — Tim Duncklee, Oct 01 '16 at 17:05

score 1 · Accepted Answer · answered Oct 01 '16 at 16:03

Use a trick with the function to_json(), which for a composite type returns a json object with column names as keys:

create or replace function mytest4() 
returns text as $$
declare
   wc_row wc_files;
   fieldname text;
begin
    select * into wc_row from wc_files where "filenumber" = 17117;
    fieldname := 'filetitle';
    return to_json(wc_row)->>fieldname;
end;
$$ language plpgsql;

score 0 · Answer 2 · edited May 23 '17 at 12:19

You don't need tricks. EXECUTE does what you need, you were on the right track already. But RETURN EXECUTE ... is not legal syntax.

CREATE OR REPLACE FUNCTION mytest4(OUT my_col text) AS
$func$
DECLARE
   field_name text := 'fileTitle';
BEGIN
   EXECUTE format('SELECT %I FROM wc_files WHERE "fileNumber" = 17117', field_name)
   INTO my_col;  -- data type coerced to text automatically.
END
$func$  LANGUAGE plpgsql;

Since you only want to return a scalar value use EXECUTE .. INTO ... - optionally you can assign to the OUT parameter directly.
RETURN QUERY EXECUTE .. is for returning a set of values.
Use format() to conveniently escape identifiers and avoid SQL injection. Provide identifiers names case sensitive! filetitle is not the same as fileTitle in this context.
- Are PostgreSQL column names case-sensitive?
Use an OUT parameter to simplify your code.

How to get the value of a dynamically generated field name in PL/pgSQL

2 Answers2

Linked