sql - 如何从 postgres 函数中选择特定的列？

Question

假设我有以下 postgres 函数:

CREATE OR REPLACE FUNCTION get_summary(
  IN param INT)
  RETURNS TABLE (
    value NUMERIC,
    amount NUMERIC) AS $$
BEGIN

  RETURN QUERY
  SELECT sum(value) AS value, sum(amount) AS amount FROM ...

END;
$$ LANGUAGE plpgsql;

我可以从这个函数中选择:

SELECT * FROM get_summary(10);

但是如果我想这样选择怎么办:

SELECT value, amount FROM get_summary(10);

但随后我收到以下错误:

[2017-06-28 12:49:53] [42702] ERROR: column reference "value" is ambiguous
[2017-06-28 12:49:53] Detail: It could refer to either a PL/pgSQL variable or a table column.

如何从 postgres 函数中选择特定的列？

Answer 1

尝试对表使用别名例如:

CREATE OR REPLACE FUNCTION get_summary(
  IN param INT)
  RETURNS TABLE (
    value NUMERIC,
    amount NUMERIC) AS $$
BEGIN

  RETURN QUERY
  SELECT sum(t.value), sum(t.amount) AS amount FROM your_table t

END;
$$ LANGUAGE plpgsql;

或者试试这个:

SELECT t.value, t.amount FROM get_summary(10) t;