Search Postgresql Archives

ERROR: type " " does not exist

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux