CODEWITHSUNDEEP
arraysfunctionpostgresqlplpgsql
IT_info
IT_info

Reputation: 727

plpgsql function no results

I have done the following function in plpgsql. It works fine and the it returns 'OK', BUT for some reason it inputs nothing in the table temp.

CREATE OR REPLACE FUNCTION public.get_route(node integer[])
  RETURNS character varying AS
$BODY$DECLARE 
    i int := 0;
    _r record;
    vertex int;

BEGIN

    FOREACH i IN ARRAY node

    LOOP

    IF( i < (array_length(node, 1) - 1))

    THEN

        FOR _r IN EXECUTE 'select * from shortest_path(''
                   select id as id, source_id::int4 as source, target_id::int4 as target, cost, reverse_cost
                           from network_of_point'','|| node[i] ||','|| node[i+1] ||', true, true)'

        LOOP
            vertex := _r.vertex_id;

            EXECUTE 'insert into temp
                 select nextval(''road_intersection_id_seq''), point
                 from distinct_network_point
                 where distinct_network_point.id ='|| vertex;

        END LOOP;

    i = i + 1;

    END IF;

    END LOOP;

RETURN 'OK';

END;$BODY$

The following is the synatx I am using to call the function:

select get_route('{2,7}')

Upvotes: 0

Views: 103

Answers (2)

Ibrar Ahmed
Ibrar Ahmed

Reputation: 1089

Because of this line, inner part of code never executed.

IF( i < (array_length(node, 1) - 1))

Actually you are extracting value of i from node array

FOREACH i IN ARRAY node

which has value 2 or 7 as in your example and you are treating i as node index which is wrong. I have created a separate int variable value, now we can use i as array index.

I think you want to achieve this. 

CREATE OR REPLACE FUNCTION public.get_route(node integer[])
  RETURNS character varying AS
$BODY$
DECLARE
    i int := 1;
    _r record;
    vertex int;
    value int;

BEGIN

    FOREACH value IN ARRAY node
    LOOP
        IF( i < (array_length(node, 1)))
        THEN
            FOR _r IN EXECUTE 'select * from shortest_path(''
                select id as id, source_id::int4 as source, target_id::int4 as target, cost, reverse_cost
                        from network_of_point'','|| node[i] ||','|| node[i+1] ||', true, true)'
           LOOP
            vertex := _r.vertex_id;
            EXECUTE 'insert into temp
                select nextval(''road_intersection_id_seq''), point
                from distinct_network_point
                where distinct_network_point.id ='|| vertex;

           END LOOP;
        i = i + 1;
        END IF;
    END LOOP;

    RETURN 'OK';
END;
$BODY$ language plpgsql;

Upvotes: 0

Davide Berra
Davide Berra

Reputation: 6568

It doesn't work because this line 

IF( i < (array_length(node, 1) - 1))

You're testing if 2 or 7 (taken from your example) are less than the length of the array, that's valued 2.

That's because your next queries are never executed.

Upvotes: 1

Related Questions