0

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.

4
  • Somehow what I am returning, is not getting linked to the OUT.. I havent been able to figure out what I am missing. I have also stripped off the date and only tried with the edition, but still I am not able to get it to output the value. So : WHEN (edition = 1 AND (period_from <= issue AND period_to >= issue)) became WHEN edition = 1 Commented Jun 12, 2018 at 17:57
  • Please, don't use ALL CAPS in the title. It's considered YELLING. Commented Jun 12, 2018 at 18:07
  • 1
    Sorry, probably out of frustration. Editing it. Commented Jun 12, 2018 at 18:08
  • 1
    Happens to the best of us. First step to solving a problem is often to calm down, not always an easy thing though. Commented Jun 12, 2018 at 18:09

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.