Hello > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alexander Farber > Sent: Mittwoch, 10. August 2016 14:54 > To: pgsql-general <pgsql-general@xxxxxxxxxxxxxx> > Subject: 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); > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
