Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous

On 08/10/2016 10:19 AM, Pavel Stehule wrote:
>
>
> 2016-08-10 19:05 GMT+02:00 Alexander Farber <alexander(dot)farber(at)gmail(dot)com
> <mailto:alexander(dot)farber(at)gmail(dot)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(at)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(dot)klaver(at)aklaver(dot)com