2016-08-10 15:39 GMT+02:00 Alexander Farber <alexander.far...@gmail.com>:
> > 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-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; >> > >> >
