2016-08-10 15:42 GMT+02:00 Pavel Stehule <pavel.stehule@xxxxxxxxx>:
2016-08-10 15:39 GMT+02:00 Alexander Farber <alexander.farber@xxxxxxxxx>:Thank you -On Wed, Aug 10, 2016 at 3:18 PM, Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx> wrote:
#variable_conflict [use_column|use_variable] before BEGIN:
- http://dba.stackexchange.com/questions/105831/naming-conflic t-between-function-parameter-a nd-result-of-join-with-using-c lause
- 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
PavelRegardsAlex> 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;
>
