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

Question

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;

Pavel Stehule · Accepted Answer · 2013-12-09 10:46:44Z

5

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;

answered Dec 9, 2013 at 10:46

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

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

2 Comments

Nil Over a year ago

Thank you very much for the answer. I`m quite new with DBMS and also with Postgresql. I had the feeling that i´m on the wrong way with the function up there. Actually my task is to finding the distant point. I have very small database which occured 6 points. And i´m supposed to a function which finds the point pairs whose have the max distance. Which way would you suggest me to follow? I´ve checked out the questions here. There are several suggestions like concex hull, ST_MaxDistance so on..

Pavel Stehule Over a year ago

@jugoslaviaa I cannot to help with PostGIS questions - sorry.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related