On 08/10/2016 08:28 AM, Alexander Farber wrote:
There is still 1 open question -In my custom function: 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$ I iterate through tiles passed as last argument and store words built by them at the game board into a temporary table: CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP; FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles) 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; And at the end I perform SELECT from the temp table: RETURN QUERY SELECT w.word, max(w.score) as score FROM _words w GROUP BY w.word; END $func$ LANGUAGE plpgsql; The question is: if it is possible to get rid of the temp table and instead add records to the implicit table being returned?
See RETURN NEXT: https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING 40.6.1.2. RETURN NEXT and RETURN QUERY
Thank you Alex
-- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
