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

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Good afternoon,

In PostgreSQL 9.5.3 I have created a function (full source code at the bottom), which goes through an 15 x 15 varchar array and collects words played horizontally and vertically.

I have declared the function as:

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$
................
        CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP;
...............
        SELECT word, max(score) as score FROM _words GROUP BY word;
END
$func$ LANGUAGE plpgsql;

And when I call it as:

select * from words_check_words(2, 1, '[{"col":11,"letter":"A","row":8,"value":1},{"col":11,"letter":"B","row":7,"value":3}]'::jsonb);

then it fails with:

ERROR:  column reference "word" is ambiguous
LINE 1: SELECT word, max(score) as score FROM _words GROUP BY word
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

As I understand the "word" is used both by the RETURN TYPE and my TEMP TABLE.

How to resolve this "naming conflict" best or maybe there is some better way like using some "internal" table implicitly created by the type declaration?

Thank you

Alex

P.S. Below is my full source code and the full log output -

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;

-----------------

-- apologies for non-english letters here

LOG:  statement: select * from words_check_words(2, 1, '[{"col":11,"letter":"?","row":8,"value":1},{"col":11,"letter":"?","row":7,"value":3}]'::jsonb);
NOTICE:  letters  = {{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,М,NULL,NULL
,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,Э,Р,О,Е,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Х,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,И,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,В,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,?,?,Ы,NULL,NULL,NULL
,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
NOTICE:  values   = {{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL
,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,2,1,1,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NU
LL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,1,5,NULL,NULL,NUL
L,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NU
LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
NOTICE:  mult     = {{TW,NULL,NULL,DL,NULL,NULL,NULL,TW,NULL,NULL,NULL,DL,NULL,NULL,TW},{NULL,TL,NULL,NULL,NULL,DW,NULL,NULL,NULL,DW,NULL,NULL,NULL,TL,NULL},{NULL,NULL,TL,NULL,NULL
,NULL,DL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL},{DL,NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL,DL},{NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,
NULL},{NULL,DW,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,DW,NULL},{NULL,NULL,DL,NULL,NULL,NULL,DL,NULL,DL,NULL,NULL,NULL,DL,NULL,NULL},{TW,NULL,NULL,DL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,DL,NULL,NULL,TW},{NULL,NULL,DL,NULL,NULL,NULL,DL,NULL,DL,NULL,NULL,NULL,DL,NULL,NULL},{NULL,DW,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,DW,NULL},{
NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL},{DL,NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL,DL},{NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,DL,N
ULL,NULL,NULL,TL,NULL,NULL},{NULL,TL,NULL,NULL,NULL,DW,NULL,NULL,NULL,DW,NULL,NULL,NULL,TL,NULL},{TW,NULL,NULL,DL,NULL,NULL,NULL,TW,NULL,NULL,NULL,DL,NULL,NULL,TW}}
ERROR:  column reference "word" is ambiguous at character 8
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT word, max(score) as score FROM _words GROUP BY word
CONTEXT:  PL/pgSQL function words_check_words(integer,integer,jsonb) line 131 at SQL statement
STATEMENT:  select * from words_check_words(2, 1, '[{"col":11,"letter":"?","row":8,"value":1},{"col":11,"letter":"?","row":7,"value":3}]'::jsonb);