有没有办法在 PostgreSQL 查询中定义命名常量? [英] Is there a way to define a named constant in a PostgreSQL query?

问题描述

有没有办法在 PostgreSQL 查询中定义一个命名常量?例如:

Is there a way to define a named constant in a PostgreSQL query? For example:

MY_ID = 5;
SELECT * FROM users WHERE id = MY_ID;

推荐答案

这个问题之前有人问过 (你如何在 PostgreSQL 中使用脚本变量?).但是，我有时会使用一个技巧来进行查询:

This question has been asked before (How do you use script variables in PostgreSQL?). However, there is a trick that I use for queries sometimes:

with const as (
    select 1 as val
)
select . . .
from const cross join
     <more tables>

也就是说，我定义了一个名为 const 的 CTE，其中定义了常量.然后我可以在任何级别将其交叉加入我的查询中，任意次数.我发现这在我处理日期时特别有用，并且需要跨多个子查询处理日期常量.

That is, I define a CTE called const that has the constants defined there. I can then cross join this into my query, any number of times at any level. I have found this particularly useful when I'm dealing with dates, and need to handle date constants across many subqueries.

这篇关于有没有办法在 PostgreSQL 查询中定义命名常量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！