UPDATE (for anyone who might be stuck with this):
RETURN QUERY SELECT works. It is written elsewhere that PERFORM is used in conjuction with void. So probably that is the reason. Thanks to the links from a_horse_with_no_name.
THE PROBLEM WAS:
===============
The following is the function I am trying to execute:
CREATE OR REPLACE FUNCTION get_edition_data (date[], OUT fc_copies bigint, OUT cs_copies bigint, OUT ms_copies bigint)
RETURNS SETOF record AS $$
DECLARE
issue DATE;
BEGIN
FOREACH issue IN ARRAY $1
LOOP
SELECT
SUM (
CASE
WHEN (edition = 1 AND (period_from <= issue AND period_to >= issue)) THEN
copies
ELSE
0
END
),
SUM (
CASE
WHEN (edition = 2 AND (period_from <= issue AND period_to >= issue)) THEN
copies
ELSE
0
END
),
SUM (
CASE
WHEN (edition = 3 AND (period_from <= issue AND period_to >= issue)) THEN
copies
ELSE
0
END
)
FROM
public.subscriptions;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
I am invoking this function using:
SELECT public.get_edition_data(ARRAY[CAST ('2018-01-01' AS DATE)])
and here I get the error:
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function get_edition_data(date[]) line 7 at SQL statement
I tried creating the function using examples from postgres and other places at S.O. In some places I have seen that the return generally points to a record variable put in DECLARE. But here I want to loop through the dates that I receive.
------- UPDATE
Based on what horse_with_no_name has provided, I went through the links and found that if I replace SELECT with PERFORM it works in plpgsql. Now the errors have been silenced. But I get no output. The updated code is :
CREATE OR REPLACE FUNCTION get_edition_data (date[], OUT issue date, OUT fc_copies bigint, OUT cs_copies bigint, OUT ms_copies bigint)
RETURNS SETOF record AS $$
DECLARE
issue DATE;
BEGIN
FOREACH issue IN ARRAY $1
LOOP
RAISE NOTICE '%', issue;
PERFORM
issue,
SUM (
CASE
WHEN (edition = 1 AND (period_from <= issue AND period_to >= issue)) THEN
copies
ELSE
0
END
),
SUM (
CASE
WHEN (edition = 2 AND (period_from <= issue AND period_to >= issue)) THEN
copies
ELSE
0
END
),
SUM (
CASE
WHEN (edition = 3 AND (period_from <= issue AND period_to >= issue)) THEN
copies
ELSE
0
END
)
FROM
public.subscriptions;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
I am now querying using the following:
SELECT * FROM public.get_edition_data(ARRAY[CAST ('2018-05-01' AS DATE),CAST ('2018-03-01' AS DATE)])
I get no records, but it loops through twice, and I am getting the notice showing the date.
If I strip all the functions away and use a simple select statement with the date hardcoded like :
SELECT SUM (
CASE
WHEN (edition = 1 AND (period_from <= '2018-03-01' AND period_to >= '2018-03-01')) THEN
copies
ELSE
0
END
),
SUM (
CASE
WHEN (edition = 2 AND (period_from <= '2018-03-01' AND period_to >= '2018-03-01')) THEN
copies
ELSE
0
END
),
SUM (
CASE
WHEN (edition = 3 AND (period_from <= '2018-03-01' AND period_to >= '2018-03-01')) THEN
copies
ELSE
0
END
)
FROM
public.subscriptions;
I get the following:
sum | sum | sum |
3 | 0| 0 |
So there are records and the select query works.