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

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

From

Alexander Farber

Date:

10 August 2016, 15:54:34

Good afternoon,

In PostgreSQL 9.5.3 I have created a function (full source code at the bottom), which goes through an 15 x 15 varchar array and collects words played horizontally and vertically.

I have declared the function as:

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$
................
        CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP;
...............
        SELECT word, max(score) as score FROM _words GROUP BY word;
END
$func$ LANGUAGE plpgsql;

And when I call it as:

select * from words_check_words(2, 1, '[{"col":11,"letter":"A","row":8,"value":1},{"col":11,"letter":"B","row":7,"value":3}]'::jsonb);

then it fails with:

ERROR: column reference "word" is ambiguous
LINE 1: SELECT word, max(score) as score FROM _words GROUP BY word
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.

As I understand the "word" is used both by the RETURN TYPE and my TEMP TABLE.

How to resolve this "naming conflict" best or maybe there is some better way like using some "internal" table implicitly created by the type declaration?

Thank you

Alex

P.S. Below is my full source code and the full log output -

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$
DECLARE
        _tile    jsonb;
        _letter varchar;
        _letter2 varchar;
        _value   integer;
        _value2 integer;
        _col     integer;
        _col2    integer;
        _row     integer;
        _row2    integer;
        _letters varchar[][];
        _values integer[][];
        _mult    varchar[][];
        _factor integer;
        _score   integer;
        _word    varchar;
BEGIN
        SELECT
                g.letters,
                g.values,
                b.mult
        INTO
                _letters,
                _values,
                _mult
        FROM words_games g, words_boards b WHERE
                g.gid     = in_gid AND
                g.bid     = b.bid AND
                g.player1 = in_uid AND
                -- and it is first player's turn
        (g.played1 IS NULL OR g.played1 < g.played2);

        IF NOT FOUND THEN
                SELECT
                        g.letters,
                        g.values,
                        b.mult
                INTO
                        _letters,
                        _values,
                        _mult
                FROM words_games g, words_boards b WHERE
                        g.gid     = in_gid AND
                        g.bid     = b.bid AND
                        g.player2 = in_uid AND
                        -- and it is first player's turn
                    (g.played2 IS NULL OR g.played2 < g.played1);
        END IF;

        IF NOT FOUND THEN
                RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
        END IF;

        CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP;

        FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
        LOOP
                _letter := _tile->>'letter';
                _value := (_tile->>'value')::int;
                _col    := (_tile->>'col')::int + 1;
                _row    := (_tile->>'row')::int + 1;

                _letters[_col][_row] := _letter;
                -- multiply the new letter value with premium
                _values[_col][_row] := _value * words_letter_mult(_mult[_col][_row]);

                _word   := _letter;
                _score := _values[_col][_row];
                _factor := words_word_mult(_mult[_col][_row]);

                -- go left and prepend letters
                FOR _col2 IN REVERSE (_col - 1)..1 LOOP
                        _letter2 := _letters[_col2][_row];
                        EXIT WHEN _letter2 IS NULL;
                        _value2 := _values[_col2][_row];
                        _word    := _letter2 || _word;
                        _score   := _score + _value2;
                        _factor := _factor * words_word_mult(_mult[_col2][_row]);
                END LOOP;

                -- go right and append letters
                FOR _col2 IN (_col + 1)..15 LOOP
                        _letter2 := _letters[_col2][_row];
                        EXIT WHEN _letter2 IS NULL;
                        _value2 := _values[_col2][_row];
                        _word    := _word || _letter2;
                        _score   := _score + _value2;
                        _factor := _factor * words_word_mult(_mult[_col2][_row]);
                END LOOP;

                IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM words_nouns */ THEN
                        INSERT INTO _words(word, score)
                        VALUES (upper(_word), _score);
                END IF;

                _word   := _letter;
                _score := _values[_col][_row];
                _factor := words_word_mult(_mult[_col][_row]);

                -- go up and prepend letters
                FOR _row2 IN REVERSE (_row - 1)..1 LOOP
                        _letter2 := _letters[_col][_row2];
                        EXIT WHEN _letter2 IS NULL;
                        _value2 := _values[_col][_row2];
                        _word    := _letter2 || _word;
                        _score   := _score + _value2;
                        _factor := _factor * words_word_mult(_mult[_col][_row2]);
                END LOOP;

                -- go down and append letters
                FOR _row2 IN (_row + 1)..15 LOOP
                        _letter2 := _letters[_col][_row2];
                        EXIT WHEN _letter2 IS NULL;
                        _value2 := _values[_col][_row2];
                        _word    := _word || _letter2;
                        _score   := _score + _value2;
                        _factor := _factor * words_word_mult(_mult[_col][_row2]);
                END 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;

        RAISE NOTICE 'letters = %', _letters;
        RAISE NOTICE 'values   = %', _values;
        RAISE NOTICE 'mult     = %', _mult;

        SELECT word, max(score) as score FROM _words GROUP BY word;
END
$func$ LANGUAGE plpgsql;

-----------------

-- apologies for non-english letters here

LOG: statement: select * from words_check_words(2, 1, '[{"col":11,"letter":"?","row":8,"value":1},{"col":11,"letter":"?","row":7,"value":3}]'::jsonb);
NOTICE: letters = {{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,М,NULL,NULL
,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,Э,Р,О,Е,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Х,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,И,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,В,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,?,?,Ы,NULL,NULL,NULL
,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
NOTICE: values = {{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL
,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,2,1,1,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NU
LL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,1,5,NULL,NULL,NUL
L,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NU
LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
NOTICE: mult = {{TW,NULL,NULL,DL,NULL,NULL,NULL,TW,NULL,NULL,NULL,DL,NULL,NULL,TW},{NULL,TL,NULL,NULL,NULL,DW,NULL,NULL,NULL,DW,NULL,NULL,NULL,TL,NULL},{NULL,NULL,TL,NULL,NULL
,NULL,DL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL},{DL,NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL,DL},{NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,
NULL},{NULL,DW,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,DW,NULL},{NULL,NULL,DL,NULL,NULL,NULL,DL,NULL,DL,NULL,NULL,NULL,DL,NULL,NULL},{TW,NULL,NULL,DL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,DL,NULL,NULL,TW},{NULL,NULL,DL,NULL,NULL,NULL,DL,NULL,DL,NULL,NULL,NULL,DL,NULL,NULL},{NULL,DW,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,DW,NULL},{
NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL},{DL,NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL,DL},{NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,DL,N
ULL,NULL,NULL,TL,NULL,NULL},{NULL,TL,NULL,NULL,NULL,DW,NULL,NULL,NULL,DW,NULL,NULL,NULL,TL,NULL},{TW,NULL,NULL,DL,NULL,NULL,NULL,TW,NULL,NULL,NULL,DL,NULL,NULL,TW}}
ERROR: column reference "word" is ambiguous at character 8
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: SELECT word, max(score) as score FROM _words GROUP BY word
CONTEXT: PL/pgSQL function words_check_words(integer,integer,jsonb) line 131 at SQL statement
STATEMENT: select * from words_check_words(2, 1, '[{"col":11,"letter":"?","row":8,"value":1},{"col":11,"letter":"?","row":7,"value":3}]'::jsonb);

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

From

"Charles Clavadetscher"

Date:

10 August 2016, 16:18:55

Hello

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
> Sent: Mittwoch, 10. August 2016 14:54
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous
>
> Good afternoon,
>
>
> In PostgreSQL 9.5.3 I have created a function (full source code at the bottom), which goes through an 15 x 15
> varchar array and collects words played horizontally and vertically.
>
>
> I have declared the function as:
>
>
> 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$ ................
>         CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP; ...............
>         SELECT word, max(score) as score FROM _words GROUP BY word; END $func$ LANGUAGE plpgsql;
>
>
> And when I call it as:
>
>
> select * from words_check_words(2, 1,
> '[{"col":11,"letter":"A","row":8,"value":1},{"col":11,"letter":"B","row":7,"value":3}]'::jsonb);
>
>
> then it fails with:
>
>
> ERROR:  column reference "word" is ambiguous LINE 1: SELECT word, max(score) as score FROM _words GROUP BY word
>                ^
> DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
>
>
> As I understand the "word" is used both by the RETURN TYPE and my TEMP TABLE.
>
>
> How to resolve this "naming conflict" best or maybe there is some better way like using some "internal" table
> implicitly created by the type declaration?
>
>
> Thank you
>
> Alex
>
>
> P.S. Below is my full source code and the full log output -
>
>

You can use

#variable_conflict [use_column|use_variable] before BEGIN:

-
http://dba.stackexchange.com/questions/105831/naming-conflict-between-function-parameter-and-result-of-join-with-using-clause
- https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

Hope this helps.
Regards
Charles

> 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$ DECLARE
>         _tile    jsonb;
>         _letter  varchar;
>         _letter2 varchar;
>         _value   integer;
>         _value2  integer;
>         _col     integer;
>         _col2    integer;
>         _row     integer;
>         _row2    integer;
>         _letters varchar[][];
>         _values  integer[][];
>         _mult    varchar[][];
>         _factor  integer;
>         _score   integer;
>         _word    varchar;
> BEGIN
>         SELECT
>                 g.letters,
>                 g.values,
>                 b.mult
>         INTO
>                 _letters,
>                 _values,
>                 _mult
>         FROM words_games g, words_boards b WHERE
>                 g.gid     = in_gid AND
>                 g.bid     = b.bid  AND
>                 g.player1 = in_uid AND
>                 -- and it is first player's turn
>         (g.played1 IS NULL OR g.played1 < g.played2);
>
>         IF NOT FOUND THEN
>                 SELECT
>                         g.letters,
>                         g.values,
>                         b.mult
>                 INTO
>                         _letters,
>                         _values,
>                         _mult
>                 FROM words_games g, words_boards b WHERE
>                         g.gid     = in_gid AND
>                         g.bid     = b.bid  AND
>                         g.player2 = in_uid AND
>                         -- and it is first player's turn
>                     (g.played2 IS NULL OR g.played2 < g.played1);
>         END IF;
>
>         IF NOT FOUND THEN
>                 RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
>         END IF;
>
>         CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP;
>
>         FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
>         LOOP
>                 _letter :=  _tile->>'letter';
>                 _value  := (_tile->>'value')::int;
>                 _col    := (_tile->>'col')::int + 1;
>                 _row    := (_tile->>'row')::int + 1;
>
>                 _letters[_col][_row] := _letter;
>                 -- multiply the new letter value with premium
>                 _values[_col][_row]  := _value * words_letter_mult(_mult[_col][_row]);
>
>                 _word   := _letter;
>                 _score  := _values[_col][_row];
>                 _factor := words_word_mult(_mult[_col][_row]);
>
>                 -- go left and prepend letters
>                 FOR _col2 IN REVERSE (_col - 1)..1 LOOP
>                         _letter2 := _letters[_col2][_row];
>                         EXIT WHEN _letter2 IS NULL;
>                         _value2  := _values[_col2][_row];
>                         _word    := _letter2 || _word;
>                         _score   := _score + _value2;
>                         _factor  := _factor * words_word_mult(_mult[_col2][_row]);
>                 END LOOP;
>
>                 -- go right and append letters
>                 FOR _col2 IN (_col + 1)..15 LOOP
>                         _letter2 := _letters[_col2][_row];
>                         EXIT WHEN _letter2 IS NULL;
>                         _value2  := _values[_col2][_row];
>                         _word    := _word || _letter2;
>                         _score   := _score + _value2;
>                         _factor  := _factor * words_word_mult(_mult[_col2][_row]);
>                 END LOOP;
>
>                 IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM words_nouns */ THEN
>                         INSERT INTO _words(word, score)
>                         VALUES (upper(_word), _score);
>                 END IF;
>
>                 _word   := _letter;
>                 _score  := _values[_col][_row];
>                 _factor := words_word_mult(_mult[_col][_row]);
>
>                 -- go up and prepend letters
>                 FOR _row2 IN REVERSE (_row - 1)..1 LOOP
>                         _letter2 := _letters[_col][_row2];
>                         EXIT WHEN _letter2 IS NULL;
>                         _value2  := _values[_col][_row2];
>                         _word    := _letter2 || _word;
>                         _score   := _score + _value2;
>                         _factor  := _factor * words_word_mult(_mult[_col][_row2]);
>                 END LOOP;
>
>                 -- go down and append letters
>                 FOR _row2 IN (_row + 1)..15 LOOP
>                         _letter2 := _letters[_col][_row2];
>                         EXIT WHEN _letter2 IS NULL;
>                         _value2  := _values[_col][_row2];
>                         _word    := _word || _letter2;
>                         _score   := _score + _value2;
>                         _factor  := _factor * words_word_mult(_mult[_col][_row2]);
>                 END 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;
>
>         RAISE NOTICE 'letters  = %', _letters;
>         RAISE NOTICE 'values   = %', _values;
>         RAISE NOTICE 'mult     = %', _mult;
>
>         SELECT word, max(score) as score FROM _words GROUP BY word; END $func$ LANGUAGE plpgsql;
>
>
> -----------------
>
>
> -- apologies for non-english letters here
>
>
> LOG:  statement: select * from words_check_words(2, 1,
> '[{"col":11,"letter":"?","row":8,"value":1},{"col":11,"letter":"?","row":7,"value":3}]'::jsonb);
> NOTICE:  letters  =
> {{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU
> LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL
> ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
> L,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
> ,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,М,NULL,NULL
> ,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,Э,Р,О,Е,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NUL
> L,NULL,NULL,Х,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NUL
> L,NULL,NULL,NULL,NULL,И,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,В,NULL,NULL,NULL,NUL
> L,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,?,?,Ы,NULL,NULL,NULL
> ,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,N
> ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NUL
> L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
> NOTICE:  values   =
> {{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU
> LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL
> ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
> L,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
> ,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL
> ,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,2,1,1,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NU
> LL,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NU
> LL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NU
> LL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,1,5,NULL,NULL,NUL
> L,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NU
> LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
> NOTICE:  mult     =
> {{TW,NULL,NULL,DL,NULL,NULL,NULL,TW,NULL,NULL,NULL,DL,NULL,NULL,TW},{NULL,TL,NULL,NULL,NULL,DW,NULL,NULL,NULL,DW,NUL
> L,NULL,NULL,TL,NULL},{NULL,NULL,TL,NULL,NULL
> ,NULL,DL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL},{DL,NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL,DL},{NU
> LL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,
> NULL},{NULL,DW,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,DW,NULL},{NULL,NULL,DL,NULL,NULL,NULL,DL,NULL,
> DL,NULL,NULL,NULL,DL,NULL,NULL},{TW,NULL,NULL,DL,NULL,NULL,NULL,
> NULL,NULL,NULL,NULL,DL,NULL,NULL,TW},{NULL,NULL,DL,NULL,NULL,NULL,DL,NULL,DL,NULL,NULL,NULL,DL,NULL,NULL},{NULL,DW,N
> ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,DW,NULL},{
> NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL},{DL,NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,NULL
> ,NULL,TL,NULL,NULL,DL},{NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,DL,N
> ULL,NULL,NULL,TL,NULL,NULL},{NULL,TL,NULL,NULL,NULL,DW,NULL,NULL,NULL,DW,NULL,NULL,NULL,TL,NULL},{TW,NULL,NULL,DL,NU
> LL,NULL,NULL,TW,NULL,NULL,NULL,DL,NULL,NULL,TW}}
> ERROR:  column reference "word" is ambiguous at character 8
> DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
> QUERY:  SELECT word, max(score) as score FROM _words GROUP BY word
> CONTEXT:  PL/pgSQL function words_check_words(integer,integer,jsonb) line 131 at SQL statement
> STATEMENT:  select * from words_check_words(2, 1,
> '[{"col":11,"letter":"?","row":8,"value":1},{"col":11,"letter":"?","row":7,"value":3}]'::jsonb);
>
>

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

From

Adrian Klaver

Date:

10 August 2016, 16:37:48

On 08/10/2016 05:54 AM, Alexander Farber wrote:
> Good afternoon,
>
> In PostgreSQL 9.5.3 I have created a function (full source code at the
> bottom), which goes through an 15 x 15 varchar array and collects words
> played horizontally and vertically.
>
> I have declared the function as:
>
> 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$
> ................
>         CREATE TEMPORARY TABLE _words (word varchar, score integer) ON
> COMMIT DROP;
> ...............
>         SELECT word, max(score) as score FROM _words GROUP BY word;
> END
> $func$ LANGUAGE plpgsql;
>
> And when I call it as:
>
> select * from words_check_words(2, 1,
> '[{"col":11,"letter":"A","row":8,"value":1},{"col":11,"letter":"B","row":7,"value":3}]'::jsonb);
>
> then it fails with:
>
> ERROR:  column reference "word" is ambiguous
> LINE 1: SELECT word, max(score) as score FROM _words GROUP BY word
>                ^
> DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
>
> As I understand the "word" is used both by the RETURN TYPE and my TEMP
> TABLE.
>
> How to resolve this "naming conflict" best or maybe there is some better
> way like using some "internal" table implicitly created by the type
> declaration?

SELECT _words.word, max(score) as score FROM _words GROUP BY word;

>
> Thank you
> Alex
>



--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Alexander Farber

Date:

10 August 2016, 16:39:33

Thank you -

On Wed, Aug 10, 2016 at 3:18 PM, Charles Clavadetscher <clavadetscher@swisspug.org> wrote:

#variable_conflict [use_column|use_variable] before BEGIN:

- http://dba.stackexchange.com/questions/105831/naming-conflict-between-function-parameter-and-result-of-join-with-using-clause
- https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

now I have changed my last statement to:

        SELECT w.word, max(w.score) as score
        FROM _words w
        GROUP BY w.word;

And get the next error:

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function words_check_words(integer,integer,jsonb) line 131 at SQL statement

However I do not want to discard my results, but return them by my custom function...

Regards

Alex

> 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$ DECLARE
> _tile jsonb;
> _letter varchar;
> _letter2 varchar;
> _value integer;
> _value2 integer;
> _col integer;
> _col2 integer;
> _row integer;
> _row2 integer;
> _letters varchar[][];
> _values integer[][];
> _mult varchar[][];
> _factor integer;
> _score integer;
> _word varchar;
> BEGIN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid AND
> g.player1 = in_uid AND
> -- and it is first player's turn
> (g.played1 IS NULL OR g.played1 < g.played2);
>
> IF NOT FOUND THEN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid AND
> g.player2 = in_uid AND
> -- and it is first player's turn
> (g.played2 IS NULL OR g.played2 < g.played1);
> END IF;
>
> IF NOT FOUND THEN
> RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
> END IF;
>
> CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP;
>
> FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
> LOOP
> _letter := _tile->>'letter';
> _value := (_tile->>'value')::int;
> _col := (_tile->>'col')::int + 1;
> _row := (_tile->>'row')::int + 1;
>
> _letters[_col][_row] := _letter;
> -- multiply the new letter value with premium
> _values[_col][_row] := _value * words_letter_mult(_mult[_col][_row]);
>
> _word := _letter;
> _score := _values[_col][_row];
> _factor := words_word_mult(_mult[_col][_row]);
>
> -- go left and prepend letters
> FOR _col2 IN REVERSE (_col - 1)..1 LOOP
> _letter2 := _letters[_col2][_row];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col2][_row];
> _word := _letter2 || _word;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col2][_row]);
> END LOOP;
>
> -- go right and append letters
> FOR _col2 IN (_col + 1)..15 LOOP
> _letter2 := _letters[_col2][_row];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col2][_row];
> _word := _word || _letter2;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col2][_row]);
> END LOOP;
>
> IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM words_nouns */ THEN
> INSERT INTO _words(word, score)
> VALUES (upper(_word), _score);
> END IF;
>
> _word := _letter;
> _score := _values[_col][_row];
> _factor := words_word_mult(_mult[_col][_row]);
>
> -- go up and prepend letters
> FOR _row2 IN REVERSE (_row - 1)..1 LOOP
> _letter2 := _letters[_col][_row2];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col][_row2];
> _word := _letter2 || _word;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col][_row2]);
> END LOOP;
>
> -- go down and append letters
> FOR _row2 IN (_row + 1)..15 LOOP
> _letter2 := _letters[_col][_row2];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col][_row2];
> _word := _word || _letter2;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col][_row2]);
> END 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;
>
> RAISE NOTICE 'letters = %', _letters;
> RAISE NOTICE 'values = %', _values;
> RAISE NOTICE 'mult = %', _mult;
>
> SELECT word, max(score) as score FROM _words GROUP BY word; END $func$ LANGUAGE plpgsql;
>

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

From

Pavel Stehule

Date:

10 August 2016, 16:40:00

2016-08-10 15:18 GMT+02:00 Charles Clavadetscher <clavadetscher@swisspug.org>:

Hello

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
> Sent: Mittwoch, 10. August 2016 14:54
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous
>
> Good afternoon,
>
>
> In PostgreSQL 9.5.3 I have created a function (full source code at the bottom), which goes through an 15 x 15
> varchar array and collects words played horizontally and vertically.
>
>
> I have declared the function as:
>
>
> 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$ ................
> CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP; ...............
> SELECT word, max(score) as score FROM _words GROUP BY word; END $func$ LANGUAGE plpgsql;
>
>
> And when I call it as:
>
>
> select * from words_check_words(2, 1,
> '[{"col":11,"letter":"A","row":8,"value":1},{"col":11,"letter":"B","row":7,"value":3}]'::jsonb);
>
>
> then it fails with:
>
>
> ERROR: column reference "word" is ambiguous LINE 1: SELECT word, max(score) as score FROM _words GROUP BY word
> ^
> DETAIL: It could refer to either a PL/pgSQL variable or a table column.
>
>
> As I understand the "word" is used both by the RETURN TYPE and my TEMP TABLE.
>
>
> How to resolve this "naming conflict" best or maybe there is some better way like using some "internal" table
> implicitly created by the type declaration?
>
>
> Thank you
>
> Alex
>
>
> P.S. Below is my full source code and the full log output -
>
>

You can use

#variable_conflict [use_column|use_variable] before BEGIN:

- http://dba.stackexchange.com/questions/105831/naming-conflict-between-function-parameter-and-result-of-join-with-using-clause
- https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

I am sorry, but disabling this check is not good.This is pretty big trap.

correct and usual solution is using qualified names

_words.word

Regards

Pavel

Hope this helps.
Regards
Charles

> 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$ DECLARE
> _tile jsonb;
> _letter varchar;
> _letter2 varchar;
> _value integer;
> _value2 integer;
> _col integer;
> _col2 integer;
> _row integer;
> _row2 integer;
> _letters varchar[][];
> _values integer[][];
> _mult varchar[][];
> _factor integer;
> _score integer;
> _word varchar;
> BEGIN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid AND
> g.player1 = in_uid AND
> -- and it is first player's turn
> (g.played1 IS NULL OR g.played1 < g.played2);
>
> IF NOT FOUND THEN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid AND
> g.player2 = in_uid AND
> -- and it is first player's turn
> (g.played2 IS NULL OR g.played2 < g.played1);
> END IF;
>
> IF NOT FOUND THEN
> RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
> END IF;
>
> CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP;
>
> FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
> LOOP
> _letter := _tile->>'letter';
> _value := (_tile->>'value')::int;
> _col := (_tile->>'col')::int + 1;
> _row := (_tile->>'row')::int + 1;
>
> _letters[_col][_row] := _letter;
> -- multiply the new letter value with premium
> _values[_col][_row] := _value * words_letter_mult(_mult[_col][_row]);
>
> _word := _letter;
> _score := _values[_col][_row];
> _factor := words_word_mult(_mult[_col][_row]);
>
> -- go left and prepend letters
> FOR _col2 IN REVERSE (_col - 1)..1 LOOP
> _letter2 := _letters[_col2][_row];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col2][_row];
> _word := _letter2 || _word;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col2][_row]);
> END LOOP;
>
> -- go right and append letters
> FOR _col2 IN (_col + 1)..15 LOOP
> _letter2 := _letters[_col2][_row];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col2][_row];
> _word := _word || _letter2;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col2][_row]);
> END LOOP;
>
> IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM words_nouns */ THEN
> INSERT INTO _words(word, score)
> VALUES (upper(_word), _score);
> END IF;
>
> _word := _letter;
> _score := _values[_col][_row];
> _factor := words_word_mult(_mult[_col][_row]);
>
> -- go up and prepend letters
> FOR _row2 IN REVERSE (_row - 1)..1 LOOP
> _letter2 := _letters[_col][_row2];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col][_row2];
> _word := _letter2 || _word;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col][_row2]);
> END LOOP;
>
> -- go down and append letters
> FOR _row2 IN (_row + 1)..15 LOOP
> _letter2 := _letters[_col][_row2];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col][_row2];
> _word := _word || _letter2;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col][_row2]);
> END 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;
>
> RAISE NOTICE 'letters = %', _letters;
> RAISE NOTICE 'values = %', _values;
> RAISE NOTICE 'mult = %', _mult;
>
> SELECT word, max(score) as score FROM _words GROUP BY word; END $func$ LANGUAGE plpgsql;
>
>
> -----------------
>
>
> -- apologies for non-english letters here
>
>
> LOG: statement: select * from words_check_words(2, 1,
> '[{"col":11,"letter":"?","row":8,"value":1},{"col":11,"letter":"?","row":7,"value":3}]'::jsonb);
> NOTICE: letters =
> {{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU
> LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL
> ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
> L,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
> ,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,М,NULL,NULL
> ,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,Э,Р,О,Е,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NUL
> L,NULL,NULL,Х,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NUL
> L,NULL,NULL,NULL,NULL,И,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,В,NULL,NULL,NULL,NUL
> L,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,?,?,Ы,NULL,NULL,NULL
> ,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,N
> ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NUL
> L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
> NOTICE: values =
> {{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU
> LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL
> ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
> L,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
> ,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL
> ,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,2,1,1,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NU
> LL,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NU
> LL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NU
> LL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,6,1,5,NULL,NULL,NUL
> L,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,
> NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NU
> LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
> NOTICE: mult =
> {{TW,NULL,NULL,DL,NULL,NULL,NULL,TW,NULL,NULL,NULL,DL,NULL,NULL,TW},{NULL,TL,NULL,NULL,NULL,DW,NULL,NULL,NULL,DW,NUL
> L,NULL,NULL,TL,NULL},{NULL,NULL,TL,NULL,NULL
> ,NULL,DL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL},{DL,NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,NULL,NULL,TL,NULL,NULL,DL},{NU
> LL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,
> NULL},{NULL,DW,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,DW,NULL},{NULL,NULL,DL,NULL,NULL,NULL,DL,NULL,
> DL,NULL,NULL,NULL,DL,NULL,NULL},{TW,NULL,NULL,DL,NULL,NULL,NULL,
> NULL,NULL,NULL,NULL,DL,NULL,NULL,TW},{NULL,NULL,DL,NULL,NULL,NULL,DL,NULL,DL,NULL,NULL,NULL,DL,NULL,NULL},{NULL,DW,N
> ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,DW,NULL},{
> NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL,NULL,TL,NULL,NULL,NULL,NULL},{DL,NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,NULL
> ,NULL,TL,NULL,NULL,DL},{NULL,NULL,TL,NULL,NULL,NULL,DL,NULL,DL,N
> ULL,NULL,NULL,TL,NULL,NULL},{NULL,TL,NULL,NULL,NULL,DW,NULL,NULL,NULL,DW,NULL,NULL,NULL,TL,NULL},{TW,NULL,NULL,DL,NU
> LL,NULL,NULL,TW,NULL,NULL,NULL,DL,NULL,NULL,TW}}
> ERROR: column reference "word" is ambiguous at character 8
> DETAIL: It could refer to either a PL/pgSQL variable or a table column.
> QUERY: SELECT word, max(score) as score FROM _words GROUP BY word
> CONTEXT: PL/pgSQL function words_check_words(integer,integer,jsonb) line 131 at SQL statement
> STATEMENT: select * from words_check_words(2, 1,
> '[{"col":11,"letter":"?","row":8,"value":1},{"col":11,"letter":"?","row":7,"value":3}]'::jsonb);
>
>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

From

Pavel Stehule

Date:

10 August 2016, 16:43:16

2016-08-10 15:39 GMT+02:00 Alexander Farber <alexander.farber@gmail.com>:

Thank you -

On Wed, Aug 10, 2016 at 3:18 PM, Charles Clavadetscher <clavadetscher@swisspug.org> wrote:

#variable_conflict [use_column|use_variable] before BEGIN:

- http://dba.stackexchange.com/questions/105831/naming-conflict-between-function-parameter-and-result-of-join-with-using-clause
- https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

now I have changed my last statement to:

        SELECT w.word, max(w.score) as score
        FROM _words w
        GROUP BY w.word;

And get the next error:

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function words_check_words(integer,integer,jsonb) line 131 at SQL statement

However I do not want to discard my results, but return them by my custom function...

you should to use INTO clause probably -

https://www.postgresql.org/docs/current/static/plpgsql-statements.html

Regards

Pavel

Regards
Alex

> 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$ DECLARE
> _tile jsonb;
> _letter varchar;
> _letter2 varchar;
> _value integer;
> _value2 integer;
> _col integer;
> _col2 integer;
> _row integer;
> _row2 integer;
> _letters varchar[][];
> _values integer[][];
> _mult varchar[][];
> _factor integer;
> _score integer;
> _word varchar;
> BEGIN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid AND
> g.player1 = in_uid AND
> -- and it is first player's turn
> (g.played1 IS NULL OR g.played1 < g.played2);
>
> IF NOT FOUND THEN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid AND
> g.player2 = in_uid AND
> -- and it is first player's turn
> (g.played2 IS NULL OR g.played2 < g.played1);
> END IF;
>
> IF NOT FOUND THEN
> RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
> END IF;
>
> CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP;
>
> FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
> LOOP
> _letter := _tile->>'letter';
> _value := (_tile->>'value')::int;
> _col := (_tile->>'col')::int + 1;
> _row := (_tile->>'row')::int + 1;
>
> _letters[_col][_row] := _letter;
> -- multiply the new letter value with premium
> _values[_col][_row] := _value * words_letter_mult(_mult[_col][_row]);
>
> _word := _letter;
> _score := _values[_col][_row];
> _factor := words_word_mult(_mult[_col][_row]);
>
> -- go left and prepend letters
> FOR _col2 IN REVERSE (_col - 1)..1 LOOP
> _letter2 := _letters[_col2][_row];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col2][_row];
> _word := _letter2 || _word;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col2][_row]);
> END LOOP;
>
> -- go right and append letters
> FOR _col2 IN (_col + 1)..15 LOOP
> _letter2 := _letters[_col2][_row];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col2][_row];
> _word := _word || _letter2;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col2][_row]);
> END LOOP;
>
> IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM words_nouns */ THEN
> INSERT INTO _words(word, score)
> VALUES (upper(_word), _score);
> END IF;
>
> _word := _letter;
> _score := _values[_col][_row];
> _factor := words_word_mult(_mult[_col][_row]);
>
> -- go up and prepend letters
> FOR _row2 IN REVERSE (_row - 1)..1 LOOP
> _letter2 := _letters[_col][_row2];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col][_row2];
> _word := _letter2 || _word;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col][_row2]);
> END LOOP;
>
> -- go down and append letters
> FOR _row2 IN (_row + 1)..15 LOOP
> _letter2 := _letters[_col][_row2];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col][_row2];
> _word := _word || _letter2;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col][_row2]);
> END 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;
>
> RAISE NOTICE 'letters = %', _letters;
> RAISE NOTICE 'values = %', _values;
> RAISE NOTICE 'mult = %', _mult;
>
> SELECT word, max(score) as score FROM _words GROUP BY word; END $func$ LANGUAGE plpgsql;
>

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

From

"David G. Johnston"

Date:

10 August 2016, 16:44:12

On Wed, Aug 10, 2016 at 9:39 AM, Alexander Farber <alexander.farber@gmail.com> wrote:

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function words_check_words(integer,integer,jsonb) line 131 at SQL statement

However I do not want to discard my results, but return them by my custom function...

https://www.postgresql.org/docs/devel/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

David J.

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

From

Pavel Stehule

Date:

10 August 2016, 16:45:36

2016-08-10 15:42 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:

2016-08-10 15:39 GMT+02:00 Alexander Farber <alexander.farber@gmail.com>:

Thank you -

On Wed, Aug 10, 2016 at 3:18 PM, Charles Clavadetscher <clavadetscher@swisspug.org> wrote:

#variable_conflict [use_column|use_variable] before BEGIN:

- http://dba.stackexchange.com/questions/105831/naming-conflict-between-function-parameter-and-result-of-join-with-using-clause
- https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

now I have changed my last statement to:

        SELECT w.word, max(w.score) as score
        FROM _words w
        GROUP BY w.word;

And get the next error:

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function words_check_words(integer,integer,jsonb) line 131 at SQL statement

However I do not want to discard my results, but return them by my custom function...

you should to use INTO clause probably -

https://www.postgresql.org/docs/current/static/plpgsql-statements.html

Regards

the result of PLpgSQL function is set by RETURN statement - if the result is table, then RETURN QUERY statement should be used.

Regards

Pavel

Pavel

Regards
Alex

> 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$ DECLARE
> _tile jsonb;
> _letter varchar;
> _letter2 varchar;
> _value integer;
> _value2 integer;
> _col integer;
> _col2 integer;
> _row integer;
> _row2 integer;
> _letters varchar[][];
> _values integer[][];
> _mult varchar[][];
> _factor integer;
> _score integer;
> _word varchar;
> BEGIN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid AND
> g.player1 = in_uid AND
> -- and it is first player's turn
> (g.played1 IS NULL OR g.played1 < g.played2);
>
> IF NOT FOUND THEN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid AND
> g.player2 = in_uid AND
> -- and it is first player's turn
> (g.played2 IS NULL OR g.played2 < g.played1);
> END IF;
>
> IF NOT FOUND THEN
> RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
> END IF;
>
> CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT DROP;
>
> FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
> LOOP
> _letter := _tile->>'letter';
> _value := (_tile->>'value')::int;
> _col := (_tile->>'col')::int + 1;
> _row := (_tile->>'row')::int + 1;
>
> _letters[_col][_row] := _letter;
> -- multiply the new letter value with premium
> _values[_col][_row] := _value * words_letter_mult(_mult[_col][_row]);
>
> _word := _letter;
> _score := _values[_col][_row];
> _factor := words_word_mult(_mult[_col][_row]);
>
> -- go left and prepend letters
> FOR _col2 IN REVERSE (_col - 1)..1 LOOP
> _letter2 := _letters[_col2][_row];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col2][_row];
> _word := _letter2 || _word;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col2][_row]);
> END LOOP;
>
> -- go right and append letters
> FOR _col2 IN (_col + 1)..15 LOOP
> _letter2 := _letters[_col2][_row];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col2][_row];
> _word := _word || _letter2;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col2][_row]);
> END LOOP;
>
> IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM words_nouns */ THEN
> INSERT INTO _words(word, score)
> VALUES (upper(_word), _score);
> END IF;
>
> _word := _letter;
> _score := _values[_col][_row];
> _factor := words_word_mult(_mult[_col][_row]);
>
> -- go up and prepend letters
> FOR _row2 IN REVERSE (_row - 1)..1 LOOP
> _letter2 := _letters[_col][_row2];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col][_row2];
> _word := _letter2 || _word;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col][_row2]);
> END LOOP;
>
> -- go down and append letters
> FOR _row2 IN (_row + 1)..15 LOOP
> _letter2 := _letters[_col][_row2];
> EXIT WHEN _letter2 IS NULL;
> _value2 := _values[_col][_row2];
> _word := _word || _letter2;
> _score := _score + _value2;
> _factor := _factor * words_word_mult(_mult[_col][_row2]);
> END 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;
>
> RAISE NOTICE 'letters = %', _letters;
> RAISE NOTICE 'values = %', _values;
> RAISE NOTICE 'mult = %', _mult;
>
> SELECT word, max(score) as score FROM _words GROUP BY word; END $func$ LANGUAGE plpgsql;
>

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

From

Alexander Farber

Date:

10 August 2016, 17:49:50

Thank you for confirming, I supposed I have to use RETURN QUERY and now it works.

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

From

Alexander Farber

Date:

10 August 2016, 18:28:26

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

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

From

Adrian Klaver

Date:

10 August 2016, 18:33:03

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@aklaver.com

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

From

Alexander Farber

Date:

10 August 2016, 20:05:31

Thank you Adrian and others -

I am trying to replace INSERT into temp table in my custom function by RETURN NEXT, but get an error:

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$
.......

-- INSERT INTO _words(word, score)
-- VALUES (upper(_word), _score);

RETURN NEXT (word, score);

ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters
LINE 98: RETURN NEXT (word, score);

Regards

Alex

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

From

Adrian Klaver

Date:

10 August 2016, 20:18:14

On 08/10/2016 10:05 AM, Alexander Farber wrote:
> Thank you Adrian and others -
>
> I am trying to replace INSERT into temp table in my custom function by
> RETURN NEXT, but get an error:
>
>     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$
>     .......
>
>                         -- INSERT INTO _words(word, score)
>                         -- VALUES (upper(_word), _score);
>
>                         RETURN NEXT (word, score);
>
>
> ERROR:  RETURN NEXT cannot have a parameter in function with OUT parameters
> LINE 98:                         RETURN NEXT (word, score);

With RETURN NEXT you have to build the table a row at a time where
RETURN NEXT is in a LOOP.  You also need to assign to the OUT
parameters, in this case the fields in your RETURN TABLE. So something
like, inside LOOP:

word := upper(_word);
score := _score;

RETURN NEXT;

If I am following correctly.

>
> Regards
> Alex


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Pavel Stehule

Date:

10 August 2016, 20:19:56

2016-08-10 19:05 GMT+02:00 Alexander Farber <alexander.farber@gmail.com>:

Thank you Adrian and others -

I am trying to replace INSERT into temp table in my custom function by RETURN NEXT, but get an error:

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$
.......
-- INSERT INTO _words(word, score)
-- VALUES (upper(_word), _score);

RETURN NEXT (word, score);

ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters
LINE 98: RETURN NEXT (word, score);

This was limit in older version

you have to assign values to these variables and call RETURN NEXT without any parameters

CREATE OR REPLACE FUNCTION public.foob(OUT a integer, OUT b integer)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
BEGIN
a := 10; b := 20;
RETURN NEXT;
b := 30;
RETURN NEXT;
END;
$function$

result

┌────┬────┐
│ a │ b │
╞════╪════╡
│ 10 │ 20 │
│ 10 │ 30 │
└────┴────┘
(2 rows)

Regards
Alex

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

From

"David G. Johnston"

Date:

10 August 2016, 20:30:59

On Wed, Aug 10, 2016 at 1:19 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2016-08-10 19:05 GMT+02:00 Alexander Farber <alexander.farber@gmail.com>:
Thank you Adrian and others -

I am trying to replace INSERT into temp table in my custom function by RETURN NEXT, but get an error:

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$
.......
-- INSERT INTO _words(word, score)
-- VALUES (upper(_word), _score);

RETURN NEXT (word, score);

ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters
LINE 98: RETURN NEXT (word, score);

This was limit in older version

He's supposedly using 9.5 which documents "RETURN NEXT expression;"

https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html

Dave

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

From

Adrian Klaver

Date:

10 August 2016, 20:36:04

On 08/10/2016 10:30 AM, David G. Johnston wrote:
> On Wed, Aug 10, 2016 at 1:19 PM, Pavel Stehule <pavel.stehule@gmail.com
> <mailto:pavel.stehule@gmail.com>>wrote:
>
>
>
>     2016-08-10 19:05 GMT+02:00 Alexander Farber
>     <alexander.farber@gmail.com <mailto:alexander.farber@gmail.com>>:
>
>         Thank you Adrian and others -
>
>         I am trying to replace INSERT into temp table in my custom
>         function by RETURN NEXT, but get an error:
>
>             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$
>             .......
>
>                                 -- INSERT INTO _words(word, score)
>                                 -- VALUES (upper(_word), _score);
>
>                                 RETURN NEXT (word, score);
>
>
>         ERROR:  RETURN NEXT cannot have a parameter in function with OUT
>         parameters
>         LINE 98:                         RETURN NEXT (word, score);
>
>
>     This was limit in older version
>
>
> He's supposedly using 9.5 which documents "RETURN NEXT expression;"
>
> https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html

"If you declared the function with output parameters, write just RETURN
NEXT with no expression. On each execution, the current values of the
output parameter variable(s) will be saved for eventual return as a row
of the result. Note that you must declare the function as returning
SETOF record when there are multiple output parameters, or SETOF
sometype when there is just one output parameter of type sometype, in
order to create a set-returning function with output parameters."


>
> Dave
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Adrian Klaver

Date:

10 August 2016, 20:43:07

On 08/10/2016 10:19 AM, Pavel Stehule wrote:
>
>
> 2016-08-10 19:05 GMT+02:00 Alexander Farber <alexander.farber@gmail.com
> <mailto:alexander.farber@gmail.com>>:
>
>     Thank you Adrian and others -
>
>     I am trying to replace INSERT into temp table in my custom function
>     by RETURN NEXT, but get an error:
>
>         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$
>         .......
>
>                             -- INSERT INTO _words(word, score)
>                             -- VALUES (upper(_word), _score);
>
>                             RETURN NEXT (word, score);
>
>
>     ERROR:  RETURN NEXT cannot have a parameter in function with OUT
>     parameters
>     LINE 98:                         RETURN NEXT (word, score);
>
>
> This was limit in older version
>
> you have to assign values to these variables and call RETURN NEXT
> without any parameters
>
> CREATE OR REPLACE FUNCTION public.foob(OUT a integer, OUT b integer)
>  RETURNS SETOF record
>  LANGUAGE plpgsql
> AS $function$
> BEGIN
>   a := 10; b := 20;
>   RETURN NEXT;
>   b := 30;
>   RETURN NEXT;
> END;
> $function$
>
> result
> ┌────┬────┐
> │ a  │ b  │
> ╞════╪════╡
> │ 10 │ 20 │
> │ 10 │ 30 │
> └────┴────┘
> (2 rows)

To build on this:

CREATE OR REPLACE FUNCTION public.foob(a integer,  b integer)
  RETURNS TABLE(c integer, d integer)
  LANGUAGE plpgsql
AS $function$
BEGIN
     FOR i in 1..10 LOOP
         c := a + i;
         d := b + i;
       RETURN NEXT;
     END LOOP;
END;
$function$
;


aklaver@test=> select * from foob(1, 2);
  c  | d
----+----
   2 |  3
   3 |  4
   4 |  5
   5 |  6
   6 |  7
   7 |  8
   8 |  9
   9 | 10
  10 | 11
  11 | 12
(10 rows)

>
>
>
>
>     Regards
>     Alex
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Alexander Farber

Date:

10 August 2016, 21:27:03

Both variants have worked for me, thanks

I am using 9.5.3 on CentOS 7 (my "production server" and Win 7, Mac (my "dev machines") :)

Where does RETURN NEXT EXPRESSION work, on 9.6?

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

From

Adrian Klaver

Date:

10 August 2016, 21:31:20

On 08/10/2016 11:26 AM, Alexander Farber wrote:
> Both variants have worked for me, thanks
>
> I am using 9.5.3 on CentOS 7 (my "production server" and Win 7, Mac (my
> "dev machines") :)
>
> Where does RETURN NEXT EXPRESSION work, on 9.6?

Given what you are doing, RETURN TABLE it will not work there for the
same reason it does not work in 9.5:

https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

"If you declared the function with output parameters, write just RETURN
NEXT with no expression. On each execution, the current values of the
output parameter variable(s) will be saved for eventual return as a row
of the result. Note that you must declare the function as returning
SETOF record when there are multiple output parameters, or SETOF
sometype when there is just one output parameter of type sometype, in
order to create a set-returning function with output parameters."

--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Alexander Farber

Date:

10 August 2016, 23:14:56

No, actually both variants work for me right now at 9.5.3 on Mac -

On Wed, Aug 10, 2016 at 8:31 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

Given what you are doing, RETURN TABLE it will not work there for the same reason it does not work in 9.5:

https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

"If you declared the function with output parameters, write just RETURN NEXT with no expression. On each execution, the current values of the output parameter variable(s) will be saved for eventual return as a row of the result. Note that you must declare the function as returning SETOF record when there are multiple output parameters, or SETOF sometype when there is just one output parameter of type sometype, in order to create a set-returning function with output parameters."

Either:

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb
OUT out_word varchar,
OUT out_score integer
) RETURNS SETOF RECORD AS
$func$

Or:

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb
) RETURNS TABLE (out_word varchar, out_score integer) AS
$func$

And then I assign values to the variables and call RETURN NEXT:

out_word := ... ;

out_score := ... ;

RETURN NEXT;

Regards

Alex

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

From

Adrian Klaver

Date:

10 August 2016, 23:26:19

On 08/10/2016 01:14 PM, Alexander Farber wrote:
> No, actually both variants work for me right now at 9.5.3 on Mac -

I thought the question you where asking was:

"Where does RETURN NEXT EXPRESSION work, on 9.6?"

In the examples below you are not doing that.

Inline comments below.

>
> On Wed, Aug 10, 2016 at 8:31 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>
>     Given what you are doing, RETURN TABLE it will not work there for
>     the same reason it does not work in 9.5:
>
>     https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
>     <https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING>
>
>     "If you declared the function with output parameters, write just
>     RETURN NEXT with no expression. On each execution, the current
>     values of the output parameter variable(s) will be saved for
>     eventual return as a row of the result. Note that you must declare
>     the function as returning SETOF record when there are multiple
>     output parameters, or SETOF sometype when there is just one output
>     parameter of type sometype, in order to create a set-returning
>     function with output parameters."
>
>
> Either:
>
>     CREATE OR REPLACE FUNCTION words_check_words(
>             IN in_uid integer,
>             IN in_gid integer,
>             IN in_tiles jsonb
>             OUT out_word varchar,
>             OUT out_score integer
>             ) RETURNS SETOF RECORD AS
>     $func$
>
>
> Or:
>
>     CREATE OR REPLACE FUNCTION words_check_words(
>             IN in_uid integer,
>             IN in_gid integer,
>             IN in_tiles jsonb
>             ) RETURNS TABLE (out_word varchar, out_score integer) AS
>     $func$
>
>
> And then I assign values to the variables and call RETURN NEXT:
>
>       out_word := ... ;
>       out_score := ... ;
>       RETURN NEXT;

RETURN SETOF and RETURN TABLE are comparable, as you found out:

https://www.postgresql.org/docs/9.5/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS

"PL/pgSQL functions can also be declared with output parameters in place
of an explicit specification of the return type. This does not add any
fundamental capability to the language, but it is often convenient,
especially for returning multiple values. The RETURNS TABLE notation can
also be used in place of RETURNS SETOF."


>
> Regards
> Alex


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Sridhar N Bamandlapally

Date:

11 August 2016, 10:13:06

need to return query with alias

example:

create table emp (id integer, ename text);

insert into emp values(1, 'aaa');

create or replace function f_sample1() returns table (id integer, ename text) as $$

declare

begin

return query select id, ename from emp;

end$$ language plpgsql;

select f_sample1(); ---- this will throw ERROR: column reference "id" is ambiguous LINE 1: select id, ename from emp

create or replace function f_sample1() returns table (id integer, ename text) as $$

declare

begin

return query select a.id, a.ename from emp a;

end$$ language plpgsql;

select f_sample1(); ---- success

thanks

Sridhar

On Thu, Aug 11, 2016 at 1:56 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 08/10/2016 01:14 PM, Alexander Farber wrote:
No, actually both variants work for me right now at 9.5.3 on Mac -

I thought the question you where asking was:

"Where does RETURN NEXT EXPRESSION work, on 9.6?"

In the examples below you are not doing that.

Inline comments below.

On Wed, Aug 10, 2016 at 8:31 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

Given what you are doing, RETURN TABLE it will not work there for
the same reason it does not work in 9.5:

https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
<https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING>

"If you declared the function with output parameters, write just
RETURN NEXT with no expression. On each execution, the current
values of the output parameter variable(s) will be saved for
eventual return as a row of the result. Note that you must declare
the function as returning SETOF record when there are multiple
output parameters, or SETOF sometype when there is just one output
parameter of type sometype, in order to create a set-returning
function with output parameters."

Either:

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb
OUT out_word varchar,
OUT out_score integer
) RETURNS SETOF RECORD AS
$func$

Or:

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb
) RETURNS TABLE (out_word varchar, out_score integer) AS
$func$

And then I assign values to the variables and call RETURN NEXT:

out_word := ... ;
out_score := ... ;
RETURN NEXT;

RETURN SETOF and RETURN TABLE are comparable, as you found out:

https://www.postgresql.org/docs/9.5/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS

"PL/pgSQL functions can also be declared with output parameters in place of an explicit specification of the return type. This does not add any fundamental capability to the language, but it is often convenient, especially for returning multiple values. The RETURNS TABLE notation can also be used in place of RETURNS SETOF."

Regards
Alex

--
Adrian Klaver
adrian.klaver@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