Thank you - On Wed, Aug 10, 2016 at 3:18 PM, Charles Clavadetscher < clavadetsc...@swisspug.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; > > >
