catching select query return value in postgresql function and use it

Question

I want to execute this function. But it got error said

ERROR:

syntax error at or near ":="

LINE 7: select result:=MAX(path_history_id)as path INTO result from...

In this function I want to:

execute select with (MAX) and it will return maximum id from a table;
catch that value (it is an integer value);
put that value into last select query where condition.

I cant find a way in postgresql to do this.

CREATE OR REPLACE FUNCTION memcache(IN starting_point_p1 character varying, IN ending_point_p1 character varying)

RETURNS TABLE(path integer, movement_id_out integer, object_id_fk_out integer, path_history_id_fk_out integer, walking_distance_out real, angel_out real, direction_out character varying, time_stamp_out timestamp without time zone, x_coordinate_out real, y_coordinate_out real, z_coordinate_out real) AS
$BODY$
    DECLARE result int;
    BEGIN
    
    select result:=MAX(path_history_id)as path INTO result from path_history_info where starting_point=starting_point_p1 and ending_point =ending_point_p1 and achieve='1';
    return query
    select * from movement_info where path_history_id_fk=result;    
    END;
    $BODY$
  LANGUAGE plpgsql

e4c5 · Accepted Answer · 2016-04-28 14:25:48Z

1

Syntax Error

The first query inside your function needs to be changed as follows:

select MAX(path_history_id)as path INTO result 
  from path_history_info 
    where starting_point=starting_point_p1 
     and ending_point =ending_point_p1 and achieve='1';

A single Query

You don't actually need a stored procedure for this. A single query can achieve the same result.

select * from movement_info where path_history_id_fk = 
 (SELECT MAX(path_history_id) FROM path_history_info 
    where starting_point=starting_point_p1 
     and ending_point =ending_point_p1 and achieve='1';

answered Apr 28, 2016 at 14:25

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

Collectives™ on Stack Overflow

catching select query return value in postgresql function and use it

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related