Data arguments passing to user-defined function postgresql

Question

I'm trying to create user-defined function which will return SUM from expenses view based on given dates.

CREATE FUNCTION budget.getTotalAmountFromView (startDate DATE, endDate DATE)
RETURNS DECIMAL AS $$
DECLARE
    totalValue DECIMAL := 0;
BEGIN
    SELECT INTO totalValue sum(amount) from budget.epenses_overview where transaction_date >= startDate AND transaction_date <= endDate;
    RETURN totalValue;
END;
$$ LANGUAGE plpgsql;

I am trying to invoke it using:

SELECT * FROM budget.getTotalAmountFromView(TO_DATE(20190201, YYYYMMDD), TO_DATE(20190225, YYYYMMDD));

But it returns error

AFTER CHANGES:
Function shall be assigned to the right schema -> budget;
and invoke:
SELECT budget.getTotalAmountFromView('20190201'::DATE, '20190225'::DATE);

What error does it return?

Bergi
– Bergi

2020-02-12 20:51:54 +00:00
Commented Feb 12, 2020 at 20:51 — Bergi
– Bergi, Commented Feb 12, 2020 at 20:51

Lukasz Szozda · Accepted Answer · 2020-02-12 20:37:58Z

1

You don't need FROM for scalar function:

SELECT budget.getTotalAmountFromView22('20190201'::DATE, '20190225'::DATE);

answered Feb 12, 2020 at 20:37

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2020-02-12 20:48:55Z

0

You are lacking single quotes around the date and format strings.

TO_DATE(20190201, YYYYMMDD) should be TO_DATE('20190201', 'YYYYMMDD')

edited Feb 12, 2020 at 20:48

user330315

answered Feb 12, 2020 at 20:46

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Data arguments passing to user-defined function postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related