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;

what and where you want to put?.. why don't you want the fn() to return a trigger instead?.. — Vao Tsun
– Vao Tsun, Commented Dec 12, 2017 at 8:37
Unrelated, but: the language name is an identifier. Do not put it in single quotes — user330315
– user330315, Commented Dec 12, 2017 at 8:59

user330315 · Accepted Answer · 2017-12-12 09:03:09Z

2

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;

answered Dec 12, 2017 at 9:03

user330315

Sign up to request clarification or add additional context in comments.

Comments

Ron Ballard · Accepted Answer · 2017-12-12 11:39:53Z

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)

Vao Tsun · Accepted Answer · 2017-12-12 09:02:05Z

0

just concat the interval value, like

interval concat(index_hour - 1 , ' hour')

answered Dec 12, 2017 at 9:02

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Put Variable on query in postgresql function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related