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

2016-08-10 15:42 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 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
>
>
the result of PLpgSQL function is set by RETURN statement - if the result
is table, then RETURN QUERY statement should be used.

Regards

Pavel

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