From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous |
Date: | 2016-08-10 15:32:54 |
Message-ID: | 4f469d2e-b763-fd39-b9c8-fe1e66637e44@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
40.6.1.2. RETURN NEXT and RETURN QUERY
>
> Thank you
> Alex
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2016-08-10 16:23:11 | Re: Should a DB vacuum use up a lot of space ? |
Previous Message | Alexander Farber | 2016-08-10 15:28:19 | Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous |