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

On 08/10/2016 05:54 AM, Alexander Farber wrote:
> 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?

SELECT _words.word, max(score) as score FROM _words GROUP BY word;

>
> Thank you
> Alex
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com