Обсуждение: [GENERAL] ERROR: type " " does not exist
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
Alex
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
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.
On Mon, Jun 12, 2017 at 4:28 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
ERROR: type " " does not exist at character 149Nothing 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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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
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.