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

2016-08-10 15:39 GMT+02:00 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:

>
> 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-conflic
>> t-between-function-parameter-and-result-of-join-with-using-clause
>> - https://www.postgresql.org/docs/current/static/plpgsql-imple
>> mentation.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...
>

you should to use INTO clause probably -

https://www.postgresql.org/docs/current/static/plpgsql-statements.html

Regards

Pavel

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