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

On 08/10/2016 08:28 AM, Alexander Farber wrote:
> There is still 1 open question -
>
> In my custom function:
>
>     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$
>
>
> I iterate through tiles passed as last argument and store words built by
> them at the game board into a temporary table:
>
>             CREATE TEMPORARY TABLE _words (word varchar, score integer)
>     ON COMMIT DROP;
>
>             FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
>             LOOP
>                   .....
>                     IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM
>     words_nouns */ THEN
>                             INSERT INTO _words(word, score)
>                             VALUES (upper(_word), _score);
>                     END IF;
>             END LOOP;
>
>
> And at the end I perform SELECT from the temp table:
>
>             RETURN QUERY
>             SELECT w.word, max(w.score) as score
>             FROM _words w
>             GROUP BY w.word;
>     END
>     $func$ LANGUAGE plpgsql;
>
>
> The question is: if it is possible to get rid of the temp table and
> instead add records to the implicit table being returned?

See RETURN NEXT:

https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

40.6.1.2. RETURN NEXT and RETURN QUERY
>
> Thank you
> Alex


--
Adrian Klaver
adrian.klaver@aklaver.com