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

Thank you -

On Wed, Aug 10, 2016 at 3:18 PM, Charles Clavadetscher <
clavadetscher(at)swisspug(dot)org> wrote:

>
> #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
>
>
now I have changed my last statement to:

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

And get the next error:

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function words_check_words(integer,integer,jsonb) line
131 at SQL statement

However I do not want to discard my results, but return them by my custom
function...

Regards
Alex

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