ERROR: type " " does not exist

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: ERROR: type " " does not exist
Date: 2017-06-12 20:03:01
Message-ID: CAADeyWh4euu3nxGJJ6cV2dX023e6Yj8HH670bgzjO3tB-MUakA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-06-12 20:28:13 Re: ERROR: type " " does not exist
Previous Message Fabiana Zioti 2017-06-12 19:20:02 User defined Type PostgreSQL in C