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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous
Date: 2016-08-10 15:28:19
Message-ID: CAADeyWjVfBA_6=ns5scV5LOqnSL3EVopz8GUXuxUtAZg8DahfQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-08-10 15:32:54 Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous
Previous Message Alexander Farber 2016-08-10 14:49:44 Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous