Put Variable on query in postgresql function

Question

I'm trying to make a function on psql. It will be triggered on insert on table. I want to inject an variable on my select. Can't get working ...

CREATE OR REPLACE FUNCTION updateHistoricLongTime()
RETURNS void AS $$
 DECLARE 
 hour_nb int; 
 index_hour int;
 saved_hours int;
 tmp_counter int; 
 BEGIN
 hour_nb := 0;
 index_hour := 1;
 saved_hours := 2160;
 tmp_counter := 0;
 SELECT COUNT(*) FROM locationhistoric WHERE type='hour' AND idLocation=6 INTO hour_nb;
 IF (hour_nb<saved_hours) THEN
 FOR i IN 1 .. saved_hours LOOP
 SELECT COUNT(*) FROM visits 
WHERE stend < (timestamp '2017-11-29 15:00' - interval **>> index_hour<<<** - 1 ' hour') AND stend > (timestamp '017-11-29 15:00' - interval **>>index_hour <<<**' hour') AND location_id=6 AND duration>0 INTO tmp_counter;
 index_hour := index_hour + 1;
 END LOOP;
 END IF;
 END;
 $$
 LANGUAGE 'plpgsql' IMMUTABLE;

How can I inject variable index_hour in my SELECT COUNT(*) FROM Visits ...

EDIT: It's just syntax issue, but I can't manage to find the right way !

The result in command line:

ERROR: syntax error at or near "index_hour"
LINE 16: ... stend < (timestamp '2017-11-29 15:00' - interval index_hour...

Thanks a lot, The solution

CREATE OR REPLACE FUNCTION updateHistoricLongTime()
RETURNS void AS $$
 DECLARE 
 hour_nb int; 
 index_hour int;
 saved_hours int;
 tmp_counter int; 
 index_hour_minor int;
 BEGIN
 hour_nb := 0;
 index_hour := 1;
 index_hour_minor := 0;
 saved_hours := 2160;
 SELECT COUNT(*)
 INTO hour_nb
 FROM locationhistoric 
 WHERE type='hour' 
 AND idLocation=6;
 IF (hour_nb<saved_hours) THEN
 FOR i IN 1 .. saved_hours LOOP
 SELECT COUNT(*) 
 INTO tmp_counter 
 FROM visits
 WHERE start > timestamp '2017-11-29 15:00' - ( interval '1 hour' * index_hour ) 
 AND start < timestamp '2017-11-29 15:00' - ( interval '1 hour' * index_hour_minor) 
 AND location_id=6 
 AND duration>0;
 INSERT INTO locationhistoric
 (type, date, counter, idLocation)
 VALUES( 'hour',
 timestamp '2017-11-29 15:00' - ( interval '1 hour' * index_hour_minor),
 tmp_counter,
 6);
 index_hour_minor := index_hour_minor + 1;
 index_hour := index_hour + 1;
 END LOOP;
 END IF;
END;
$$
LANGUAGE plpgsql;

• Vao Tsun: what and where you want to put?.. why don't you want the fn() to return a trigger instead?..
• user330315: Unrelated, but: the language name is an identifier. Do not put it in single quotes

Answer 1

The value specified for an interval can't be passed as a variable. However if the base unit is always an hour you can multiply a one our interval with the desired number of ours, e.g.

interval '1' hour * 5

will return 5 hours. The 5 can be a parameter. So your query should be:

SELECT COUNT(*) 
 INTO tmp_counter
FROM visits 
WHERE stend < (timestamp '2017-11-29 15:00' - (interval '1' hour * index_hour)) 
 AND stend > (timestamp '2017-11-29 15:00' - (interval '1' hour * index_hour)) 
 AND location_id=6 
 AND duration > 0;

Answer 2

The syntax you want to get for your query (where index_hour = 8, for example) is:

select count(*) 
from visits 
where 
 stend < (timestamp '2017-11-29 15:00' - interval '7 hour') and 
 stend > (timestamp '2017-11-29 15:00' - interval '8 hour') and
 location_id = 6 and 
 duration > 0;

Note where the quotes are. This means that your variable has to be inside quotes in pl/pgsql and that means it will be treated as a literal.

The solution is:

execute 
 'select count(*) ' ||
 'from visits ' ||
 'where ' ||
 'stend < (timestamp ''2017-11-29 15:00'' - interval ''' || (index_hour - 1) || ' hour'') and ' ||
 'stend > (timestamp ''2017-11-29 15:00'' - interval ''' || index_hour || ' hour'') and ' ||
 'location_id = 6 and ' ||
 'duration > 0'

To save me setting up your data I've written a simpler example using a table that I have (driver) so that I could test. Note that you have to use 2 single quotes to get one single quote into a string and that means counting quotes carefully.

create function a47768241() returns integer
as $body$
declare
 index_hour int;
 id integer;
begin
 index_hour = 8;
 execute
 'select id ' ||
 'from driver ' ||
 'where ' ||
 'from_date_time < (timestamp ''2013-04-22 16:00:00'' - interval ''' || (index_hour - 1) || ' hour'') '
 into id;
 return id;
end;
$body$
language 'plpgsql';

Simple test:

# select a47768241();
 a47768241 
-----------
 158
(1 row)

Using the result value to check the date:

# select * from driver where id = a47768241();
 id | vehicle_id | person_id | from_date_time | to_date_time | created_at | updated_at 
-----+------------+-----------+---------------------------+---------------------------+----------------------------+------------
 158 | 6784 | 15430 | 2012年09月13日 17:00:41.39778 | 2012年09月14日 01:54:46.39778 | 2016年06月03日 16:43:11.456063 | 
(1 row)

Answer 3

just concat the interval value, like

interval concat(index_hour - 1 , ' hour')