有没有一种方法可以在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;

推荐答案

此问题之前已被问过(

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屋！