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

Alex

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$
DECLARE
        _tile    jsonb;
        _letter  varchar;
        _letter2 varchar;
        _value   integer;
        _value2  integer;
        _col     integer;
        _col2    integer;
        _row     integer;
        _row2    integer;
        _letters varchar[][];
        _values  integer[][];
        _mult    varchar[][];
        _factor  integer;
        _score   integer;
        _word    varchar;
BEGIN
        SELECT
                g.letters,
                g.values,
                b.mult
        INTO
                _letters,
                _values,
                _mult
        FROM words_games g, words_boards b WHERE
                g.gid     = in_gid AND
                g.bid     = b.bid  AND
                g.player1 = in_uid AND
                -- and it is first player's turn
        (g.played1 IS NULL OR g.played1 < g.played2);

        IF NOT FOUND THEN
                SELECT
                        g.letters,
                        g.values,
                        b.mult
                INTO
                        _letters,
                        _values,
                        _mult
                FROM words_games g, words_boards b WHERE
                        g.gid     = in_gid AND
                        g.bid     = b.bid  AND
                        g.player2 = in_uid AND
                        -- and it is first player's turn
                    (g.played2 IS NULL OR g.played2 < g.played1);
        END IF;

        IF NOT FOUND THEN
                RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
        END IF;

        CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP;

        FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
        LOOP
                _letter :=  _tile->>'letter';
                _value  := (_tile->>'value')::int;
                _col    := (_tile->>'col')::int + 1;
                _row    := (_tile->>'row')::int + 1;

                _letters[_col][_row] := _letter;
                -- multiply the new letter value with premium
                _values[_col][_row]  := _value * words_letter_mult(_mult[_col][_row]);
 
                _word   := _letter;
                _score  := _values[_col][_row];
                _factor := words_word_mult(_mult[_col][_row]);

                -- go left and prepend letters
                FOR _col2 IN REVERSE (_col - 1)..1 LOOP
                        _letter2 := _letters[_col2][_row];
                        EXIT WHEN _letter2 IS NULL;
                        _value2  := _values[_col2][_row];
                        _word    := _letter2 || _word;
                        _score   := _score + _value2;
                        _factor  := _factor * words_word_mult(_mult[_col2][_row]);
                END LOOP;

                -- go right and append letters
                FOR _col2 IN (_col + 1)..15 LOOP
                        _letter2 := _letters[_col2][_row];
                        EXIT WHEN _letter2 IS NULL;
                        _value2  := _values[_col2][_row];
                        _word    := _word || _letter2;
                        _score   := _score + _value2;
                        _factor  := _factor * words_word_mult(_mult[_col2][_row]);
                END LOOP;

                IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM words_nouns */ THEN
                        INSERT INTO _words(word, score)
                        VALUES (upper(_word), _score);
                END IF;

                _word   := _letter;
                _score  := _values[_col][_row];
                _factor := words_word_mult(_mult[_col][_row]);

                -- go up and prepend letters
                FOR _row2 IN REVERSE (_row - 1)..1 LOOP
                        _letter2 := _letters[_col][_row2];
                        EXIT WHEN _letter2 IS NULL;
                        _value2  := _values[_col][_row2];
                        _word    := _letter2 || _word;
                        _score   := _score + _value2;
                        _factor  := _factor * words_word_mult(_mult[_col][_row2]);
                END LOOP;

                -- go down and append letters
                FOR _row2 IN (_row + 1)..15 LOOP
                        _letter2 := _letters[_col][_row2];
                        EXIT WHEN _letter2 IS NULL;
                        _value2  := _values[_col][_row2];
                        _word    := _word || _letter2;
                        _score   := _score + _value2;
                        _factor  := _factor * words_word_mult(_mult[_col][_row2]);
                END 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;

        RAISE NOTICE 'letters  = %', _letters;
        RAISE NOTICE 'values   = %', _values;
        RAISE NOTICE 'mult     = %', _mult;

        SELECT word, max(score) as score FROM _words GROUP BY word;
END
$func$ LANGUAGE plpgsql;