CODEWITHSUNDEEP
postgresqlplpgsql
Nil
Nil

Reputation: 221

PostgreSQL error: RETURN NEXT cannot have a parameter in function with OUT parameters

I have a plpgsql function in PostgreSQL 9.2 which returns a table. I try to find the distance between the point pairs which are I have in my database. But I am facing the error in the title of this question.

I wanted to do something like this:

CREATE OR REPLACE FUNCTION max_dis(which_tab text)
RETURNS TABLE(P1 geometry, P2 geometry, dis double precision) as

$$
DECLARE
   my_row RECORD;
   my_row2 RECORD;

BEGIN

    FOR my_row IN EXECUTE ('SELECT * FROM '||which_tab) LOOP
         -- 
       FOR my_row2 IN EXECUTE ('SELECT * FROM '||which_tab) LOOP

        SELECT ST_DISTANCE(my_row.the_geom, my_row2.the_geom) 

        INTO dis;

            RETURN NEXT my_row,my_row2; 

        END LOOP;

    END LOOP;

    RETURN;

END ;

$$
LANGUAGE plpgsql VOLATILE STRICT;

Upvotes: 2

Views: 2712

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45835

When you use a table function - with declaration RETURNS TABLE (), then there are a implicit variables for any column of output table. And in this case, RETURN NEXT is used without parameters, because a return value is composed from content of parameter' variables.

CREATE OR REPLACE FUNCTION xx()
RETURNS TABLE(a int, b int) AS $$
BEGIN
   a := 1;
   b := 2;
   RETURN NEXT;
   b := 3;
   RETURN NEXT;
   RETURN;
END;
$$ LANGUAGE plpgsql;

But you can enhance your function via RETURN QUERY statement

BEGIN
  RETURN QUERY EXECUTE format('SELECT t1.the_geom, t2.the_geom,
                                      ST_DISTANCE(t1.the_geom, t2.geom)
                                  FROM %I t1, %I t2', which_tab, which_tab);
  RETURN;
END;

Upvotes: 5

Related Questions