Thread: [GENERAL] ERROR: query returned no rows

Il 26/06/2017 20:21, Alexander Farber ha scritto:
>
>                 RETURNING
>                         player1,
>                         score2,
>                         score1
>                 INTO
>                         _opponent,
>                         _score1,               -- the line 85
>                         _score2;
>
Is it intentional to return score2 in score1 and vice versa?

On 06/26/2017 11:21 AM, Alexander Farber wrote:
> The error message returned by
> the database is rather cryptic:
>
> words=> select * from words_skip_game(1, 1);
> ERROR:  query returned no rows
> CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at
> SQL statement
>
> When I look at my source code
> ( https://gist.github.com/afarber/cac9a83b7a37307ace8d787be9b8ff4c ) at
> the line 85, then I am not sure if the line number reported by the error
> message is correct, because it points into middle of an UPDATE statement:

I agree the line numbers for error messages inside of plpgsql functions
never seem to make sense, so I wouldn't get too hung up on line 85.
Perhaps you should see what is line 85 when you do `\sf words_skip_game`
(rather than line 85 in your own source code). But mostly I would try to
find some other way of locating the cause of the error.

You can read about the "query returned no rows" message here:

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

It seems to me you should only see it for `INTO STRICT`, not plain
`INTO`. I see from your gist that your function *does* have some queries
that are `INTO STRICT`, so I would focus on those.

That page also describes how to use `print_strict_params` to get a
little more info about the details of the error.

I hope that helps!

Paul

On 06/26/2017 12:03 PM, Paul Jungwirth wrote:
> On 06/26/2017 11:21 AM, Alexander Farber wrote:
>> The error message returned by
>> the database is rather cryptic:
>>
>> words=> select * from words_skip_game(1, 1);
>> ERROR:  query returned no rows
>> CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at
>> SQL statement
>>
>> When I look at my source code
>> ( https://gist.github.com/afarber/cac9a83b7a37307ace8d787be9b8ff4c ) at
>> the line 85, then I am not sure if the line number reported by the error
>> message is correct, because it points into middle of an UPDATE statement:
>
> I agree the line numbers for error messages inside of plpgsql functions
> never seem to make sense, so I wouldn't get too hung up on line 85.
> Perhaps you should see what is line 85 when you do `\sf words_skip_game`
> (rather than line 85 in your own source code). But mostly I would try to
> find some other way of locating the cause of the error.

Or easier yet:

https://www.postgresql.org/docs/9.5/static/app-psql.html
"
\ef [ function_description [ line_number ] ]

     This command fetches and edits the definition of the named
function, in the form of a CREATE OR REPLACE FUNCTION command. Editing
is done in the same way as for \edit. After the editor exits, the
updated command waits in the query buffer; type semicolon or \g to send
it, or \r to cancel.

     The target function can be specified by name alone, or by name and
arguments, for example foo(integer, text). The argument types must be
given if there is more than one function of the same name.

     If no function is specified, a blank CREATE FUNCTION template is
presented for editing.

     If a line number is specified, psql will position the cursor on the
specified line of the function body. (Note that the function body
typically does not begin on the first line of the file.)

"

So:

\ef words_skip_game 85

>
> You can read about the "query returned no rows" message here:
>
> https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html
>
> It seems to me you should only see it for `INTO STRICT`, not plain
> `INTO`. I see from your gist that your function *does* have some queries
> that are `INTO STRICT`, so I would focus on those.
>
> That page also describes how to use `print_strict_params` to get a
> little more info about the details of the error.
>
> I hope that helps!
>
> Paul
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 06/26/2017 12:03 PM, Paul Jungwirth wrote:
>> Perhaps
>> you should see what is line 85 when you do `\sf words_skip_game` (rather
>> than line 85 in your own source code).
>
> Or easier yet:
>
> https://www.postgresql.org/docs/9.5/static/app-psql.html
> "
> \ef [ function_description [ line_number ] ]

I am always nervous about answering questions here when so many actual
Postgres maintainers are around. But it's pretty great that so often
when I do it provokes an even better answer. It's not the first time!
:-)

Paul

On 06/26/2017 01:10 PM, Paul A Jungwirth wrote:
> On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver
> <adrian.klaver@aklaver.com> wrote:
>> On 06/26/2017 12:03 PM, Paul Jungwirth wrote:
>>> Perhaps
>>> you should see what is line 85 when you do `\sf words_skip_game` (rather
>>> than line 85 in your own source code).
>>
>> Or easier yet:
>>
>> https://www.postgresql.org/docs/9.5/static/app-psql.html
>> "
>> \ef [ function_description [ line_number ] ]
>
> I am always nervous about answering questions here when so many actual
> Postgres maintainers are around. But it's pretty great that so often
> when I do it provokes an even better answer. It's not the first time!
> :-)

The docs are extensive and in a constant state of change, so the list is
  a great resource for finding information you haven't stumbled across
yet on your own. It's how I have learned a lot deal about Postgres.

>
> Paul
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/26/2017 01:10 PM, Paul A Jungwirth wrote:
> On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver
> <adrian.klaver@aklaver.com> wrote:
>> On 06/26/2017 12:03 PM, Paul Jungwirth wrote:
>>> Perhaps
>>> you should see what is line 85 when you do `\sf words_skip_game` (rather
>>> than line 85 in your own source code).
>>
>> Or easier yet:
>>
>> https://www.postgresql.org/docs/9.5/static/app-psql.html
>> "
>> \ef [ function_description [ line_number ] ]
>
> I am always nervous about answering questions here when so many actual
> Postgres maintainers are around. But it's pretty great that so often
> when I do it provokes an even better answer. It's not the first time!
> :-)

This:

"It's how I have learned a lot deal about Postgres. "

should be:

"It's how I have learned a lot about Postgres. "

The hazards of editing on the fly.

>
> Paul
>


--
Adrian Klaver
adrian.klaver@aklaver.com