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

2017-06-14 Thread Alexander Farber 
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

: 5345 4c45 4354 2031 2c20 2759 6f75 2068  SELECT 1, 'You h
0010: 6176 6520 7265 7369 676e 6564 2027 207c  ave resigned ' |
0020: 7c20 3120 7c7c c2a0 273a 2720 7c7c 2031  | 1 ||..':' || 1
0030: 3b0a ;.

Thank you.

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

2017-06-12 Thread Adrian Klaver 

On 06/12/2017 01:03 PM, Alexander Farber wrote:

Good evening!

Why does PostgreSQL 9.5.4 print the error:

LOG:  execute : 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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2017-06-12 Thread Melvin Davidson 
On Mon, Jun 12, 2017 at 4:28 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jun 12, 2017 at 1:03 PM, Alexander Farber <
> alexander.far...@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

2017-06-12 Thread David G. Johnston 
On Mon, Jun 12, 2017 at 1:03 PM, Alexander Farber <
alexander.far...@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

2017-06-12 Thread Tom Lane 
Alexander Farber  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ERROR: type " " does not exist

2017-06-12 Thread Alexander Farber 
Good evening!

Why does PostgreSQL 9.5.4 print the error:

LOG:  execute : 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
   FROMwords_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
FROMwords_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
FROMwords_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