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);