postgresql - 访问给定字段名称作为文本字符串的 postgres 字段

Question

我在 postgres 中有一张表:

create table fubar (
name1 text,
name2 text, ...,
key integer);

我想编写一个函数，在给定列名的情况下从 fubar 返回字段值:

function getFubarValues(col_name text, key integer) returns text ...

其中 getFubarValues 返回键标识的行中指定列的值。看起来这应该很容易。

我很迷茫。有人可以帮忙吗？谢谢。

Answer 1

Klin 的回答是对所提出问题的一种很好(即安全)的方法，但它可以简化:

PostgreSQL 的 -> 运算符允许表达式。例如:

CREATE TABLE test (
    id SERIAL,
    js JSON NOT NULL,
    k TEXT NOT NULL
);
INSERT INTO test (js,k) VALUES ('{"abc":"def","ghi":"jkl"}','abc');
SELECT js->k AS value FROM test;

生产

 value
-------
 "def"

所以我们可以将它与 row_to_json 结合起来:

CREATE TABLE test (
    id SERIAL,
    a TEXT,
    b TEXT,
    k TEXT NOT NULL
);
INSERT INTO test (a,b,k) VALUES
    ('foo','bar','a'),
    ('zip','zag','b');
SELECT row_to_json(test)->k AS value FROM test;

产生:

 value 
-------
 "foo"
 "zag"

这里我从表本身获取 key ，但当然您可以从任何源/表达式获取它。这只是一个值。另请注意，返回的结果是 JSON 值类型(它不知道它是文本、数字还是 bool 值)。如果您希望它是文本，只需将其转换为:(row_to_json(test)->k)::TEXT

---

既然问题本身已经得到解答，下面就是为什么您不应该这样做，以及您应该怎么做!

永远不要相信任何数据。即使它已经存在于您的数据库中，您也不应该相信它。我在此处发布的方法可安全抵御 SQL 注入(inject)攻击，但攻击者仍可将 k 设置为 'id' 并查看本不打算显示的列给他们。

更好的方法是在考虑此类查询的情况下构建数据。 Postgres 对此有一些优秀的数据类型； HSTORE 和 JSON/JSONB。将您的动态列合并为具有其中一种类型的单个列(我建议使用 HSTORE，因为它简单且通常更完整)。

这有几个优点:您的模式定义明确，如果您添加更多动态列则不需要更改，您不需要执行昂贵的重新转换(即 row_to_json)，并且您可以利用列上的索引(感谢 PostgreSQL 的功能索引)。

相当于我上面写的代码是:

CREATE EXTENSION HSTORE; -- necessary if you're not already using HSTORE
CREATE TABLE test (
    id SERIAL,
    cols HSTORE NOT NULL,
    k TEXT NOT NULL
);
INSERT INTO test (cols,k) VALUES
    ('a=>"foo",b=>"bar"','a'),
    ('a=>"zip",b=>"zag"','b');
SELECT cols->k AS value FROM test;

或者，为了在插入时自动转义您的值，您可以使用以下之一:

INSERT INTO test (cols,k) VALUES
    (hstore( 'a', 'foo' ) || hstore( 'b', 'bar' ), 'a'),
    (hstore( ARRAY['a','b'], ARRAY['zip','zag'] ), 'b');

参见 http://www.postgresql.org/docs/9.1/static/hstore.html了解更多详情。