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

Hello

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alexander Farber
> Sent: Mittwoch, 10. August 2016 14:54
> To: pgsql-general <pgsql-general(at)postgresql(dot)org>
> Subject: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous
>
> 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 -
>
>

You can use

#variable_conflict [use_column|use_variable] before BEGIN:

- http://dba.stackexchange.com/questions/105831/naming-conflict-between-function-parameter-and-result-of-join-with-using-clause
- https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

Hope this helps.
Regards
Charles

> 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,NU
> LL,NULL,NULL,NULL,NULL,NULL,NULL,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,NUL
> L,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,N
> ULL,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,NU
> LL,NULL,NULL,NULL,NULL,NULL,NULL,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,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,NU
> LL,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,NU
> LL,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,NUL
> L,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},{NU
> LL,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,N
> ULL,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,NU
> LL,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);
>
>