Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous - Mailing list pgsql-general

On 08/10/2016 10:19 AM, Pavel Stehule wrote:
>
>
> 2016-08-10 19:05 GMT+02:00 Alexander Farber <alexander.farber@gmail.com
> <mailto:alexander.farber@gmail.com>>:
>
>     Thank you Adrian and others -
>
>     I am trying to replace INSERT into temp table in my custom function
>     by RETURN NEXT, but get an error:
>
>         CREATE OR REPLACE FUNCTION words_check_words(
>                 IN in_uid integer,
>                 IN in_gid integer,
>                 IN in_tiles jsonb)
>                 RETURNS TABLE(word varchar, score integer) AS
>         $func$
>         .......
>
>                             -- INSERT INTO _words(word, score)
>                             -- VALUES (upper(_word), _score);
>
>                             RETURN NEXT (word, score);
>
>
>     ERROR:  RETURN NEXT cannot have a parameter in function with OUT
>     parameters
>     LINE 98:                         RETURN NEXT (word, score);
>
>
> This was limit in older version
>
> you have to assign values to these variables and call RETURN NEXT
> without any parameters
>
> CREATE OR REPLACE FUNCTION public.foob(OUT a integer, OUT b integer)
>  RETURNS SETOF record
>  LANGUAGE plpgsql
> AS $function$
> BEGIN
>   a := 10; b := 20;
>   RETURN NEXT;
>   b := 30;
>   RETURN NEXT;
> END;
> $function$
>
> result
> ┌────┬────┐
> │ a  │ b  │
> ╞════╪════╡
> │ 10 │ 20 │
> │ 10 │ 30 │
> └────┴────┘
> (2 rows)

To build on this:

CREATE OR REPLACE FUNCTION public.foob(a integer,  b integer)
  RETURNS TABLE(c integer, d integer)
  LANGUAGE plpgsql
AS $function$
BEGIN
     FOR i in 1..10 LOOP
         c := a + i;
         d := b + i;
       RETURN NEXT;
     END LOOP;
END;
$function$
;


aklaver@test=> select * from foob(1, 2);
  c  | d
----+----
   2 |  3
   3 |  4
   4 |  5
   5 |  6
   6 |  7
   7 |  8
   8 |  9
   9 | 10
  10 | 11
  11 | 12
(10 rows)

>
>
>
>
>     Regards
>     Alex
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com