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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
