Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous

Поиск
Список
Период
Сортировка
От Alexander Farber
Тема Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous
Дата
Msg-id CAADeyWjVfBA_6=ns5scV5LOqnSL3EVopz8GUXuxUtAZg8DahfQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous  (Alexander Farber <alexander.farber@gmail.com>)
Ответы Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
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?

Thank you
Alex

В списке pgsql-general по дате отправления:

Предыдущее
От: Alexander Farber
Дата:
Сообщение: Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous