如何获取PL/pgSQL中动态生成的字段名称的值

问题描述

示例代码精简了说明问题的基本要点:

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?

推荐答案

对功能 to_json() ，对于复合类型，它返回一个以列名作为键的json对象:

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; 

这篇关于如何获取PL/pgSQL中动态生成的字段名称的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！