2016-08-10 15:42 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 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 > > 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; >>> > >>> >> >
