Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux