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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: RETURNS TABLE function: ERROR: column reference "word" is ambiguous
Date: 2016-08-10 12:54:25
Message-ID: CAADeyWjzb-uPL=o4i7yGOFbu6q87zpT6NLaQM_0KfhYSkTdv+w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Charles Clavadetscher 2016-08-10 13:18:32 Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous
Previous Message Simon Riggs 2016-08-10 09:26:40 Re: Logical Decoding Failover