如何选择PostgreSQL中另一列中的名称为值的列？

发布于 2025-02-14 01:06:52 字数 599 浏览 0 评论 0原文

我知道这不是有效的SQL，但是我想做类似的事情：

SELECT items.{SELECT items.preferred_column}

详细说明，要实现我要实现的目标，我可以在陈述时写一个漫长的案例：

SELECT
CASE WHEN items.preferred_column = "column_a" THEN items.column_a
CASE WHEN items.preferred_column = "column_b" THEN items.column_b
CASE WHEN items.preferred_column = "column_c" THEN items.column_c
... and so on...

但这似乎是错误的。我宁愿编写一个查询，以查看itemss.preferred_column的值并加载该列。

这可能吗？

我的用例涉及一个主动记录（用于导轨的ORM）查询，这限制了我。例如，我无法使用“进入”。

在不创建SQL函数的情况下执行此操作将首选，尽管如果不创建SQL函数，那将是一件好事。

事先感谢您的专业知识！

原文

I know this isn't valid SQL, but I'd like to do something like:

SELECT items.{SELECT items.preferred_column}

To elaborate, to achieve what I'm trying to achieve, I could write a long case when statement:

SELECT
CASE WHEN items.preferred_column = "column_a" THEN items.column_a
CASE WHEN items.preferred_column = "column_b" THEN items.column_b
CASE WHEN items.preferred_column = "column_c" THEN items.column_c
... and so on...

But that seems wrong. I would prefer to write a query that looks at the value of items.preferred_column and loads that column.

Is this possible?

My use case involves an Active Record (the ORM for Rails) query, which limits me. I'm not able to use "INTO" for example.

Doing this without creating a SQL function would preferred, though if it's not possible without creating a SQL function that would be good to know.

Thanks in advance for lending your expertise!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浪漫之都 2025-02-21 01:06:52

您可以尝试使用row_to_json（）转换表行，然后使用json_each（），您可以在preferred_column上加入所得的“键”字段：

WITH CTE AS (
 SELECT 
     row_to_json(Z.*)::jsonb as rcr, 
     row_number() over(partition by null order by <whatever comparator clause>) as rn,
     Z.*
 FROM items Z)

SELECT b.value, a.* 
FROM CTE a, jsonb_each(rcr) b, CTE c 
WHERE c.rn=a.rn AND b.key = ( c.preferred_column )

请注意，这本质上是作为Quasi-pivot工作的，因此您需要维护一个从jsonb_each的set-return中提取适当的键值对时，索引（row_number invocation）自行加入表。铸造给JSONB会有所帮助，因为二进制形式将按照对象本身内的键顺序将键值对按字母顺序排列。

如果您需要将结果值作为文本字符串而不是JSON rimitive获取，则可以

b.value #>>'{}'

使用JSONB_EACH_TEXT（）来执行，这将保留任何JSON列。

You can try transforming the table rows with row_to_json() and then using json_each(), you can join the resultant "key" field on the preferred_column:

WITH CTE AS (
 SELECT 
     row_to_json(Z.*)::jsonb as rcr, 
     row_number() over(partition by null order by <whatever comparator clause>) as rn,
     Z.*
 FROM items Z)

SELECT b.value, a.* 
FROM CTE a, jsonb_each(rcr) b, CTE c 
WHERE c.rn=a.rn AND b.key = ( c.preferred_column )

Note that this essentially operates as a quasi-pivot, so you'll need to maintain an index (the row_number invocation) to self-join the table when extracting the appropriate key-value pairs from jsonb_each's set-return. Casting to jsonb will be helpful in that the binary form will alphabetize the key-value pairs by key order within the object itself.

If you need to get the resultant value as a text string instead of a json primitive, you can do