PostgreSQL - 向查询返回的集合添加额外变量

Question

我有这样一种情况，我从这样的 postgre 表中返回一组值

RETURN QUERY
SELECT sale_set.sau_price_median AS avg_sale, sale_set.area_range_id AS area_range FROM 

(SELECT apart_comp_sale_price_est.sau_price_median, apart_comp_sale_price_est.area_range_id
FROM apart_comp_sale_price_est
WHERE
    (apart_comp_sale_price_est.county_id = _county_id) AND
    (apart_comp_sale_price_est.parish_id = _parish_id) AND
    (apart_comp_sale_price_est.city_id = _city_id) AND
    (apart_comp_sale_price_est.precint_id = _precint_id)
ORDER BY apart_comp_sale_price_est.precint_id NULLS LAST,
     apart_comp_sale_price_est.city_id NULLS LAST,
     apart_comp_sale_price_est.parish_id NULLS LAST,
     apart_comp_sale_price_est.county_id NULLS LAST
LIMIT 6) AS sale_set

ORDER BY sale_set.area_range_id;

这会根据需要工作并返回 6 行值，每行两个变量。

问题:我有一个常量变量:_const ='constant';

如何将这个自变量 (_const) 作为第三个变量添加到集合中。这样返回的表看起来像这样

平均销售额 |区域范围 |常量

并且 const 会在每一行上重复自身(不需要，但我认为这是唯一合乎逻辑的解决方案)。

你对我有什么建议吗？

Answer 1

这就像将它添加到选择查询中一样简单，如果你想为所有这些返回真，那么执行 true AS _const 或 'string_value' AS _const 对于一个字符串

RETURN QUERY
SELECT sale_set.sau_price_median AS avg_sale, sale_set.area_range_id AS area_range, 'constant' AS _const 
FROM 

(SELECT apart_comp_sale_price_est.sau_price_median, apart_comp_sale_price_est.area_range_id
FROM apart_comp_sale_price_est
WHERE
    (apart_comp_sale_price_est.county_id = _county_id) AND
    (apart_comp_sale_price_est.parish_id = _parish_id) AND
    (apart_comp_sale_price_est.city_id = _city_id) AND
    (apart_comp_sale_price_est.precint_id = _precint_id)
ORDER BY apart_comp_sale_price_est.precint_id NULLS LAST,
     apart_comp_sale_price_est.city_id NULLS LAST,
     apart_comp_sale_price_est.parish_id NULLS LAST,
     apart_comp_sale_price_est.county_id NULLS LAST
LIMIT 6) AS sale_set

ORDER BY sale_set.area_range_id;