0

"structure of query does not match function result type" this isssue occurred when i try to execute the this function.

CREATE OR REPLACE FUNCTION historicalavg01() RETURNS SETOF text AS
$$
DECLARE starttime timestamp;
DECLARE endtime timestamp;
DECLARE total_hour_avg DOUBLE PRECISION;
l_row RECORD;
BEGIN
CREATE TEMP TABLE historical_temp(Histroical DOUBLE PRECISION,time timestamp);
total_hour_avg := 0;
FOR b IN 0 .. 23 LOOP
    FOR a IN 0 .. 27 LOOP

        starttime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + (b * INTERVAL '1 hour');

        endtime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + ((b + 1) * INTERVAL '1 hour');

        total_hour_avg := total_hour_avg + (SELECT Avg("activepower") AS "Avg" FROM sca_meter WHERE "time" between starttime AND endtime);

        IF(a = 27) THEN 
            INSERT INTO historical_temp SELECT AVG(total_hour_avg) AS "Histroical Avg", CURRENT_DATE - ((b + 1) * interval '23 hour') AS "time";
        END IF;

    END LOOP;
END LOOP;

RETURN QUERY SELECT * FROM historical_temp;

END$$ LANGUAGE plpgsql;
SELECT * FROM historicalavg01();

Please give the solution for it. thanks in adavance.

1
  • 2
    The declare keyword is only needed once, not for every variable declaration
    – user1822
    Feb 6, 2020 at 8:58

1 Answer 1

1

To return a TABLE add the table definition:

CREATE OR REPLACE FUNCTION historicalavg01() 
RETURNS TABLE
(
    Hist double precision,
    Tm   timestamp
) AS
$$
DECLARE starttime timestamp;
DECLARE endtime timestamp;
DECLARE total_hour_avg DOUBLE PRECISION;
l_row RECORD;
BEGIN
CREATE TEMP TABLE historical_temp(Histroical DOUBLE PRECISION,time timestamp);
total_hour_avg := 0;
FOR b IN 0 .. 23 LOOP
    FOR a IN 0 .. 27 LOOP

        starttime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + (b * INTERVAL '1 hour');

        endtime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + ((b + 1) * INTERVAL '1 hour');

        --total_hour_avg := total_hour_avg + (SELECT Avg("activepower") AS "Avg" FROM sca_meter WHERE "time" between starttime AND endtime);
        total_hour_avg := b * 0.5;

        IF(a = 27) THEN 
            INSERT INTO historical_temp SELECT AVG(total_hour_avg) AS "Histroical Avg", CURRENT_DATE - ((b + 1) * interval '23 hour') AS "time";
        END IF;

    END LOOP;
END LOOP;

RETURN QUERY SELECT * FROM historical_temp;

END$$ LANGUAGE plpgsql;
SELECT * FROM historicalavg01();
hist | tm                 
:--- | :------------------
0    | 2020-02-05 01:00:00
0.5  | 2020-02-04 02:00:00
1    | 2020-02-03 03:00:00
1.5  | 2020-02-02 04:00:00
2    | 2020-02-01 05:00:00
2.5  | 2020-01-31 06:00:00
3    | 2020-01-30 07:00:00
3.5  | 2020-01-29 08:00:00
4    | 2020-01-28 09:00:00
4.5  | 2020-01-27 10:00:00
5    | 2020-01-26 11:00:00
5.5  | 2020-01-25 12:00:00
6    | 2020-01-24 13:00:00
6.5  | 2020-01-23 14:00:00
7    | 2020-01-22 15:00:00
7.5  | 2020-01-21 16:00:00
8    | 2020-01-20 17:00:00
8.5  | 2020-01-19 18:00:00
9    | 2020-01-18 19:00:00
9.5  | 2020-01-17 20:00:00
10   | 2020-01-16 21:00:00
10.5 | 2020-01-15 22:00:00
11   | 2020-01-14 23:00:00
11.5 | 2020-01-14 00:00:00

db<>fiddle here

Have a look at this tutorial about it.

3
  • I guess the correct answer depends on what OP wants, which we do not know. Prehaps the query result should be formatted as a string? Feb 6, 2020 at 9:04
  • @LaurenzAlbe you're right, I'll wait for OP explanations.
    – McNets
    Feb 6, 2020 at 9:06
  • @McNets thanks lot, issue resolved.
    – Avz Vicky
    Feb 6, 2020 at 10:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.