Thread: [GENERAL] ERROR: type " " does not exist

[GENERAL] ERROR: type " " does not exist

From

Alexander Farber

Date:

13 June 2017, 02:03:01

Good evening!

Why does PostgreSQL 9.5.4 print the error:

LOG: execute <unnamed>: SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns, out_sns AS sns, out_note AS note FROM words_resign_game($1::int, $2::int)

DETAIL: parameters: $1 = '2', $2 = '1'

ERROR: type " " does not exist at character 149

QUERY: SELECT

in_uid,

fcm,

apns,

sns,

'You have resigned at the score ' || _score1 || ':' || _score2

FROM words_users

WHERE uid = in_uid

CONTEXT: PL/pgSQL function words_resign_game(integer,integer) line 61 at SQL statement

STATEMENT: SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns, out_sns AS sns, out_note AS note FROM words_resign_game($1::int, $2::int)

I have also tried appending ::text to _score1 and _score2, but the error stayed same.

Below is my full custom function:

CREATE OR REPLACE FUNCTION words_resign_game(

in_uid integer,

in_gid integer

) RETURNS TABLE (

out_uid integer, -- the player to be notified

out_fcm text,

out_apns text,

out_sns text,

out_note text

) AS

$func$

DECLARE

_opponent integer;

_score1 integer;

_score2 integer;

BEGIN

UPDATE words_games SET

finished = CURRENT_TIMESTAMP,

played1 = CURRENT_TIMESTAMP

WHERE

gid = in_gid AND

player1 = in_uid AND

finished IS NULL

RETURNING

player2,

score1,

score2

INTO

_opponent,

_score1,

_score2;

IF NOT FOUND THEN

UPDATE words_games SET

finished = CURRENT_TIMESTAMP,

played2 = CURRENT_TIMESTAMP

WHERE

gid = in_gid AND

player2 = in_uid AND

finished IS NULL

RETURNING

player1,

score2,

score1

INTO

_opponent,

_score1,

_score2;

END IF;

IF NOT FOUND THEN

RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;

END IF;

IF _opponent IS NULL THEN

RAISE EXCEPTION 'Game % can not be resigned yet by user %', in_gid, in_uid;

END IF;

INSERT INTO words_moves (

action,

gid,

uid,

played

) VALUES (

'resign',

in_gid,

in_uid,

CURRENT_TIMESTAMP

);

SELECT

in_uid,

fcm,

apns,

sns,

'You have resigned at the score ' || _score1 || ':' || _score2

FROM words_users

WHERE uid = in_uid

INTO STRICT

out_uid,

out_fcm,

out_apns,

out_sns,

out_note;

RETURN NEXT;

SELECT

_opponent,

fcm,

apns,

sns,

'The opponent resigned at the score ' || _score2 || ':' || _score1

FROM words_users

WHERE uid = _opponent

INTO STRICT

out_uid,

out_fcm,

out_apns,

out_sns,

out_note;

RETURN NEXT;

END

$func$ LANGUAGE plpgsql;

Thank you
Alex

Re: [GENERAL] ERROR: type " " does not exist

From

Tom Lane

Date:

13 June 2017, 02:28:13

Alexander Farber <alexander.farber@gmail.com> writes:
> Why does PostgreSQL 9.5.4 print the error:
> ERROR:  type " " does not exist at character 149

Hmph.  Works for me, after reverse-engineering some tables and sample
data.  Maybe your actual function text contains some non-breaking spaces,
or other weird stuff that didn't show up in your email?  I find it a bit
fishy that it looks like there's two spaces after "resigned at the score"
in your function text, but only one in the error message.

            regards, tom lane

Re: [GENERAL] ERROR: type " " does not exist

From

"David G. Johnston"

Date:

13 June 2017, 02:28:49

On Mon, Jun 12, 2017 at 1:03 PM, Alexander Farber <alexander.farber@gmail.com> wrote:

ERROR: type " " does not exist at character 149

Nothing shown would explain what you are seeing. Of course, white-space issues are really hard to communicate via email.

As a brute-force suggestion I'd say you want to re-write the function one statement at a time to confirm that, one, you are indeed calling the correct function body, and, two figure out the exact statement that is problematic. I'd do this via copy-paste until the problem statement appears then I'd re-type in the problem statement by hand.

There are probably more efficient ways to do all this but given a lack of experience and a relatively small function I'd spend more effort trying to figure out a better way than just doing it brute force.

David J.

Re: [GENERAL] ERROR: type " " does not exist

From

Melvin Davidson

Date:

13 June 2017, 02:41:04

On Mon, Jun 12, 2017 at 4:28 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Mon, Jun 12, 2017 at 1:03 PM, Alexander Farber <alexander.farber@gmail.com> wrote:
ERROR: type " " does not exist at character 149

Nothing shown would explain what you are seeing. Of course, white-space issues are really hard to communicate via email.

As a brute-force suggestion I'd say you want to re-write the function one statement at a time to confirm that, one, you are indeed calling the correct function body, and, two figure out the exact statement that is problematic. I'd do this via copy-paste until the problem statement appears then I'd re-type in the problem statement by hand.

There are probably more efficient ways to do all this but given a lack of experience and a relatively small function I'd spend more effort trying to figure out a better way than just doing it brute force.

David J.

You did not state the O/S you are using, but if it's LINUX, just use vi and do a "set list".

That should reveal any strange characters that might be the cause,

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] ERROR: type " " does not exist

From

Adrian Klaver

Date:

13 June 2017, 02:53:32

On 06/12/2017 01:03 PM, Alexander Farber wrote:
> Good evening!
>
> Why does PostgreSQL 9.5.4 print the error:
>
> LOG:  execute <unnamed>: SELECT out_uid  AS uid,  out_fcm  AS fcm,
>   out_apns AS apns, out_sns  AS sns,  out_note AS note  FROM
> words_resign_game($1::int, $2::int)
> DETAIL:  parameters: $1 = '2', $2 = '1'
> ERROR:  type " " does not exist at character 149

What are you using to execute the above query and how are the parameters
being supplied to $1 and $2?




--
Adrian Klaver
adrian.klaver@aklaver.com

Re: [GENERAL] ERROR: type " " does not exist

From

Alexander Farber

Date:

14 June 2017, 22:14:24

Good evening everyone,

I apologize for the delay in replying and that you had to "reverse engineer" my question.

This turned out indeed to be a special char problem.

On MBA with macOS Sierra 10.12.5 I am using Postgres 9.5.4.1 of postgresapp.com.

At the psql prompt I had copy-pasted:

words=> SELECT 1, 'You have resigned ' || 1 || ':' || 1;

ERROR: type " " does not exist

LINE 1: SELECT 1, 'You have resigned ' || 1 || ':' || 1;

After I removed the "white space" char before ':', everything worked.

In vi (after \e) I could see that special char by typing %!xxd

00000000: 5345 4c45 4354 2031 2c20 2759 6f75 2068 SELECT 1, 'You h

00000010: 6176 6520 7265 7369 676e 6564 2027 207c ave resigned ' |

00000020: 7c20 3120 7c7c c2a0 273a 2720 7c7c 2031 | 1 ||..':' || 1

00000030: 3b0a ;.

Thank you.