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?

Putscher

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) – Wedlock

Fantastic! I struggled with this for many hours. THANK YOU!!! – Putscher

Answer 1

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; 

Scorekeeper

Answer 2

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.

Gottlieb