1

I create a function and pass the parameters to it when i execute that i get this Error : cannot open SELECT query as cursor PL/pgSQL function getlocation_for_excel(bigint[],text,text,boolean) line 6 at RETURN QUERY

here is my code:

CREATE OR REPLACE FUNCTION public.getLocation_for_excel(
_device_id bigint[],
_start text,
_end text,
_from boolean)
RETURNS TABLE(device_id bigint, lat double precision, long double precision, 
ntrip_user character varying, insert_time text) 
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000

AS $BODY$
declare 
ss text;
ee text;
begin
return query 
select concat(_start, ' 00:00:00') into ss;
select concat(_end, ' 23:59:59') into ee;
    SELECT
        v1.device_id,
        v1.geo_x,
        v1.geo_y,
        v1.ntrip_user,
        v1.insert_time      
    FROM view_locations2022 as v1 WHERE device_id = ANY(_device_id)
     AND
          (
              (
                 ((_start <> '' and _end <> '') and v1.insert_time between 
                  ss::timestamp and ee::timestamp) or
                ((_from = true and _start <> '' and _end ='' ) and v1.insert_time >= 
                   ss::timestamp) or
                ((_from = false and _start <> '' and _end ='') and 
                   v1.insert_time::date = ss::date) or
                (_start = '' and _end <> '' and v1.insert_time <= ee::timestamp)
              ) or (_start = '' and _end = '')
          )
    
    ORDER BY v1.insert_time desc;
end;
$BODY$;

what is my problem in that ?

1 Answer 1

0

The return query must precede the query you want to return, not the variable assignments. Assignment is also done better using := (or =) using SELECT is slightly slower.

begin
  -- no return query here!!
  ss := concat(_start, ' 00:00:00');
  ee := concat(_end, ' 23:59:59');

  return query  --<< this is important
    SELECT
        v1.device_id,
        v1.geo_x,
        v1.geo_y,
        v1.ntrip_user,
        v1.insert_time      
    FROM view_locations2022 as v1 WHERE device_id = ANY(_device_id)
     AND
          (
              (
                 ((_start <> '' and _end <> '') and v1.insert_time between 
                  ss::timestamp and ee::timestamp) or
                ((_from = true and _start <> '' and _end ='' ) and v1.insert_time >= 
                   ss::timestamp) or
                ((_from = false and _start <> '' and _end ='') and 
                   v1.insert_time::date = ss::date) or
                (_start = '' and _end <> '' and v1.insert_time <= ee::timestamp)
              ) or (_start = '' and _end = '')
          )
    
    ORDER BY v1.insert_time desc;
end;
4
  • i changed the fuction but I get the error: operator does not exist: text >= timestamp without time zone LINE 11: ... ((_start <> '' and _end <> '') and v1.insert_time between Jan 16 at 12:20
  • Do you have any idea? Jan 16 at 12:54
  • Well, you are trying to compare a text value with a timestamp value. You need to cast the text values - but that is essentially a new question. Jan 16 at 12:55
  • yes do you know what is that problem? Jan 16 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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