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

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);

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

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

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

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

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

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-conflic
t-between-function-parameter-and-result-of-join-with-using-clause
- https://www.postgresql.org/docs/current/static/plpgsql-imple
mentation.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

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

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-conflic
t-between-function-parameter-and-result-of-join-with-using-clause
- https://www.postgresql.org/docs/current/static/plpgsql-imple
mentation.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

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

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

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

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

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

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

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

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)

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

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

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

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

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

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?

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

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

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