Issue of structure of query does not match function result type

Question

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

• user1822: The declare keyword is only needed once, not for every variable declaration

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

• Laurenz Albe: 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?
• McNets: @LaurenzAlbe you're right, I'll wait for OP explanations.