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

2016-08-10 19:05 GMT+02:00 Alexander Farber <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)

>
> Regards
> Alex
>