1

Let's say I have following postgres function:

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;

I can select from this function like that:

SELECT * FROM get_summary(10);

But what if I want to select like that:

SELECT value, amount FROM get_summary(10);

But then I receive following error:

[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.

How can I select particular columns from postgres function?

Improve this question

1 Answer 1

Reset to default
4

try use alias to table for example:

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;

or try this:

SELECT t.value, t.amount FROM get_summary(10) t;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.