Re: [GENERAL] Comparing epoch to timestamp

[Alexander Farber]

# EXPLAIN SELECT ARRAY_AGG(hashed)

FROM words_nouns

  WHERE added > to_timestamp(0)

 UNION

   SELECT ARRAY_AGG(hashed)

  FROM words_verbs

WHERE added > to_timestamp(0)

   ;
   QUERY PLAN


 Unique  (cost=2361.99..2362.00 rows=2 width=32)
   ->  Sort  (cost=2361.99..2361.99 rows=2 width=32)
 Sort Key: (array_agg(words_nouns.hashed))
 ->  Append  (cost=1517.06..2361.98 rows=2 width=32)
   ->  Aggregate  (cost=1517.06..1517.07 rows=1 width=32)
 ->  Seq Scan on words_nouns  (cost=0.00..1517.05
rows=1 width=32)
   Filter: (added > '1970-01-01
01:00:00+01'::timestamp with time zone)
   ->  Aggregate  (cost=844.88..844.89 rows=1 width=32)
 ->  Seq Scan on words_verbs  (cost=0.00..844.88 rows=1
width=32)
   Filter: (added > '1970-01-01
01:00:00+01'::timestamp with time zone)
(10 rows)

Re: [GENERAL] Comparing epoch to timestamp

[Alexander Farber]

Sorry, I probably had to call:

# EXPLAIN SELECT ARRAY_AGG(hashed)

FROM words_nouns

  WHERE EXTRACT(EPOCH FROM added) > 0

 UNION

   SELECT ARRAY_AGG(hashed)

  FROM words_verbs

WHERE EXTRACT(EPOCH FROM added) > 0

   ;
 QUERY PLAN

-
 Unique  (cost=2707.03..2707.04 rows=2 width=32)
   ->  Sort  (cost=2707.03..2707.03 rows=2 width=32)
 Sort Key: (array_agg(words_nouns.hashed))
 ->  Append  (cost=1740.53..2707.02 rows=2 width=32)
   ->  Aggregate  (cost=1740.53..1740.54 rows=1 width=32)
 ->  Seq Scan on words_nouns  (cost=0.00..1684.66
rows=22348 width=32)
   Filter: (date_part('epoch'::text, added) >
'0'::double precision)
   ->  Aggregate  (cost=966.45..966.46 rows=1 width=32)
 ->  Seq Scan on words_verbs  (cost=0.00..936.05
rows=12157 width=32)
   Filter: (date_part('epoch'::text, added) >
'0'::double precision)
(10 rows)

but still not sure if this statement is ok performancewise...

Regards
Alex

[GENERAL] Comparing epoch to timestamp

[Alexander Farber]

Hello,

in PostgreSQL 9.5 I have a table with 67000 records:

 # \d words_nouns
   Table "public.words_nouns"
 Column  |   Type   | Modifiers
-+--+---
 word| text | not null
 hashed  | text | not null
 added   | timestamp with time zone |
 removed | timestamp with time zone |
Indexes:
"words_nouns_pkey" PRIMARY KEY, btree (word)
Check constraints:
"words_nouns_word_check" CHECK (word ~ '^[А-Я]{2,}$'::text AND word !~
'[ЖШ]Ы'::text AND word !~ '[ЧЩ]Я'::text AND word !~ 'Ц[ЮЯ]'::text)
Triggers:
words_nouns_trigger BEFORE INSERT OR UPDATE ON words_nouns FOR EACH ROW
EXECUTE PROCEDURE words_trigger()

And a similar one words_verbs with 36000 records.

Is it a good idea to define the following custom function:

CREATE OR REPLACE FUNCTION words_get_added(
in_visited integer,
OUT out_json jsonb
) RETURNS jsonb AS
$func$
DECLARE
_added text[];
BEGIN
-- create array with words added to dictionary since in_visited
timestamp
IF in_visited > 0 THEN
_added := (
SELECT ARRAY_AGG(hashed)
FROM words_nouns
WHERE EXTRACT(EPOCH FROM added) > in_visited
UNION
SELECT ARRAY_AGG(hashed)
FROM words_verbs
WHERE EXTRACT(EPOCH FROM added) > in_visited
);

IF  CARDINALITY(_added) > 0 THEN
out_json := jsonb_build_object('added', _added);
END IF;
END IF;
END
$func$ LANGUAGE plpgsql;

or should I better transform in_visited to a timestamp with timezone and
compare to that?

I have tried the following, but am not sure how to interpret the result:

# explain select * from words_get_added(0);
 QUERY PLAN
-
 Function Scan on words_get_added  (cost=0.25..0.26 rows=1 width=32)
(1 row)

Thank you
Alex

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

[Alexander Farber]

Hello, I appreciate your comments, thank you

[GENERAL] Selecting a daily puzzle record - which type of column to add?

[Alexander Farber]

Good evening,

In a word game I store all player moves in the table:

CREATE TYPE words_action AS ENUM (
'play',
'skip',
'swap',
'resign',
'ban',
'expire'
);

CREATE TABLE words_moves (
mid BIGSERIAL PRIMARY KEY,
action  words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played  timestamptz NOT NULL,
tiles   jsonb,
score   integer CHECK(score >= 0)
);

I could run a cron job on all moves played each day and select the
"spectacular" ones by it, i.e. when a very big score has been achieved in
the move or all 7 tiles have been played...

Then I (as admin of the game) would manually review the daily mails sent by
that cronjob and select the few I have found interesting - for later
publishing them as "daily puzzle" in my day.

However I don't want to do the reviewing every day as that would be
tedious, but more like once per week and then select several such moves at
once (for the future dates).

My question (and thanks for reading my mail sofar) is: which column would
you add to the words_moves table for such a purpose?

If I add a boolean (is a daily puzzle move: true or false) - then it is
difficult to keep the order of the daily puzzles, I think.

If I add a timestamptz, then to which date to set it, when I do my manual
review once a week?

I need to add a useful column, so that it would be easy to me to create a
web script which would display today's and all past "daily puzzle" records
- and wouldn't change the already published puzzles...

If you have a good idea here, please share with me. If not, sorry for the
maybe offtopic question.

Thanks
Alex

Re: [GENERAL] make postgresql 9.5 default on centos 7

[Alexander Farber]

Oops, one correction - instead of

systemctl initdb postgresql-9.6

please use

/usr/pgsql-9.6/bin/postgresql96-setup initdb

as explained at https://wiki.postgresql.org/wiki/YUM_Installation

Regards

Alex

Re: [GENERAL] make postgresql 9.5 default on centos 7

[Alexander Farber]

Hi Steve,

On Fri, Aug 18, 2017 at 7:50 PM, Steve Clark 
wrote:

>
> I loaded 9.5 on CentOS 7 but by default every thing wants to use the
> default
> 9.2 version that comes with CentOS 7.
>
> Is there a simple way to fix this so the 9.5 version of tools and
> libraries are used.
>
>
to use PostgreSQL 9.6 on CentOS 7 -

first delete the postgresql packages coming with CentOS 7 by using "yum
erase " command and then:

rpm -Uvh
https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96-server
systemctl initdb postgresql-9.6
systemctl enable postgresql-9.6
systemctl start postgresql-9.6
systemctl status -l postgresql-9.6

Regards
Alex

[GENERAL] Type cast in PHP PDO (does not work like in Java?)

[Alexander Farber]

Good evening,

with PostgreSQL 9.6.3 and JDBC 42.1.1.jre7 types can be casted when calling
a stored function:

final String sql = "SELECT words_buy_vip(?::text, ?::int, ?::text,
?::text, ?::float, ?::inet)";

try (Connection db = DriverManager.getConnection(DATABASE_URL,
DATABASE_USER, DATABASE_PASS);
PreparedStatement st = db.prepareStatement(sql)) {
 st.setString(2, sid);
 st.setInt(1, SOCIAL_FACEBOOK);
 // etc.
 st.executeQuery();
}

But with PHP 5.4.16 on CentOS 7 Linux the types can not be casted (and
strangely the statement is just not being executed without any error being
reported) and the "::text", "::int" and "::inet" should be removed from the
placeholders as in:

$sql = 'SELECT words_buy_vip(?, ?, ?, ?, ?, ?)';   // can not use
type casts here?

try {
   $options = array(PDO::ATTR_ERRMODE =>
PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_CASE =>
PDO::CASE_LOWER);
   $dbh = new PDO(sprintf('pgsql:host=%s;port=%u;dbname=%s',
DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);
   $sth = $dbh->prepare($sql);
   $params = array($sid, SOCIAL_FACEBOOK, $payment_id,
$product, $price, $_SERVER['REMOTE_ADDR']);
$sth->execute($params);
} catch (PDOException $ex) {
error_log("PDO exception $ex");
}

Is that so or do I overlook something?

And when reading the PHP docs
http://php.net/manual/en/pdostatement.bindvalue.php
and http://php.net/manual/en/pdo.constants.php
then there is no constant to use for the "::inet"

Thank you for any comments
Alex

Re: [GENERAL] Do not INSERT if UPDATE fails

[Alexander Farber]

Hello, I have followed David's suggestion (thank you!) -

On Wed, Aug 2, 2017 at 6:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Aug 2, 2017 at 8:58 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>> However if the user record is not found or the user already has vip_until
>> >= CURRENT_TIMESTAMP (i.e. the user has already purchased "vip status") I
>> would like to cancel the INSERT.
>>
>>
> ​You can "join" two DDL commands by using a Common Table Expression (CTE)
> (i.e., WITH / SELECT)​.  You would need to make it so the UPDATE happens
> first and if there are no results the INSERT simply becomes a no-op.
>
>
and the following works (if I change the function return type to VOID):

CREATE OR REPLACE FUNCTION words_buy_vip(
in_sid text,
in_social integer,
in_tid text,
in_item text,
in_price float,
in_ip inet)
RETURNS void AS
$func$
WITH cte AS (
UPDATEwords_users u
SET   vip_until = CURRENT_TIMESTAMP + interval '1 year'
FROM  words_social s
WHERE s.sid= in_sid
AND   s.social = in_social
AND   u.uid= s.uid
AND   (u.vip_until IS NULL OR u.vip_until <
CURRENT_TIMESTAMP)
RETURNING
  u.uid AS uid,
  in_sidAS sid,
  in_social AS social,
  in_tidAS tid,
  in_price  AS price,
  in_ip AS ip
)
INSERT INTO words_payments (
sid,
social,
tid,
paid,
price,
ip
) SELECT
sid,
social,
tid,
CURRENT_TIMESTAMP,
price,
ip
FROM cte
-- RETURNING uid;

$func$ LANGUAGE sql;

But I wonder how to return the uid in the above statement?

(my original function returned integer uid)

Regards
Alex

[GENERAL] Do not INSERT if UPDATE fails

[Alexander Farber]

Good evening,

I have a custom SQL function in PostgreSQL 9.5.7 which adds a "log entry"
to the table words_payments and then updates "vip_until" column in another
table:

CREATE OR REPLACE FUNCTION words_buy_vip(
in_sid text,
in_social integer,
in_tid text,
in_item text,
in_price float,
in_ip inet)
RETURNS integer AS
$func$
INSERT INTO words_payments (
sid,
social,
tid,
paid,
price,
ip
) VALUES (
in_sid,
in_social,
in_tid,
CURRENT_TIMESTAMP,
in_price,
in_ip
);

UPDATEwords_users u
SET   vip_until = CURRENT_TIMESTAMP + interval '1 year'
FROM  words_social s
WHERE s.sid = in_sid
AND   s.social = in_social
AND   u.uid = s.uid
AND   (u.vip_until IS NULL OR u.vip_until < CURRENT_TIMESTAMP)
RETURNING u.uid;

$func$ LANGUAGE sql;

However if the user record is not found or the user already has vip_until
>= CURRENT_TIMESTAMP (i.e. the user has already purchased "vip status") I
would like to cancel the INSERT.

Is there please a way to rewrite the above function, without switching from
SQL to PL/pgSQL?

Regards
Alex

Re: [GENERAL] number of referencing and referenced columns for foreign key disagree

[Alexander Farber]

Thank you, Tom!

Should I have the CHECK in the new table written out again as in -

On Sat, Jul 29, 2017 at 3:41 PM, Tom Lane  wrote:
>
> You have to use the separate-constraint FK syntax:
>
> CREATE TABLE words_payments (
> sid textNOT NULL,
> social  integer NOT NULL ... ,
> foreign key (sid, social) references words_social
> );
>
> Or in even more pedantic detail:
>
> foreign key (sid, social) references words_social (sid, social)
>
> You'd have to use that if (sid, social) were not the PK of words_social
> but just some random unique key.
>


 CREATE TABLE words_payments (
sid textNOT NULL,
social  integer NOT NULL CHECK (0 < social AND social <= 64), /*
should I add this? */
trans   text  NOT NULL,
paidtimestamptz NOT NULL,
price   integer  NOT NULL CHECK (price > 0),
FOREIGN KEY (sid, social) REFERENCES words_social (sid, social) ON
DELETE CASCADE
);

Regards
Alex

[GENERAL] number of referencing and referenced columns for foreign key disagree

[Alexander Farber]

Good afternoon,

in 9.5.7 I have the following 2 tables -

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,

created timestamptz NOT NULL,
visited timestamptz NOT NULL,
ip  inetNOT NULL,

fcm text,
apnstext,
sns text,
motto   text,

vip_until   timestamptz,
grand_until timestamptz,

banned_until timestamptz,
banned_reason text CHECK (LENGTH(banned_reason) > 0),

elo integer NOT NULL CHECK (elo >= 0),
medals  integer NOT NULL CHECK (medals >= 0),
coins   integer NOT NULL
);

CREATE TABLE words_social (
sid text NOT NULL,
social  integer  NOT NULL CHECK (0 < social AND social <= 64),
given   text NOT NULL CHECK (given ~ '\S'),
family  text,
photo   text CHECK (photo ~* '^https?://...'),
lat float,
lng float,
stamp   integer  NOT NULL,

uid integer NOT NULL REFERENCES words_users ON DELETE
CASCADE,
PRIMARY KEY(sid, social)
);

And then I am trying to add another table, which should reference the (sid,
social) pair -

CREATE TABLE words_payments (
sid textNOT NULL REFERENCES words_social ON DELETE
CASCADE,
social  integer NOT NULL CHECK (0 < social AND social <= 64)
REFERENCES words_social ON DELETE CASCADE,
trans   textNOT NULL,
paidtimestamptz NOT NULL,
price   integer NOT NULL
);

Unfortunately, I get the error:

ERROR:  42830: number of referencing and referenced columns for foreign key
disagree
LOCATION:  ATAddForeignKeyConstraint, tablecmds.c:6345

How to refer to the (sid, social) FKs properly please?

The background is that social is one of the predefined (by me) constants:
public static final int UNKNOWN = 0;
public static final int GOOGLE  = 1;
public static final int APPLE   = 2;
public static final int ODNOKLASSNIKI   = 4;
public static final int MAILRU  = 8;
public static final int VKONTAKTE   = 16;
public static final int FACEBOOK= 32;
public static final int AMAZON  = 64;

And the sid is a "user id" used in the social network (for example Facebook
user id).

Thank you
Alex

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

[Alexander Farber]

Hi David,

On Mon, Jul 10, 2017 at 10:02 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jul 10, 2017 at 7:32 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>>
>> However there is a problem: I can not use a "single-instance" cronjob to
>> run words_expire_games hourly.
>>
>
> ​Setup a cron job that invokes the servlet - probably via "curl"
>

I will go with curl, thanks for that and the other comments

Regards
Alex

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

[Alexander Farber]

I have tried:

  FOR _gid, _loser, _winner IN
UPDATE  words_games
SET finished = CURRENT_TIMESTAMP
WHERE   finished IS NULL
AND played1 IS NOT NULL
AND played2 IS NOT NULL
AND (played1 < CURRENT_TIMESTAMP - INTERVAL '24 hours'
OR   played2 < CURRENT_TIMESTAMP - INTERVAL '24 hours')
RETURNING
gid,
CASE WHEN played1 < played2 THEN player1 ELSE player2 END,
CASE WHEN played1 < played2 THEN player2 ELSE player1 END
FOR UPDATE SKIP LOCKED
  LOOP
...
  END LOOP;

but this fails with:

ERROR:  syntax error at or near "FOR"

I have also described my problem at SO:


https://stackoverflow.com/questions/45015368/how-to-handle-simultaneous-for-in-update-returning-loops

Thank you
Alex

[GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

[Alexander Farber]

Good afternoon,

in PostgreSQL 9.5 with pgbouncer (having "pool_mode = session" and
"server_reset_query = DISCARD ALL") 2-player games are stored in the
following table:

CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,

created timestamptz NOT NULL,
finished timestamptz,

player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT
NULL CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,

played1 timestamptz,
played2 timestamptz
);

And every hour I run a custom PL/pgSQL function to forcibly finish games,
where one of the players hasn't played any move since more than 24h:
https://gist.github.com/afarber/416da460e5722ab1e3ed25385cea6cae (also
copy-pasted at the bottom of this mail).

However there is a problem: I can not use a "single-instance" cronjob to
run words_expire_games hourly.

I have to use the HttpServlet (so that I can send notifications to the
websocket-clients) and as result multiple servlet's might end up running at
the same time.

My question is if I should ensure that only 1 servlet runs the custom
PL/pgSQL function by using "synchronized" in Java as I do it right now:

private static final String SQL_EXPIRE_GAMES =
"SELECT " +
"out_uid  AS uid,  " +
"out_gid  AS gid,  " +
"out_fcm  AS fcm,  " +
"out_apns AS apns, " +
"out_sns  AS sns,  " +
"out_note AS note  " +
"FROM words_expire_games()";

// the timestamp in milliseconds of the last successful hourly job run
private static long sLastRun = 0L;

// this method is run every time the servlet is called (i.e. very often)
private void hourlyJob() throws SQLException, IOException {
if (System.currentTimeMillis() - sLastRun < ONE_HOUR) {
return;
}

synchronized (MyListener.class) {
if (System.currentTimeMillis() - sLastRun < ONE_HOUR) {
return;
}

try (PreparedStatement st =
mDatabase.prepareStatement(SQL_EXPIRE_GAMES)) {
try (ResultSet rs = st.executeQuery()) {
while (rs.next()) {
Notification n = new Notification(
rs.getInt(KEY_UID),
rs.getInt(KEY_GID),
true,
rs.getString(KEY_FCM),
rs.getString(KEY_APNS),
rs.getString(KEY_SNS),
rs.getString(KEY_NOTE)
);
sendNotification(n);  // send notifications about
forcibly finished games via websockets
}
}
}

sLastRun = System.currentTimeMillis();
}
}

Or if maybe there is some condition (maybe "UPDATE SKIP LOCKED"?) I could
add to my custom function copy-pasted below? -

Thank you for any insights
Alex

CREATE OR REPLACE FUNCTION words_expire_games(
) RETURNS TABLE (
out_uid  integer, -- the player to be notified
out_gid  integer, -- which game has expired
out_fcm  text,
out_apns text,
out_sns  text,
out_note text
) AS
$func$
DECLARE
_gidinteger;
_loser  integer;
_winner integer;
BEGIN
FOR _gid, _loser, _winner IN
UPDATE  words_games
SET finished = CURRENT_TIMESTAMP
WHERE   finished IS NULL
AND played1 IS NOT NULL
AND played2 IS NOT NULL
AND (played1 < CURRENT_TIMESTAMP - INTERVAL '24 hours'
OR   played2 < CURRENT_TIMESTAMP - INTERVAL '24 hours')
RETURNING
gid,
CASE WHEN played1 < played2 THEN player1 ELSE
player2 END,
CASE WHEN played1 < played2 THEN player2 ELSE
player1 END
LOOP
-- log the last "move"
INSERT INTO words_moves (
action,
gid,
uid,
played,
tiles
) VALUES (
'expire',
_gid,
_loser,
CURRENT_TIMESTAMP,
null
);

-- notify the loser
SELECT
uid,
_gid,
fcm,
apns,
sns,
'You have lost (game expired)!'
FROM words_users
WHERE   uid = _loser
INTO STRICT
out_uid,
out_gid,
   

Re: [GENERAL] ERROR: query returned no rows

[Alexander Farber]

Thank you Adrian, with \sf+ words_skip_game(integer, integer) the line 85
was correct

Re: [GENERAL] ERROR: query returned no rows

[Alexander Farber]

In my case _opponent was NULL and there are no records in words_users with
PK uid being NULL... so that was the reason.

Thank you

Re: [GENERAL] ERROR: query returned no rows

[Alexander Farber]

Hi again,

On Mon, Jun 26, 2017 at 8:21 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> words=> select * from words_skip_game(1, 1);
> ERROR:  query returned no rows
> CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at
> SQL statement
>
> When I look at my source code ( https://gist.github.com/afarber/
> cac9a83b7a37307ace8d787be9b8ff4c ) at the line 85, then I am not sure if
> the line number reported by the error message is correct
>


I have added many RAISE NOTICE '' lines and now see that the error is
at SELECT ... INTO STRICT (which was not the line 85):

SELECT
_opponent,
fcm,
apns,
sns,
CASE
WHEN _skips = 5 THEN 'Противник пропускает
ход (еще один пропуск завершит игру)'
ELSE 'Противник пропускает ход'
END
FROMwords_users
WHERE   uid = _opponent
INTO STRICT
out_uid,
out_fcm,
out_apns,
out_sns,
out_note;
RETURN NEXT;

Regards
Alex

Re: [SPAM] [GENERAL] ERROR: query returned no rows

[Alexander Farber]

On Mon, Jun 26, 2017 at 8:39 PM, Moreno Andreo <moreno.and...@evolu-s.it>
wrote:

> Il 26/06/2017 20:21, Alexander Farber ha scritto:
>
>>
>> RETURNING
>> player1,
>> score2,
>> score1
>> INTO
>> _opponent,
>> _score1,   -- the line 85
>> _score2;
>>
>> Is it intentional to return score2 in score1 and vice versa?



>
> Yes, Andreo, that one is intentional, but thank you.
>
> In all my custom functions I return player1, played1, hand1 for the
> calling player (even if it is player2 in the words_games table)
>

Regards
Alex

[GENERAL] ERROR: query returned no rows

[Alexander Farber]

Good evening,

with PostgreSQL 9.5 I have extended a larger custom function, which has
worked well before and my problem is that the error message returned by the
database is rather cryptic:

words=> select * from words_skip_game(1, 1);
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at SQL
statement

When I look at my source code (
https://gist.github.com/afarber/cac9a83b7a37307ace8d787be9b8ff4c ) at the
line 85, then I am not sure if the line number reported by the error
message is correct, because it points into middle of an UPDATE statement:

UPDATE words_games SET
finished = _finished,
played2  = CURRENT_TIMESTAMP
WHERE
gid  = in_gid AND
player2  = in_uid AND
-- game is not over yet
finished IS NULL  AND
-- and it is second player's turn
(played2 IS NULL OR played2 < played1)
RETURNING
player1,
score2,
score1
INTO
_opponent,
_score1,   -- the line 85
_score2;

And here is my words_games table:

words=> \d words_games
   Table "public.words_games"
  Column  |   Type   | Modifiers

--+--+---
 gid  | integer  | not null default
nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone | not null
 finished | timestamp with time zone |
 player1  | integer  | not null
 player2  | integer  |
 played1  | timestamp with time zone |
 played2  | timestamp with time zone |
 score1   | integer  | not null
 score2   | integer  | not null
 hand1| character(1)[]   | not null
 hand2| character(1)[]   | not null
 pile | character(1)[]   | not null
 letters  | character(1)[]   | not null
 values   | integer[]| not null
 bid  | integer  | not null
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
Check constraints:
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_notes" CONSTRAINT "words_notes_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

And the record for the gid=1 (apologies if I copy-paste too much data here):

words=> select * from words_games where gid=1;
 gid |created| finished | player1 | player2 |
played1 | played2 | score1 | score2 |  hand1  |  hand2  |

pile

 |






  letters







 |







 values






   | bid

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

[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.

[GENERAL] ERROR: type " " does not exist

[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

Re: [GENERAL] Merging records in a table with 2-columns primary key

[Alexander Farber]

Hello Andy and others -

On Sun, Apr 2, 2017 at 5:13 PM, Andy Colson <a...@squeakycode.net> wrote:

> On 04/02/2017 09:26 AM, Alexander Farber wrote:
>>
>> http://stackoverflow.com/questions/43168406/merging-records-
>> in-a-table-with-2-columns-primary-key
>
>

after some thinking, when I call

SELECT out_uid FROM merge_users(ARRAY[1,2,3,4]);

then "reviews" table should be empty (get rid of all self-reviews)
and "users" should contain just 1 record (1, 'User 1').

And if instead I call

SELECT out_uid FROM merge_users(ARRAY[1,2]);

then "reviews" should be (records with User 2 removed because overlapped
with User 1):

 uid | author | review
-++
   1 |  3 | User 3 says: 1 is nice
   1 |  4 | User 4 says: 1 is nice
   3 |  1 | User 1 says: 3 is nice
   3 |  4 | User 4 says: 3 is ugly
   4 |  1 | User 1 says: 4 is ugly
   4 |  3 | User 3 says: 4 is ugly

and "users":

 uid |  name
-+
   1 | User 1
   3 | User 3
   4 | User 4

So my 2 questions are -

1) Why the error when calling merge_users(ARRAY[1,2]) and then
merge_users(ARRAY[1,2,3,4])?

2) Is there a way to use an UPDATE reviews instead of the inefficient
(because copying) INSERT ... SELECT ... ON CONFLICT DO NOTHING?

Thank you
Alex

[GENERAL] Merging records in a table with 2-columns primary key

[Alexander Farber]

Good afternoon,

I have prepared a simple test case for my question -

CREATE TABLE users (
uid SERIAL PRIMARY KEY,
name varchar(255) NOT NULL
);

CREATE TABLE reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES users ON
DELETE CASCADE,
author integer NOT NULL REFERENCES users(uid) ON DELETE CASCADE,
review varchar(255),
PRIMARY KEY(uid, author)
);

Here I fill the above tables with sample data -

INSERT INTO users (uid, name) VALUES (1, 'User 1');
INSERT INTO users (uid, name) VALUES (2, 'User 2');
INSERT INTO users (uid, name) VALUES (3, 'User 3');
INSERT INTO users (uid, name) VALUES (4, 'User 4');

INSERT INTO reviews (uid, author, review) VALUES (1, 2, 'User 2 says: 1 is
nice');
INSERT INTO reviews (uid, author, review) VALUES (1, 3, 'User 3 says: 1 is
nice');
INSERT INTO reviews (uid, author, review) VALUES (1, 4, 'User 4 says: 1 is
nice');

INSERT INTO reviews (uid, author, review) VALUES (2, 1, 'User 1 says: 2 is
nice');
INSERT INTO reviews (uid, author, review) VALUES (2, 3, 'User 3 says: 2 is
nice');
INSERT INTO reviews (uid, author, review) VALUES (2, 4, 'User 4 says: 2 is
ugly');

INSERT INTO reviews (uid, author, review) VALUES (3, 1, 'User 1 says: 3 is
nice');
INSERT INTO reviews (uid, author, review) VALUES (3, 2, 'User 2 says: 3 is
ugly');
INSERT INTO reviews (uid, author, review) VALUES (3, 4, 'User 4 says: 3 is
ugly');

INSERT INTO reviews (uid, author, review) VALUES (4, 1, 'User 1 says: 4 is
ugly');
INSERT INTO reviews (uid, author, review) VALUES (4, 2, 'User 2 says: 4 is
ugly');
INSERT INTO reviews (uid, author, review) VALUES (4, 3, 'User 3 says: 4 is
ugly');

And finally here is my problematic custom stored function:

CREATE OR REPLACE FUNCTION merge_users(
in_uids integer[],
OUT out_uid integer
) RETURNS integer AS
$func$
BEGIN
SELECT
MIN(uid)
INTO STRICT
out_uid
FROM users
WHERE uid = ANY(in_uids);

-- delete self-reviews
DELETE FROM reviews
WHERE uid = out_uid
AND author = ANY(in_uids);

DELETE FROM reviews
WHERE author = out_uid
AND uid = ANY(in_uids);

-- try to copy as many reviews OF this user as possible
INSERT INTO reviews (
uid,
author,
review
) SELECT
out_uid,-- change to out_uid
author,
review
FROM reviews
WHERE uid <> out_uid
AND uid = ANY(in_uids)
ON CONFLICT DO NOTHING;

DELETE FROM reviews
WHERE uid <> out_uid
AND uid = ANY(in_uids);

-- try to copy as many reviews BY this user as possible
INSERT INTO reviews (
uid,
author,
review
) SELECT
uid,
out_uid,-- change to out_uid
review
FROM reviews
WHERE author <> out_uid
AND author = ANY(in_uids)
ON CONFLICT DO NOTHING;

DELETE FROM reviews
WHERE author <> out_uid
AND author = ANY(in_uids);

DELETE FROM users
WHERE uid <> out_uid
AND uid = ANY(in_uids);
END
$func$ LANGUAGE plpgsql;

The purpose of the function is to merge several user records to one (with
the lowest uid).

While merging the reviews records I delete all self-reviews and try to copy
over as many remaining reviews as possible.

However with PostgreSQL 9.5 the following 2 calls fail:

test=> SELECT out_uid FROM merge_users(ARRAY[1,2]);
 out_uid
-
   1
(1 row)

test=> SELECT out_uid FROM merge_users(ARRAY[1,2,3,4]);
ERROR:  new row for relation "reviews" violates check constraint
"reviews_check"
DETAIL:  Failing row contains (1, 1, User 4 says: 3 is ugly).
CONTEXT:  SQL statement "INSERT INTO reviews (
uid,
author,
review
) SELECT
uid,
out_uid,-- change to out_uid
review
FROM reviews
WHERE author <> out_uid
AND author = ANY(in_uids)
ON CONFLICT DO NOTHING"
PL/pgSQL function merge_users(integer[]) line 38 at SQL statement

I have provided more context at
http://stackoverflow.com/questions/43168406/merging-records-in-a-table-with-2-columns-primary-key

Also I have tried to create an SQL Fiddle at
http://sqlfiddle.com/#!15/5f37e/2
for your convenience

Regards
Alex

[GENERAL] Combining INSERT with DELETE RETURNING

[Alexander Farber]

Good afternoon,

the doc https://www.postgresql.org/docs/9.6/static/sql-delete.html states:

"The syntax of the RETURNING list is identical to that of the output list
of SELECT."

So I am trying to rewrite the INSERT .. ON CONFLICT DO NOTHING followed by
a DELETE:

INSERT INTO words_reviews (
uid,
author,
nice,
review,
updated
) SELECT
uid,
out_uid,-- change to out_uid
nice,
review,
updated
FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids)
ON CONFLICT DO NOTHING;

DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids);

into a single statement:

INSERT INTO words_reviews (
uid,
author,
nice,
review,
updated
) VALUES (
DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids)
RETURNING
uid,
out_uid,-- change to out_uid
nice,
review,
updated
)
ON CONFLICT DO NOTHING;

but get the syntax error:

words=> \i words_merge_users.sql
psql:words_merge_users.sql:218: ERROR:  syntax error at or near "FROM"
LINE 131: DELETE FROM words_reviews
 ^

What am I doing wrong this time please?

Thank you
Alex

P.S. Below is my custom function in its entirety + table descriptions:

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,

created timestamptz NOT NULL,
visited timestamptz NOT NULL,
ip inet NOT NULL,

fcm varchar(255),
apnsvarchar(255),
motto   varchar(255),

vip_until   timestamptz,
grand_until timestamptz,

banned_until timestamptz,
banned_reason varchar(255) CHECK (LENGTH(banned_reason) > 0),

elointeger NOT NULL CHECK (elo >= 0),
medals integer NOT NULL CHECK (medals >= 0),
coins  integer NOT NULL
);

CREATE TABLE words_social (
sid varchar(255) NOT NULL,

social integer NOT NULL CHECK (0 <= social AND social <= 6),
female integer NOT NULL CHECK (female = 0 OR female = 1),
given  varchar(255) NOT NULL CHECK (given ~ '\S'),
family varchar(255),
photo  varchar(255) CHECK (photo ~* '^https?://...'),
place  varchar(255),
stamp  integer NOT NULL,

uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

CREATE OR REPLACE FUNCTION words_merge_users(
in_users jsonb,
in_ip inet,
OUT out_uid integer,
OUT out_vip timestamptz,
OUT out_grand timestamptz,
OUT out_banned timestamptz,
OUT out_reason varchar
) RETURNS RECORD AS
$func$
DECLARE
_user  jsonb;
_uids  integer[];
-- the variables below are used to temporary save new user stats
_created   timestamptz;
_elo   integer;
_medalsinteger;
_coins integer;
BEGIN
-- in_users must be a JSON array with at least 1 element
IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
RAISE EXCEPTION 'Invalid users = %', in_users;
END IF;

_uids := (
SELECT ARRAY_AGG(DISTINCT uid)
FROM words_social
JOIN JSONB_ARRAY_ELEMENTS(in_users) x
ON sid = x->>'sid'
AND social = (x->>'social')::int
);

IF _uids IS NULL THEN
-- no users found -> create a new user
INSERT INTO words_users (
created,
visited,
ip,
elo,
medals,
coins
) VALUES (
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP,
in_ip,
1500,
  

Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv

[Alexander Farber]

Hi David,

On Tue, Mar 21, 2017 at 8:21 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
> On Tuesday, March 21, 2017, Alexander Farber <alexander.far...@gmail.com>
wrote:
>>
>> words=> COPY words_reviews (uid, author, nice, review, updated) FROM
stdin FORMAT csv;
>
>
> What did you read that lead you to think the above shoud work?
>

ok thanks, I had misunderstood your text -

"The above means the entire "WITH" section is optional, as is the word
WITH.  However, if you want to add "with" options they must appear within
parentheses, those are not optional.  Multiple options can appear within
the single set of parentheses."

I think the root cause is that it is difficult to understand an english
sentence trying to describe a keyword "with" :-)

Regards
Alex

Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv

[Alexander Farber]

Thank you - this has worked:

COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH
(FORMAT csv);
1,2,1,'1 is nice by 2','2017-03-01'
1,3,1,'1 is nice by 3','2017-03-02'
1,4,1,'1 is nice by 4','2017-03-03'
2,1,1,'2 is nice by 1','2017-03-01'
2,3,1,'2 is nice by 3','2017-03-02'
2,4,0,'2 is not nice by 4','2017-03-03'
\.

but I am confused about the comments that I should use \copy and not just
COPY and also that I could leave out WITH and brackets.

Because please take a look at the 9.6.2 psql output (COPY works, and
leaving out WITH brackets - not):

words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin
WITH (FORMAT csv);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1,2,1,'1 is nice by 2','2017-03-01'
1,3,1,'1 is nice by 3','2017-03-02'
1,4,1,'1 is nice by 4','2017-03-03'
2,1,1,'2 is nice by 1','2017-03-01'
2,3,1,'2 is nice by 3','2017-03-02'
2,4,0,'2 is not nice by 4','2017-03-03'
\.
>> >> >> >> >> >> COPY 6
words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin
FORMAT csv;
ERROR:  syntax error at or near "FORMAT"
LINE 1: ...s (uid, author, nice, review, updated) FROM stdin FORMAT csv...
 ^

[GENERAL] COPY ... FROM stdin WITH FORMAT csv

[Alexander Farber]

Good evening,

I keep rereading https://www.postgresql.org/docs/9.6/static/sql-copy.html
but just can't figure the proper syntax to put some records into the table:

words=> \d words_reviews;
  Table "public.words_reviews"
 Column  |   Type   | Modifiers
-+--+---
 uid | integer  | not null
 author  | integer  | not null
 nice| integer  | not null
 review  | character varying(255)   |
 updated | timestamp with time zone | not null
Indexes:
"words_reviews_pkey" PRIMARY KEY, btree (uid, author)
Check constraints:
"words_reviews_check" CHECK (uid <> author)
"words_reviews_nice_check" CHECK (nice = 0 OR nice = 1)
Foreign-key constraints:
"words_reviews_author_fkey" FOREIGN KEY (author) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_reviews_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
ON DELETE CASCADE

words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin
WITH FORMAT 'csv';
ERROR:  syntax error at or near "FORMAT"
LINE 1: ...d, author, nice, review, updated) FROM stdin WITH FORMAT 'cs...
 ^
words=> 1,2,1,'1 is nice by 2','2017-03-01',
words-> 1,3,1,'1 is nice by 3','2017-03-02',
words-> 1,4,1,'1 is nice by 4','2017-03-03',
words-> 2,1,1,'2 is nice by 1','2017-03-01',
words-> 2,3,1,'2 is nice by 3','2017-03-02',
words-> 2,4,0,'2 is not nice by 4','2017-03-03'
words-> \.
Invalid command \.. Try \? for help.
words-> ;
ERROR:  syntax error at or near "1"
LINE 1: 1,2,1,'1 is nice by 2','2017-03-01',
^

I am not sure if FORMAT csv or FORMAT 'csv' should be used.

And I have tried adding/removing commas at the end of lines too.

I have looked at pg_dump output, but it does not use csv.

Regards
Alex

[GENERAL] Generating JSON-encoded list of object out of joined tables

[Alexander Farber]

Good afternoon,

what would be please the best way to generate a list of JSON objects out of
an SQL join?

I am using jQuery dataTables plugin and initially was performing an SQL
join and then in my PHP script was fetching results row by row and finally
encoded them to JSON and feeded to the plugin.

But then I realized that with PostgreSQL that part could be spared and
after reading
https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql I
have rewritten my custom function:

CREATE OR REPLACE FUNCTION words_get_longest2(
in_uid integer
) RETURNS json AS
$func$
SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(x))) FROM (
SELECT
ROW_NUMBER() OVER () AS row,
s.gid AS gid,
TO_CHAR(g.created, 'DD.MM. HH24:MI') AS created,
TO_CHAR(g.finished, 'DD.MM. HH24:MI') AS
finished,
CASE WHEN g.player1 = in_uid THEN g.player1 ELSE
g.player2 END AS player1,
CASE WHEN g.player1 = in_uid THEN g.player2 ELSE
g.player1 END AS player2,
CASE WHEN g.player1 = in_uid THEN g.score1 ELSE
g.score2 END AS score1,
CASE WHEN g.player1 = in_uid THEN g.score2 ELSE
g.score1 END AS score2,
s1.female AS female1,
s2.female AS female2,
s1.given AS given1,
s2.given AS given2,
s1.photo AS photo1,
s2.photo AS photo2,
s1.place AS place1,
s2.place AS place2,
s.word AS word,
s.score AS score,
m.tiles AS tiles
FROMwords_scores s
LEFT JOIN words_games g USING(gid)
LEFT JOIN words_moves m USING(mid)
LEFT JOIN words_social s1 ON s1.uid = in_uid
-- find social record with the most recent timestamp
AND NOT EXISTS (SELECT 1
FROM words_social s
WHERE s1.uid = s.uid
AND s.stamp > s1.stamp)
LEFT JOIN words_social s2 ON s2.uid = (CASE WHEN g.player1
= in_uid THEN g.player2 ELSE g.player1 END)
-- find social record with the most recent timestamp
AND NOT EXISTS (SELECT 1
FROM words_social s
WHERE s2.uid = s.uid
AND s.stamp > s2.stamp)
WHERE s.uid = in_uid
ORDER BY LENGTH(s.word) DESC, s.mid DESC
LIMIT   10
) x;

$func$ LANGUAGE sql STABLE;

which delivers me results like:

words=> select words_get_longest2(2);



words_get_longest2



---
---
---
---
---
-
 [{"row":2,"gid":1,"created":"17.03.2017
09:06","finished":null,"player1":2,"player2":1,"score1":24,"score2":51,"female1":0,"female2":0,"given1":"Ghijk4","given2":"Abcde1","photo1":null,"
photo2":"https://vk.com/images/camera_200.png","place1":null,"place2":null,"word":"СМЯТИЕ","score":16,"tiles":[{"col":
5, "row": 13, "value": 1, "letter": "Е"}, {"col": 5, "row": 12, "val
ue": 1, "letter": "И"}, {"col": 5, "row": 11, "value": 2, "letter": "Т"},
{"col": 5, "row": 10, "value": 0, "letter": "Я"}, {"col": 5, "row": 8,
"value": 2, "letter": "С"}]},{"row":1,"gid
":1,"created":"17.03.2017
09:06","finished":null,"player1":2,"player2":1,"score1":24,"score2":51,"female1":0,"female2":0,"given1":"Ghijk4","given2":"Abcde1","photo1":null,"photo2":"https:
//vk.com/images/camera_200.png","place1":null,"place2":null,"word":"МЕХ","score":8,"tiles":[{"col":
6, "row": 9, "value": 1, "letter": "Е"}, {"col": 5, "row": 9, "value": 2,
"letter": "М"
}]}]
(1 row)

Is that please a good approach or is there maybe a better way with
PostgreSQL 9.5 or 9.6?

Thank you
Alex

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber]

Yasin, thank you for this suggestion, but -

On Tue, Mar 14, 2017 at 12:07 PM, Yasin Sari 
wrote:

​​
>> Have you tried with inner block and do nothing on exception;
>>
>
>
>> BEGIN
>>
>>  
>> ..
>>  BEGIN
>>
>>   UPDATE words_social
>> SET uid = out_uid
>> WHERE uid = ANY(_uids);
>>
>>  EXCEPTION WHEN OTHERS THEN
>> --do nothing or write NULL means do nothing
>>
>>
>
>
but it seems to me that my method of INSERTing copies would be faster than
the 2 loops

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber]

Hi Adrian -

On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 03/14/2017 09:00 AM, Alexander Farber wrote:
>
>> My initial idea has been not to use ON CONFLICT at all, but instead only
>> UPDATE the words_reviews records (set "uid" or "author" to out_uid) for
>> which NO EXISTS already such a record with PK (uid, author)... and then
>>
>
> I am afraid the logic is escaping me. If the record does not exist how can
> you UPDATE it?
>
>
here the table with reviews of users "uid" done by users "author":

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

Since I am merging user ids (from several to one), I need to change the
records in the above table too.

I need to update the PK by changing either "uid" or "author".

But this might give me conflicts, because there might be such a PK
already...

Regards
Alex

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber]

My initial idea has been not to use ON CONFLICT at all, but instead only
UPDATE the words_reviews records (set "uid" or "author" to out_uid) for
which NO EXISTS already such a record with PK (uid, author)... and then
DELETE the rest (as I can't update them without a conflict) but I haven't
figured it out yet...

Thank you anyway

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber]

Yes, Adrian -

On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 03/14/2017 07:23 AM, Alexander Farber wrote:
>
>>
>> in _uids array I have all user ids of player.
>>
>> I want to merge his or her data, including reviews, to a single user id:
>> out_uid.
>>
>> So I make a copy of related words_reviews records (where this user has
>>
>
> Alright I see that you are setting out_uid above the INSERT.
> So you are INSERTing rows and if they CONFLICT you leave them alone and
> have the DELETE remove them, correct?
>
>
that is correct and my question is:

copying records for later INSERT ON CONFLICT DO NOTHING seems ineffective
to me.

I would prefer to just use UPDATE (for changing "uid" or "author" columns
to out_uid value) - but how to handle the conflicts with UPDATE?

Thank you
Alex

P.S. I have just donated 20 Euro to pay...@postgresql.eu

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber]

Hi Adrian,

in _uids array I have all user ids of player.

I want to merge his or her data, including reviews, to a single user id:
out_uid.

So I make a copy of related words_reviews records (where this user has been
rated or this user has rated someone) and then re-INSERT ON CONFLICT DO
NOTHING those records into same table (but change the "uid" or "author"
column). And finally DELETE old records.

I hope my question is not too annoying, just trying to pick up tricks and
better strategies here.

Thank you
Alex

P.S. Below is my table data and the complete custom function for your
convenience -

CREATE TABLE words_social (
sid varchar(255) NOT NULL,

social integer NOT NULL CHECK (0 <= social AND social <= 6),  /*
Facebook, Google+, Twitter, ... */
female integer NOT NULL CHECK (female = 0 OR female = 1),
given  varchar(255) NOT NULL CHECK (given ~ '\S'),
family varchar(255),
photo  varchar(255) CHECK (photo ~* '^https?://...'),
place  varchar(255),
stamp  integer NOT NULL,

uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,

created timestamptz NOT NULL,
visited timestamptz NOT NULL,
ip inet NOT NULL,
.
wininteger NOT NULL CHECK (win >= 0),
loss   integer NOT NULL CHECK (loss >= 0),
draw   integer NOT NULL CHECK (draw >= 0),

elointeger NOT NULL CHECK (elo >= 0),
medals integer NOT NULL CHECK (medals >= 0),
coins  integer NOT NULL
);

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

CREATE OR REPLACE FUNCTION words_merge_users(
in_users jsonb,
in_ip inet,
OUT out_uid integer
) RETURNS RECORD AS
$func$
DECLARE
_user  jsonb;
_uids  integer[];
-- the variables below are used to temporary save new user stats
_created   timestamptz;
_win   integer;
_loss  integer;
_draw  integer;
_elo   integer;
_medalsinteger;
_coins integer;
BEGIN
-- in_users must be a JSON array with at least 1 element
IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
RAISE EXCEPTION 'Invalid users = %', in_users;
END IF;

_uids := (
SELECT ARRAY_AGG(DISTINCT uid)
FROM words_social
JOIN JSONB_ARRAY_ELEMENTS(in_users) x
ON sid = x->>'sid'
AND social = (x->>'social')::int
);

IF _uids IS NULL THEN
-- no users found -> create a new user
INSERT INTO words_users (
created,
visited,
ip,
win,
loss,
draw,
elo,
medals,
coins
) VALUES (
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP,
in_ip,
0,
0,
0,
1500,
0,
0
) RETURNING uid INTO STRICT out_uid;

ELSIF CARDINALITY(_uids) = 1 THEN
-- just 1 user found -> update timestamp and IP address
SELECT
uid
INTO STRICT
out_uid
FROM words_users
WHERE uid = _uids[1];

UPDATE words_users SET
visited = CURRENT_TIMESTAMP,
ip  = in_ip
WHERE uid = out_uid;
ELSE
-- few users found -> merge their records to a single one
SELECT
MIN(uid),
MIN(created),
SUM(win),
SUM(loss),
SUM(draw),
AVG(elo),
SUM(medals),
SUM(coins)
INTO STRICT
out_uid,
_created,
_win,
_loss,
_draw,
_elo,
_medals,
_coins
FROM words_users
WHERE uid = ANY(_uids);

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber]

Sorry, missed the last DELETE:

DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids);

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber]

I have come up with the following (when trying to merge array _uids to a
single out_uid):

-- try to copy as many reviews of this user as possible
INSERT INTO words_reviews (
uid,
author,
nice,
review,
updated
) SELECT
out_uid,
author,
nice,
review,
updated
FROM words_reviews
WHERE uid <> out_uid
AND uid = ANY(_uids)
ON CONFLICT DO NOTHING;

DELETE FROM words_reviews
WHERE uid <> out_uid
AND uid = ANY(_uids);

-- try to copy as many reviews by this user as possible
INSERT INTO words_reviews (
uid,
author,
nice,
review,
updated
) SELECT
uid,
out_uid,
nice,
review,
updated
FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids)
ON CONFLICT DO NOTHING;

[GENERAL] UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber]

Good afternoon,

I am working on a small game, where the mobile app initially sends social
network user data (name, city, gender) to PostgreSQL 9.5 backend and I
store that data in a table:

CREATE TABLE words_social (
sid varchar(255) NOT NULL,

social integer NOT NULL CHECK (0 <= social AND social <= 6),  /*
Facebook, Googl+, Twitter, ... */
female integer NOT NULL CHECK (female = 0 OR female = 1),
given  varchar(255) NOT NULL CHECK (given ~ '\S'),
family varchar(255),
photo  varchar(255) CHECK (photo ~* '^https?://...'),
place  varchar(255),
stamp  integer NOT NULL,

uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);

And then I have another larger table holding the rest of user information:

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,

created timestamptz NOT NULL,
visited timestamptz NOT NULL,
ip inet NOT NULL,
.
wininteger NOT NULL CHECK (win >= 0),
loss   integer NOT NULL CHECK (loss >= 0),
draw   integer NOT NULL CHECK (draw >= 0),

elointeger NOT NULL CHECK (elo >= 0),
medals integer NOT NULL CHECK (medals >= 0),
coins  integer NOT NULL
);

Whenever the mobile app notices, that the user authenticated against
several social networks - I merge his data in my custom function:

CREATE OR REPLACE FUNCTION words_merge_users(
in_users jsonb,
in_ip inet,
OUT out_uid integer /* the user id of the merged user */
) RETURNS RECORD AS
$func$
DECLARE
_user  jsonb;
_uids  integer[];
-- the variables below are used to temporary save new user stats
_created   timestamptz;
_win   integer;
_loss  integer;
_draw  integer;
_elo   integer;
_medalsinteger;
_coins integer;
BEGIN

_uids := (
SELECT ARRAY_AGG(DISTINCT uid)
FROM words_social
JOIN JSONB_ARRAY_ELEMENTS(in_users) x
ON sid = x->>'sid'
AND social = (x->>'social')::int
);

-- few users found -> merge their records to a single one
IF CARDINALITY(_uids) > 1 THEN
SELECT
MIN(uid),
MIN(created),
SUM(win),
SUM(loss),
SUM(draw),
AVG(elo),
SUM(medals),
SUM(coins)
INTO STRICT
out_uid,/* this is the new user id */
_created,
_win,
_loss,
_draw,
_elo,
_medals,
_coins
FROM words_users
WHERE uid = ANY(_uids);

-- How to merge words_reviews? Please read below...

UPDATE words_social
SET uid = out_uid
WHERE uid = ANY(_uids);

DELETE FROM words_users
WHERE uid <> out_uid
AND uid = ANY(_uids);

UPDATE words_users SET
visited   = CURRENT_TIMESTAMP,
ip= in_ip,
created   = _created,
win   = _win,
loss  = _loss,
draw  = _draw,
elo   = _elo,
medals= _medals,
coins = _coins
WHERE uid = out_uid;
END IF;
END
$func$ LANGUAGE plpgsql;

This works well, but now I have introduced a table where users can rate
each other ("author" can rate "uid"):

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

And now I have a problem, because while merging user data I can not just:

UPDATE words_reviews  /* This will produce conflicts...
*/
SET uid = out_uid
WHERE uid = ANY(_uids);

DELETE FROM words_reviews
WHERE uid <> out_uid
AND uid = ANY(_uids);

And same for the authoring part - I can not just:

UPDATE words_reviews  /* This will produce conflicts...
*/
SET author = out_uid
WHERE author = ANY(_uids);

  

Re: [GENERAL] DELETE and JOIN

[Alexander Farber]

Good morning and thank you for the replies.

I've ended up with the following DELETE USING (in order to delete reviews
coming from different user id, but same IP address in the last 24 hours):

DELETE  FROM words_reviews r
USING   words_users u
WHERE   r.uid = u.uid
AND r.uid = in_uid
AND AGE(r.updated) < INTERVAL '1 day'
AND u.ip = (SELECT ip FROM words_users WHERE uid = in_author);

Regards
Alex

PS: Here is my custom function:

CREATE OR REPLACE FUNCTION words_review_user(
in_uid integer,  /* the player in_uid... */
in_author integer, /* ... is reviewed by player in_author */
in_nice integer,
in_review varchar
) RETURNS void AS
$func$
BEGIN
DELETE  FROM words_reviews r
USING   words_users u
WHERE   r.uid = u.uid
AND r.uid = in_uid
AND AGE(r.updated) < INTERVAL '1 day'
AND u.ip = (SELECT ip FROM words_users WHERE uid = in_author);

UPDATE words_reviews SET
author= in_author,
nice  = in_nice,
review= in_review,
updated   = CURRENT_TIMESTAMP
WHERE uid = in_uid AND author = in_author;

IF NOT FOUND THEN
INSERT INTO words_reviews (
uid,
author,
nice,
review,
updated
) VALUES (
in_uid,
in_author,
in_nice,
in_review,
CURRENT_TIMESTAMP
);
END IF;
END
$func$ LANGUAGE plpgsql;

And here are the tables in question:

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL,
..
);

[GENERAL] DELETE and JOIN

[Alexander Farber]

Good evening,

In a 9.5 database I would like players to rate each other and save the
reviews in the table:

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

while user names and IP addresses are saved in the other database:

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL,
..
);

However, before saving a review, I would like to delete all previous
reviews coming from the same IP in the past 24 hours:

CREATE OR REPLACE FUNCTION words_review_user(
in_uid integer,/* this user is being rated */
in_author integer,   /* by the in_author user */
in_nice integer,
in_review varchar
) RETURNS void AS
$func$
DECLARE
_author_rep integer;
_author_ip integer;
BEGIN

/* find the current IP address of the author */

SELECT  ip
INTO_author_ip
FROMwords_users
WHERE   uid = in_author;

/* try to prevent review fraud - how to improve this query please?
*/

DELETE  FROM words_reviews
WHERE   uid = in_uid
AND AGE(updated) < INTERVAL '1 day'
AND EXISTS (
SELECT 1
FROM words_reviews r INNER JOIN words_users u USING(uid)
WHERE u.ip = u._author_ip
AND r.author = in_author
);

UPDATE words_reviews set
author= in_author,
nice  = in_nice,
review= in_review,
updated   = CURRENT_TIMESTAMP
WHERE uid = in_uid AND author = in_author;

IF NOT FOUND THEN
INSERT INTO words_reviews (
author,
nice,
review,
updated
) VALUES (
in_author,
in_nice,
in_review,
CURRENT_TIMESTAMP
);
END IF;

END
$func$ LANGUAGE plpgsql;

I have the feeling that the _author_ip variable is not really necessary and
I could use some kind of "DELETE JOIN" here, but can not figure it out.

Please advise a better query if possible

Best regards
Alex

Re: [GENERAL] CHECK for 2 FKs to be non equal

[Alexander Farber]

Thank you Alban and Francisco -

On Sat, Mar 11, 2017 at 11:52 AM, Alban Hertroys <haram...@gmail.com> wrote:
>
> > On 11 Mar 2017, at 10:41, Alexander Farber <alexander.far...@gmail.com>
> wrote:
> >  uid integer NOT NULL REFERENCES words_users(uid) CHECK (uid <>
> author) ON DELETE CASCADE,
>
>
> You put your CHECK constraint definition smack in the middle of the FK
> constraint definition, which starts with REFERENCES and ends with the
> delete CASCADE.
>
>
you are both correct!

[GENERAL] CHECK for 2 FKs to be non equal

[Alexander Farber]

Good morning,

I am trying to add a table holding player reviews of each other:

words=> CREATE TABLE words_reviews (
 uid integer NOT NULL REFERENCES words_users(uid) CHECK (uid <>
author) ON DELETE CASCADE,
 author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
 nice boolean NOT NULL,
 review varchar(255),
 updated timestamptz NOT NULL,
 PRIMARY KEY(uid, author)
);

but get syntax error in 9.5:

ERROR:  syntax error at or near "ON"
LINE 2: ...REFERENCES words_users(uid) CHECK (uid <> author) ON DELETE ...
 ^

My intention is to forbid users to rate themselves by the CHECK (uid <>
author).

What am I doing wrong please?

Regards
Alex

P.S. I apologize if GMail misformats my message... Here is the words_users
table:

words=> \d words_users

  Table "public.words_users"

Column |   Type   |
Modifiers

---+--+---

 uid   | integer  | not null default
nextval('words_users_uid_seq'::regclass)

 created   | timestamp with time zone | not null

 visited   | timestamp with time zone | not null

 ip| inet | not null

 fcm   | character varying(255)   |

 apns  | character varying(255)   |

 vip_until | timestamp with time zone |

 grand_until   | timestamp with time zone |

 banned_until  | timestamp with time zone |

 banned_reason | character varying(255)   |

 win   | integer  | not null

 loss  | integer  | not null

 draw  | integer  | not null

 elo   | integer  | not null

 medals| integer  | not null

 green | integer  | not null

 red   | integer  | not null

 coins | integer  | not null

Indexes:

"words_users_pkey" PRIMARY KEY, btree (uid)

Check constraints:

"words_users_banned_reason_check" CHECK (length(banned_reason::text) >
0)

"words_users_draw_check" CHECK (draw >= 0)

"words_users_elo_check" CHECK (elo >= 0)

"words_users_green_check" CHECK (green >= 0)

"words_users_loss_check" CHECK (loss >= 0)

"words_users_medals_check" CHECK (medals >= 0)

"words_users_red_check" CHECK (red >= 0)

"words_users_win_check" CHECK (win >= 0)

Referenced by:

TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE

TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY
(player1) REFERENCES words_users(uid) ON DELETE CASCADE

TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY
(player2) REFERENCES words_users(uid) ON DELETE CASCADE

TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE

TABLE "words_notes" CONSTRAINT "words_notes_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE

TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE

TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

[Alexander Farber]

And here is the table definition:

words=> \d words_games;
   Table "public.words_games"
  Column  |   Type   | Modifiers
--+--+---
 gid  | integer  | not null default
nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone | not null
 finished | timestamp with time zone |
 player1  | integer  | not null
 player2  | integer  |
 played1  | timestamp with time zone |
 played2  | timestamp with time zone |
 score1   | integer  | not null
 score2   | integer  | not null
 hand1| character varying[]  | not null
 hand2| character varying[]  | not null
 pile | character varying[]  | not null
 letters  | character varying[]  | not null
 values   | integer[]| not null
 bid  | integer  | not null
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
Check constraints:
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_notes" CONSTRAINT "words_notes_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

[Alexander Farber]

Good morning,

it looks that I failed to provide sufficient information in the first mail,
sorry.

Here again my problem - here is my PHP script:

const SQL_GET_BOARD  = '
SELECT
out_bid AS bid,
out_letters AS letters,
out_values AS values
FROMwords_get_board(?)
';

try {
$dbh = pgsqlConnect();
$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values  = $row['values'];
}

} catch (PDOException $ex) {
exit('Database problem: ' . $ex);
}

function pgsqlConnect() {
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_CASE => PDO::CASE_LOWER);
return new PDO(sprintf('pgsql:host=%s;port=%u;dbname=%s', DBHOST,
DBPORT, DBNAME), DBUSER, DBPASS, $options);
}

Here I run it at psql 9.6.2:

words=> SELECT
out_bid AS bid,
out_letters AS letters,
out_values AS values
FROMwords_get_board(1)
;
 bid |


letters


 |


values



-+-
---
---
---
---
-+-
---
---
---
---
--
   1 |
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,К,О,Р,О,Б,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,Р,Е,Я,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,У,
NULL,NULL,П,Э,Р,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,С,П,И,Л,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,О,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,М,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Б,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,В,А,Н,Н,А,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL}} |
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

[Alexander Farber]

Yes, as David notices it is SQL function and not pg/PlSQL (you have
probably misread this).

I wonder what to do with the string in PHP, how to convert it to an
(2-dimensional) array.

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

[Alexander Farber]

Adrian, but the stored function works, I am just not happy that the results
are casted to strings by PHP... and wonder hpw to fix or workaround this.

>
>

[GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

[Alexander Farber]

Good evening!

I am calling this stored function -

CREATE OR REPLACE FUNCTION words_get_board(
in_gid integer
) RETURNS TABLE (
out_bid integer,
out_letters varchar[15][15],
out_values integer[15][15]
) AS
$func$
SELECT
bid,
letters,
values
FROMwords_games
WHERE   gid = in_gid;

$func$ LANGUAGE sql STABLE;

by a PHP script -

$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values  = $row['values'];
}

And then print the variable type -

error_log('letters: ' . gettype($letters));

and it is a "string" (instead of inspected array) with the following
content:

[02-Mar-2017 21:28:33 Europe/Berlin] letters: string
[02-Mar-2017 21:28:33 Europe/Berlin] letters:
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,К,А,Й,Т,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Ь,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Е,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}

How to handle this please?

I was hoping to fetch a PHP array and process it with "foreach" in my
PHP-script.

Thank you
Alex

[GENERAL] Different LEFT JOIN results with and without USING

[Alexander Farber]

Good afternoon,

why do these queries please return different results?

SELECT
s.gid,
TO_CHAR(g.created, 'DD.MM.'),
TO_CHAR(g.finished, 'DD.MM.'),
LENGTH(s.word),
s.score
FROMwords_scores s
LEFT JOIN words_games g ON s.gid = g.gid
AND   s.uid = 1
ORDER BY LENGTH(s.word) DESC, s.mid DESC
LIMIT   20;

vs.

SELECT
s.gid,
TO_CHAR(g.created, 'DD.MM.'),
TO_CHAR(g.finished, 'DD.MM.'),
LENGTH(s.word),
s.score
FROMwords_scores s
LEFT JOIN words_games g USING(gid)
WHERE s.uid = 1
ORDER BY LENGTH(s.word) DESC, s.mid DESC
LIMIT   20;

Returns:

 gid |  to_char   | to_char | length | score
-++-++---
   1 || |  5 | 8
   1 | 21.02.2017 | |  5 |14
   1 | 21.02.2017 | |  4 |11
   1 || |  4 | 7
   1 | 21.02.2017 | |  4 |24
   1 | 21.02.2017 | |  3 | 5
   1 || |  3 |23
   1 || |  3 |14
   1 | 21.02.2017 | |  3 |12
   1 || |  3 | 8
   1 | 21.02.2017 | |  3 | 8
   1 || |  2 | 6
   1 | 21.02.2017 | |  2 | 3
   1 | 21.02.2017 | |  2 | 5
(14 rows)

vs.

 gid |  to_char   | to_char | length | score
-++-++---
   1 | 21.02.2017 | |  5 |14
   1 | 21.02.2017 | |  4 |11
   1 | 21.02.2017 | |  4 |24
   1 | 21.02.2017 | |  3 | 5
   1 | 21.02.2017 | |  3 |12
   1 | 21.02.2017 | |  3 | 8
   1 | 21.02.2017 | |  2 | 3
   1 | 21.02.2017 | |  2 | 5
(8 rows)

Here is my words_scores table:

 mid | gid | uid | word  | score
-+-+-+---+---
   1 |   1 |   1 | ЖИÐ| 8
   2 |   1 |   2 | ЖИÐ| 8
   3 |   1 |   1 | МОЩИ  |24
   4 |   1 |   2 | МОРО  | 7
   5 |   1 |   1 | ПОВОЙ |14
   6 |   1 |   2 | ПРРИЯ | 8
   7 |   1 |   1 | ЯД| 5
   7 |   1 |   1 | ДУÐ|12
   8 |   1 |   2 | ПЭÐ|14
   9 |   1 |   1 | ВОРС  |11
  10 |   1 |   2 | ЛОФ   |23
  11 |   1 |   1 | ОМ| 3
  11 |   1 |   1 | СОМ   | 5
  12 |   1 |   2 | УГ| 6
(14 rows)

# \d words_scores
  Table "public.words_scores"
 Column |   Type| Modifiers
+---+---
 mid| bigint| not null
 gid| integer   | not null
 uid| integer   | not null
 word   | character varying | not null
 score  | integer   | not null
Check constraints:
"words_scores_score_check" CHECK (score >= 0)
"words_scores_word_check" CHECK (word::text ~ '^[Р-Я]{2,}$'::text)
Foreign-key constraints:
"words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
ON DELETE CASCADE
"words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid)
ON DELETE CASCADE
"words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
ON DELETE CASCADE

And here words_games table:

words=> \d words_games;
   Table "public.words_games"
  Column  |   Type   | Modifiers
--+--+---
 gid  | integer  | not null default
nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone | not null
 finished | timestamp with time zone |
 player1  | integer  | not null
 player2  | integer  |
 played1  | timestamp with time zone |
 played2  | timestamp with time zone |
 score1   | integer  | not null
 score2   | integer  | not null
 hand1| character varying[]  | not null
 hand2| character varying[]  | not null
 pile | character varying[]  | not null
 letters  | character varying[]  | not null
 values   | integer[]| not null
 bid  | integer  | not null
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
Check constraints:
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT 

Re: [GENERAL] Custom shuffle function stopped working in 9.6

[Alexander Farber]

At the same time this advice from
http://stackoverflow.com/questions/42179012/how-to-shuffle-array-in-postgresql-9-6-and-also-lower-versions
works, don't know why though:

words=> select array_agg(u order by random())
words-> from unnest(array['a','b','c','d','e','f']) u;
   array_agg
---
 {d,a,f,c,b,e}

Re: [GENERAL] Custom shuffle function stopped working in 9.6

[Alexander Farber]

words=> select version();
 version

--
 PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
 unnest

 a
 b
 c
 d
 e
 f
(6 rows)

Re: [GENERAL] Custom shuffle function stopped working in 9.6

[Alexander Farber]

I think ORDER BY RANDOM() has stopped working in 9.6.2:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
 unnest

 a
 b
 c
 d
 e
 f
(6 rows)

[GENERAL] Custom shuffle function stopped working in 9.6

[Alexander Farber]

Good evening,

after switching to 9.6.2 from 9.5.3 the following custom function has
stopped working:

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
RETURNS varchar[] AS
$func$
SELECT array_agg(letters.x) FROM
(SELECT UNNEST(in_array) x ORDER BY RANDOM()) letters;
$func$ LANGUAGE sql STABLE;

In 9.5.3 it was shuffling characters:

words=> select words_shuffle(ARRAY['a','b','c','d','e','f']);
 words_shuffle
---
 {c,d,b,a,e,f}
(1 row)

But in 9.6.2 it has stopped doing so:
words=> select words_shuffle(ARRAY['a','b','c','d','e','f']);
 words_shuffle
---
 {a,b,c,d,e,f}
(1 row)

Any suggestions for a better shuffling function please?

Regards
Alex

Re: [GENERAL] Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

[Alexander Farber]

Thank you, Rob -

On Fri, Dec 2, 2016 at 11:12 AM, Rob Sargent <robjsarg...@gmail.com> wrote:

>
> > On Dec 2, 2016, at 2:52 AM, Alexander Farber <alexander.far...@gmail.com>
> wrote:
> >
> > CREATE OR REPLACE FUNCTION words_unban_user(
> > in_uid integer)
> > RETURNS integer AS
> > $func$
> > UPDATE words_users SET
> > banned_until = null,
> > banned_reason = null
> > WHERE uid = in_uid
> > RETURNING uid;  -- returns the user to be notified
> >
> > $func$ LANGUAGE sql;
> >
> > words=> SELECT uid FROM words_unban_user(1);
> > ERROR:  column "uid" does not exist
> > LINE 1: SELECT uid FROM words_unban_user(1);
> >^
> >
>
select words_unban_user(1) as uid;
> Your function returns an int not a table.


this has worked well.

However if I rewrite the same function as "language plpgsql" - then
suddenly both ways of calling work:

CREATE OR REPLACE FUNCTION words_unban_user(
in_uid integer,
OUT out_uid integer)
RETURNS integer AS
$func$
BEGIN
UPDATE words_users SET
banned_until = null,
banned_reason = null
WHERE uid = in_uid
RETURNING uid into out_uid;
END
$func$ LANGUAGE plpgsql;

words=> select out_uid AS uid from words_unban_user(1);
 uid
-
   1
(1 row)

words=> select words_unban_user(1) AS uid;
 uid
-
   1
(1 row)

I am curious, why is it so...

Regards
Alex

[GENERAL] Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

[Alexander Farber]

Hello,

why does this fail in PostgreSQL 9.5 please?

Here is my custom SQL function :

CREATE OR REPLACE FUNCTION words_unban_user(
in_uid integer)
RETURNS integer AS
$func$
UPDATE words_users SET
banned_until = null,
banned_reason = null
WHERE uid = in_uid
RETURNING uid;  -- returns the user to be notified

$func$ LANGUAGE sql;

Here is my table:

words=> TABLE words_users;
 uid |   created   |   visited   |
ip | vip_until | grand_until |banned_until |
banned_reason | win | loss | draw | elo  | medals | green | red | coins
-+-+-+---+---+-+-+---+-+--+--+--++---+-+---
   1 | 2016-12-02 10:33:59.0761+01 | 2016-12-02 10:36:36.3521+01 |
127.0.0.1 |   | | 2016-12-09 10:34:09.9151+01 | ban
user 1|   0 |0 |0 | 1500 |  0 | 0 |   0 | 0
(1 row)

And finally here is the failing usage of the function :

words=> SELECT uid FROM words_unban_user(1);
ERROR:  column "uid" does not exist
LINE 1: SELECT uid FROM words_unban_user(1);
   ^

The background is that this is a websockets-based game and of the custom
functions should return a list of user ids to be notified about changes
(like player was banned, opponent has resigned, ...)

In the custom plpgsql functions I use OUT parameters or return table with
RETURN NEXT and it works fine.

But in the above sql function this does not work...

Regards
Alex

Re: [GENERAL] How to optimize SELECT query with multiple CASE statements?

[Alexander Farber]

Ah, thanks - I've got that with JOINing via CASE now...

On Mon, Oct 31, 2016 at 5:50 PM, Geoff Winkless  wrote:

> especially since (as I said in the last paragraph of my email), you
> can remove all of the CASEs except the hand/score ones by just JOINing
> the other tables via a CASE anyway.
>

Re: [GENERAL] How to optimize SELECT query with multiple CASE statements?

[Alexander Farber]

Hi Geoff,

On Mon, Oct 31, 2016 at 4:21 PM, Geoff Winkless  wrote:

>
> You could break the game table apart into game and gameplayer.
>
> That's more "normal" and fits much more nicely, IMO, and you could
> then resolve the CASE by using joins between game and (twice)
> gameplayer:
>
> SELECT ...
> FROM game INNER JOIN gameplayer AS myplayer ON
> game.gameid=myplayer.gameid AND myplayer.uid=in_uid
> INNER JOIN gameplayer AS otherplayer ON game.gameid=otherplayer.gameid
> AND otherplayer.uid!=in_uid
> ...
>
> Then all the other tables simply join to myplayer and otherplayer.
>
>
do you mean, instead of having player1, player2 columns in the words_games
table (as in my current schema
https://gist.github.com/afarber/c40b9fc5447335db7d24 ) - I should move the
player stuff (uid, hand, score) to a separate table and then JOIN them?

Regards
Alex

[GENERAL] How to optimize SELECT query with multiple CASE statements?

[Alexander Farber]

Good afternoon,

is it please posible to optimize the following SQL query with numerous CASE
statements (on same condition!) without switching to PL/pgSQL?

SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int,
EXTRACT(EPOCH FROM g.finished)::int,
g.letters,
g.values,
g.bid,
m.tiles,
m.score,
/* HOW TO OPTIMIZE THE FOLLOWING CASE STATEMENTS? */
CASE WHEN g.player1 = in_uid THEN g.player1 ELSE g.player2 END,
CASE WHEN g.player1 = in_uid THEN g.player2 ELSE g.player1 END,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = in_uid THEN g.played1 ELSE
g.played2 END)::int,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = in_uid THEN g.played2 ELSE
g.played1 END)::int,
CASE WHEN g.player1 = in_uid THEN g.score1 ELSE g.score2 END,
CASE WHEN g.player1 = in_uid THEN g.score2 ELSE g.score1 END,
ARRAY_TO_STRING(CASE WHEN g.player1 = in_uid THEN g.hand1 ELSE
g.hand2 END, ''),
REGEXP_REPLACE(ARRAY_TO_STRING(CASE WHEN g.player1 = in_uid THEN
g.hand2 ELSE g.hand1 END, ''), '.', '?', 'g'),
CASE WHEN g.player1 = in_uid THEN s1.female ELSE s2.female END,
CASE WHEN g.player1 = in_uid THEN s2.female ELSE s1.female END,
CASE WHEN g.player1 = in_uid THEN s1.given ELSE s2.given END,
CASE WHEN g.player1 = in_uid THEN s2.given ELSE s1.given END,
CASE WHEN g.player1 = in_uid THEN s1.photo ELSE s2.photo END,
CASE WHEN g.player1 = in_uid THEN s2.photo ELSE s1.photo END,
CASE WHEN g.player1 = in_uid THEN s1.place ELSE s2.place END,
CASE WHEN g.player1 = in_uid THEN s2.place ELSE s1.place END
FROM words_games g
LEFT JOIN words_moves m ON m.gid = g.gid
-- find move record with the most recent timestamp
AND NOT EXISTS (SELECT 1
FROM words_moves m2 WHERE m2.gid = m.gid
AND m2.played > m.played)
LEFT JOIN words_social s1 ON s1.uid = g.player1
-- find social record with the most recent timestamp
AND NOT EXISTS (SELECT 1
FROM words_social s WHERE s1.uid = s.uid
AND s.stamp > s1.stamp)
LEFT JOIN words_social s2 ON s2.uid = g.player2
-- find social record with the most recent timestamp
AND NOT EXISTS (SELECT 1
FROM words_social s WHERE s2.uid = s.uid
AND s.stamp > s2.stamp)
WHERE in_uid IN (g.player1, g.player2)
AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1
day');

It is a two-player, PostgreSQL-based game and in the statement above I am
using the CASE-statements to ensure that always player1, given1, score1
columns are returned for the player in question.

Here is a bit more context:
http://stackoverflow.com/questions/40342426/how-to-optimize-select-query-with-multiple-case-statements

Thank you
Alex

Re: [GENERAL] WHERE ... IN condition and multiple columns in subquery

[Alexander Farber]

Thank you for the advices and I have also got few answers at

http://stackoverflow.com/questions/40304011/where-in-condition-and-multiple-columns-in-subquery

Regards
Alex

[GENERAL] WHERE ... IN condition and multiple columns in subquery

[Alexander Farber]

Hello,

is it please possible to rewrite the SQL query

SELECT DISTINCT ON (uid)
uid,
female,
given,
photo,
place
FROM words_social
WHERE uid IN (SELECT player1 FROM games)
OR uid IN (SELECT player2 FROM games)
ORDER BY uid, stamp DESC

where first column player1 is fetched in a subquery and then column player2
is fetched from the same table?

I've searched around and it seems that a JOIN should be used here, but can
not figure out exactly how.

Thank you
Alex

PS: Below are my tables and the actual CTE query which works well, but I'd
like to optimize:

CREATE TABLE words_social (
sid varchar(255) NOT NULL,

social integer NOT NULL CHECK (0 <= social AND social <= 6),
female integer NOT NULL CHECK (female = 0 OR female = 1),
given  varchar(255) NOT NULL CHECK (given ~ '\S'),
family varchar(255),
photo  varchar(255) CHECK (photo ~* '^https?://...'),
place  varchar(255),
stamp  integer NOT NULL,  /* only the most recent stamp
is used */

uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);

CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,

created timestamptz NOT NULL,
finished timestamptz,

player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT
NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,

played1 timestamptz,
played2 timestamptz,

mid integer /* REFERENCES words_moves */,

score1 integer NOT NULL CHECK (score1 >= 0),
score2 integer NOT NULL CHECK (score2 >= 0),

hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile  varchar[116] NOT NULL,

letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION words_get_games(in_uid integer)
RETURNS TABLE (
out_gid integer,
out_created integer,
out_finished integer,
out_player1 integer,
out_player2 integer,
out_played1 integer,
out_played2 integer,
out_score1 integer,
out_score2 integer,
out_hand1 text,
out_hand2 text,
out_letters varchar[15][15],
out_values integer[15][15],
out_bid integer,
out_last_tiles jsonb,
out_last_score integer,
out_female1 integer,
out_female2 integer,
out_given1 varchar,
out_given2 varchar,
out_photo1 varchar,
out_photo2 varchar,
out_place1 varchar,
out_place2 varchar
) AS
$func$
WITH games AS (
SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
EXTRACT(EPOCH FROM g.finished)::int AS finished,
g.player1,
g.player2, -- can be NULL
EXTRACT(EPOCH FROM g.played1)::int AS played1,
EXTRACT(EPOCH FROM g.played2)::int AS played2,
g.score1,
g.score2,
ARRAY_TO_STRING(g.hand1, '') AS hand1,
REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.',
'?', 'g') AS hand2,
g.letters,
g.values,
g.bid,
m.tiles AS last_tiles,
m.score AS last_score
FROM words_games g LEFT JOIN words_moves m USING(mid)
WHERE g.player1 = in_uid
AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP -
INTERVAL '1 day')
UNION SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
EXTRACT(EPOCH FROM g.finished)::int AS finished,
g.player2 AS player1,
g.player1 AS player2, -- can not be NULL
EXTRACT(EPOCH FROM g.played2)::int AS played1,
EXTRACT(EPOCH FROM g.played1)::int AS played2,
g.score2 AS score1,
g.score1 AS score2,
ARRAY_TO_STRING(g.hand2, '') AS hand1,
REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.',
'?', 'g') AS hand2,
g.letters,
g.values,
g.bid,
m.tiles AS last_tiles,
m.score AS last_score
FROM words_games g LEFT JOIN 

Re: [GENERAL] Selecting records with highest timestamp - for a join

[Alexander Farber]

Should I use LATERAL JOIN?

On Fri, Oct 21, 2016 at 3:24 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> In PostgreSQL 9.5.3 I keep player infos from various social networks:
>
> # TABLE words_social;
>   sid  | social | female |  given  | family | photo | place |   stamp|
> uid
> ---+++-++---+---
> ++-
>  a |  1 |  0 | Abcde1  ||   |   | 1470237061 |
>   1
>  a |  2 |  0 | Abcde2  ||   |   | 1477053188 |
>   1
>  a |  3 |  0 | Abcde3  ||   |   | 1477053330 |
>   1
>  k |  3 |  0 | Klmnop3 ||   |   | 1477053810 |
>   2
>  k |  4 |  0 | Klmnop4 ||   |   | 1477053857 |
>   2
>  g |  2 |  0 | Ghijk2  ||   |   | 1477053456 |
>   3
>  g |  3 |  0 | Ghijk3  ||   |   | 1477053645 |
>   3
>  g |  4 |  0 | Ghijk4  ||   |   | 1477053670 |
>   3
>  x |  4 |  0 | Xyzok   ||   |   | 1470237393 |
>   4
> (9 rows)
>
> The 1,2,3,4 in column "social" means "Facebook", "Twitter", etc.
>
> For a player I can always select her "most recent" info by:
>
> # select * from words_social s1 WHERE stamp = (SELECT max(stamp) FROM
> words_social s2 WHERE s1.uid = s2.uid);
>   sid  | social | female |  given  | family | photo | place |   stamp|
> uid
> ---+++-++---+---
> ++-
>  a |  3 |  0 | Abcde3  ||   |   | 1477053330 |
>   1
>  k |  4 |  0 | Klmnop4 ||   |   | 1477053857 |
>   2
>  g |  4 |  0 | Ghijk4  ||   |   | 1477053670 |
>   3
>  x |  4 |  0 | Xyzok   ||   |   | 1470237393 |
>   4
> (4 rows)
>
> Then there is another table storing current games (I have omitted some
> columns with game data below):
>
> # select gid, created, finished, player1, player2 from words_games;
>  gid |created| finished | player1 | player2
> -+---+--+-+-
>1 | 2016-10-21 14:51:12.624507+02 |  |   4 |   1
>2 | 2016-10-21 14:51:22.631507+02 |  |   3 |
> (2 rows)
>
> Whenever a user (for example with uid=1) connects to the server, I send
> her the games she is taking part in:
>
> # select gid, created, finished, player1, player2 from words_games where
> player1=1
>union select gid, created, finished, player2, player1 from words_games
> where player2=1;
>  gid |created| finished | player1 | player2
> -+---+--+-+-
>1 | 2016-10-21 14:51:12.624507+02 |  |   4 |   1
> (1 row)
>
> My problem: to the above UNION SELECT statement I need to add user infos
> from words_social table.
>
> (So that I can display user photos and names above the game board)
>
> So I try this with CTE:
>
> # with user_infos AS (select * from words_social s1 WHERE stamp = (SELECT
> max(stamp) FROM words_social s2 WHERE s1.uid = s2.uid))
>  select  g.gid, g.created, g.finished, g.player1, g.player2,
> i.given from words_games g join user_infos i on (g.player1=i.uid) where
> g.player1=1
>  union select g.gid, g.created, g.finished, g.player2, g.player1, i.given
> from words_games g join user_infos i on (g.player2=i.uid) where g.player2=1;
>  gid |created| finished | player1 | player2 | given
> -+---+--+-+-
> +
>1 | 2016-10-21 14:51:12.624507+02 |  |   1 |   4 |
> Abcde3
> (1 row)
>
> This works well (I have advanced since me first asking few days ago), but
> I still have the following problem -
>
> I am worried that the CTE-table user_infos will get very large, once my
> game has many players.
>
>

Re: [GENERAL] Selecting records with highest timestamp - for a join

[Alexander Farber]

Please let me rephrase my question so that it is better understandable -

In PostgreSQL 9.5.3 I keep player infos from various social networks:

# TABLE words_social;
  sid  | social | female |  given  | family | photo | place |   stamp|
uid
---+++-++---+---++-
 a |  1 |  0 | Abcde1  ||   |   | 1470237061 |
  1
 a |  2 |  0 | Abcde2  ||   |   | 1477053188 |
  1
 a |  3 |  0 | Abcde3  ||   |   | 1477053330 |
  1
 k |  3 |  0 | Klmnop3 ||   |   | 1477053810 |
  2
 k |  4 |  0 | Klmnop4 ||   |   | 1477053857 |
  2
 g |  2 |  0 | Ghijk2  ||   |   | 1477053456 |
  3
 g |  3 |  0 | Ghijk3  ||   |   | 1477053645 |
  3
 g |  4 |  0 | Ghijk4  ||   |   | 1477053670 |
  3
 x |  4 |  0 | Xyzok   ||   |   | 1470237393 |
  4
(9 rows)

The 1,2,3,4 in column "social" means "Facebook", "Twitter", etc.

For a player I can always select her "most recent" info by:

# select * from words_social s1 WHERE stamp = (SELECT max(stamp) FROM
words_social s2 WHERE s1.uid = s2.uid);
  sid  | social | female |  given  | family | photo | place |   stamp|
uid
---+++-++---+---++-
 a |  3 |  0 | Abcde3  ||   |   | 1477053330 |
  1
 k |  4 |  0 | Klmnop4 ||   |   | 1477053857 |
  2
 g |  4 |  0 | Ghijk4  ||   |   | 1477053670 |
  3
 x |  4 |  0 | Xyzok   ||   |   | 1470237393 |
  4
(4 rows)

Then there is another table storing current games (I have omitted some
columns with game data below):

# select gid, created, finished, player1, player2 from words_games;
 gid |created| finished | player1 | player2
-+---+--+-+-
   1 | 2016-10-21 14:51:12.624507+02 |  |   4 |   1
   2 | 2016-10-21 14:51:22.631507+02 |  |   3 |
(2 rows)

Whenever a user (for example with uid=1) connects to the server, I send her
the games she is taking part in:

# select gid, created, finished, player1, player2 from words_games where
player1=1
   union select gid, created, finished, player2, player1 from words_games
where player2=1;
 gid |created| finished | player1 | player2
-+---+--+-+-
   1 | 2016-10-21 14:51:12.624507+02 |  |   4 |   1
(1 row)

My problem: to the above UNION SELECT statement I need to add user infos
from words_social table.

(So that I can display user photos and names above the game board)

So I try this with CTE:

# with user_infos AS (select * from words_social s1 WHERE stamp = (SELECT
max(stamp) FROM words_social s2 WHERE s1.uid = s2.uid))
 select  g.gid, g.created, g.finished, g.player1, g.player2,
i.given from words_games g join user_infos i on (g.player1=i.uid) where
g.player1=1
 union select g.gid, g.created, g.finished, g.player2, g.player1, i.given
from words_games g join user_infos i on (g.player2=i.uid) where g.player2=1;
 gid |created| finished | player1 | player2 | given
-+---+--+-+-+
   1 | 2016-10-21 14:51:12.624507+02 |  |   1 |   4 | Abcde3
(1 row)

This works well (I have advanced since me first asking few days ago), but I
still have the following problem -

I am worried that the CTE-table user_infos will get very large, once my
game has many players.

How to rewrite my query, so that I fetch games and users (player1, player2)
for a certain user id (uid) - without making huge intermediate tables?

Thank you
Alex

Re: [GENERAL] Selecting records with highest timestamp - for a join

[Alexander Farber]

Adrian, for both player1 and player2 (because I need to display player
photos above the game board).

SQL join with words_social - yes, but how to take the most recent record
from that table?

For example there are user infos from Google+, Facebook, Twitter - but the
user has used Facebook to login lately and would expect her Facebook-photo
to be seen (the record with the highest "stamp" value).

Regards
Alex


On Wed, Oct 19, 2016 at 8:51 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 10/19/2016 11:35 AM, Alexander Farber wrote:
>
>> In a table I store user info coming from social networks:
>>
>

> CREATE TABLE words_social (
>> sid varchar(255) NOT NULL,
>>
>> social integer NOT NULL CHECK (0 <= social AND social <= 6),
>> female integer NOT NULL CHECK (female = 0 OR female = 1),
>> given  varchar(255) NOT NULL CHECK (given ~ '\S'),
>> family varchar(255),
>> photo  varchar(255) CHECK (photo ~* '^https?://...'),
>> place  varchar(255),
>> stamp  integer NOT NULL,  /* HOW TO USE THE LATEST stamp? */
>>
>> uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
>> PRIMARY KEY(sid, social)
>> );
>>
>> I.e. a user can have several records in the above table, but I always
>> use the most recent one (the one with the highest "stamp") to display
>> that user in my game.
>>
>> Then I use a custom function to retrieve current games info for a
>> particular user:
>>
>> CREATE OR REPLACE FUNCTION words_get_games(in_uid integer)
>> RETURNS TABLE (
>> out_gid integer,
>> out_created integer,
>> out_finished integer,
>> out_player1 integer,
>> out_player2 integer,
>> out_played1 integer,
>> out_played2 integer,
>> out_score1 integer,
>> out_score2 integer,
>> out_hand1 text,
>> out_hand2 text,
>> out_letters varchar[15][15],
>> out_values integer[15][15],
>> out_bid integer,
>> out_last_tiles jsonb,
>> out_last_score integer
>> ) AS
>> $func$
>> SELECT
>> g.gid,
>> EXTRACT(EPOCH FROM g.created)::int,
>> EXTRACT(EPOCH FROM g.finished)::int,
>> g.player1,
>> g.player2, -- can be NULL
>> EXTRACT(EPOCH FROM g.played1)::int,
>> EXTRACT(EPOCH FROM g.played2)::int,
>> g.score1,
>> g.score2,
>> ARRAY_TO_STRING(g.hand1, ''),
>> REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?',
>> 'g'),
>> g.letters,
>> g.values,
>> g.bid,
>> m.tiles,
>> m.score
>> FROM words_games g LEFT JOIN words_moves m USING(mid)
>> WHERE g.player1 = in_uid
>> AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP -
>> INTERVAL '1 day')
>> UNION SELECT
>> g.gid,
>> EXTRACT(EPOCH FROM g.created)::int,
>> EXTRACT(EPOCH FROM g.finished)::int,
>> g.player2,
>> g.player1, -- can not be NULL
>> EXTRACT(EPOCH FROM g.played2)::int,
>> EXTRACT(EPOCH FROM g.played1)::int,
>> g.score2,
>> g.score1,
>> ARRAY_TO_STRING(g.hand2, ''),
>> REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?',
>> 'g'),
>> g.letters,
>> g.values,
>> g.bid,
>> m.tiles,
>> m.score
>> FROM words_games g LEFT JOIN words_moves m USING(mid)
>> WHERE g.player2 = in_uid
>> AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP -
>> INTERVAL '1 day');
>>
>> $func$ LANGUAGE sql;
>>
>> I would like to extend the above custom function, so that user info
>> (given and last names, photo) is returned too.
>>
>> How to approach this problem please, should I use CTE for this?
>>
>
> For player1, player2 or both?
>
> Since you are returning a table from words_get_games() you can experiment
> by joining it's output to words_social.
>
>

Re: [GENERAL] isnull() function in pgAdmin3

[Alexander Farber]

Maybe your are after IS NOT DISTINCT FROM NULL

https://www.postgresql.org/docs/current/static/functions-comparison.html

Re: [GENERAL] Custom SQL function does not like IF-statement

[Alexander Farber]

Sorry, I've just realized you did that already.

And the WITH cte AS part is optional in this case...

Thank you

On Mon, Sep 26, 2016 at 9:12 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> Thank you Vik and others -
>
> On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing <v...@2ndquadrant.fr> wrote:
>
>> On 09/26/2016 08:22 PM, Alexander Farber wrote:
>> >
>> > CREATE OR REPLACE FUNCTION words_get_chat(
>> > in_uid integer,
>> > in_gid integer,
>> > in_msg varchar
>> > ) RETURNS TABLE (
>> > out_my boolean,
>> > out_msg varchar
>> > ) AS
>> > $func$
>> >
>> > IF LENGTH(TRIM(in_msg)) > 0 AND
>> > -- ensure only messages of player1 and player2 are
>> stored
>> > EXISTS (SELECT 1 FROM words_games
>> > WHERE gid = in_gid AND
>> > (player1 = in_uid OR player2 = in_uid)) THEN
>> >
>> > INSERT INTO words_chat (
>> > created,
>> > uid,
>> > gid,
>> > msg
>> > ) VALUES (
>> > CURRENT_TIMESTAMP,
>> > in_uid,
>> > in_gid,
>> > in_msg
>> > );
>> > END IF;
>> >
>> > SELECT
>> > uid = in_uid,
>> > msg
>> > FROM words_chat
>> > WHERE gid = in_gid
>> > ORDER BY created DESC;
>> >
>> > $func$ LANGUAGE sql;
>> >
>> > Unfortunately, PostgreSQL 9.5.4 does not like the syntax:
>> >
>> > ERROR:  syntax error at or near "IF"
>> > LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
>> >  ^
>> >
>> > Please, how to rewrite my queries, so that the SQL function syntax is
>> ok?
>>
>> As others have said, IF is not SQL (at least not the dialect that
>> PostgreSQL understands).  You can rewrite the whole thing like this:
>>
>> WITH cte AS (
>> INSERT INTO words_chat (created, uid, gid, msg)
>> SELECT current_timestamp, in_uid, in_gid, in_msg
>> WHERE length(trim(in_msg)) > 0 AND
>>   EXISTS (SELECT 1 FROM words_games
>>   WHERE gid = in_gid AND
>> in_uid in (player1, player2))
>> )
>> SELECT uid = in_uid, msg
>> FROM words_chat
>> WHERE gid = in_gid
>> ORDER BY created DESC;
>>
>> > Is it maybe possible by adding a WHERE part to the UPDATE statement?
>>
>> Which UPDATE statement would that be?
>>
>
> Oops, I meant the INSERT.
>
> Could the both WHERE conditions be added there?
>
> Regards
> Alex
>

Re: [GENERAL] Custom SQL function does not like IF-statement

[Alexander Farber]

Thank you Vik and others -

On Mon, Sep 26, 2016 at 8:43 PM, Vik Fearing <v...@2ndquadrant.fr> wrote:

> On 09/26/2016 08:22 PM, Alexander Farber wrote:
> >
> > CREATE OR REPLACE FUNCTION words_get_chat(
> > in_uid integer,
> > in_gid integer,
> > in_msg varchar
> > ) RETURNS TABLE (
> > out_my boolean,
> > out_msg varchar
> > ) AS
> > $func$
> >
> > IF LENGTH(TRIM(in_msg)) > 0 AND
> > -- ensure only messages of player1 and player2 are stored
> > EXISTS (SELECT 1 FROM words_games
> > WHERE gid = in_gid AND
> > (player1 = in_uid OR player2 = in_uid)) THEN
> >
> > INSERT INTO words_chat (
> > created,
> > uid,
> > gid,
> > msg
> > ) VALUES (
> > CURRENT_TIMESTAMP,
> > in_uid,
> > in_gid,
> > in_msg
> > );
> > END IF;
> >
> > SELECT
> > uid = in_uid,
> > msg
> > FROM words_chat
> > WHERE gid = in_gid
> > ORDER BY created DESC;
> >
> > $func$ LANGUAGE sql;
> >
> > Unfortunately, PostgreSQL 9.5.4 does not like the syntax:
> >
> > ERROR:  syntax error at or near "IF"
> > LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
> >  ^
> >
> > Please, how to rewrite my queries, so that the SQL function syntax is ok?
>
> As others have said, IF is not SQL (at least not the dialect that
> PostgreSQL understands).  You can rewrite the whole thing like this:
>
> WITH cte AS (
> INSERT INTO words_chat (created, uid, gid, msg)
> SELECT current_timestamp, in_uid, in_gid, in_msg
> WHERE length(trim(in_msg)) > 0 AND
>   EXISTS (SELECT 1 FROM words_games
>   WHERE gid = in_gid AND
> in_uid in (player1, player2))
> )
> SELECT uid = in_uid, msg
> FROM words_chat
> WHERE gid = in_gid
> ORDER BY created DESC;
>
> > Is it maybe possible by adding a WHERE part to the UPDATE statement?
>
> Which UPDATE statement would that be?
>

Oops, I meant the INSERT.

Could the both WHERE conditions be added there?

Regards
Alex

[GENERAL] Custom SQL function does not like IF-statement

[Alexander Farber]

Good evening!

For a 2-player game I am trying to create a custom SQL function, which
stores a new message (if not empty) into words_chat table and then return
all messages from that table for a given game:

CREATE OR REPLACE FUNCTION words_get_chat(
in_uid integer,
in_gid integer,
in_msg varchar
) RETURNS TABLE (
out_my boolean,
out_msg varchar
) AS
$func$

IF LENGTH(TRIM(in_msg)) > 0 AND
-- ensure only messages of player1 and player2 are stored
EXISTS (SELECT 1 FROM words_games
WHERE gid = in_gid AND
(player1 = in_uid OR player2 = in_uid)) THEN

INSERT INTO words_chat (
created,
uid,
gid,
msg
) VALUES (
CURRENT_TIMESTAMP,
in_uid,
in_gid,
in_msg
);
END IF;

SELECT
uid = in_uid,
msg
FROM words_chat
WHERE gid = in_gid
ORDER BY created DESC;

$func$ LANGUAGE sql;

Unfortunately, PostgreSQL 9.5.4 does not like the syntax:

ERROR:  syntax error at or near "IF"
LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
 ^

Please, how to rewrite my queries, so that the SQL function syntax is ok?

Is it maybe possible by adding a WHERE part to the UPDATE statement?

Greetings from Bochum
Alex

Re: [GENERAL] Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

[Alexander Farber]

Thank you Brian and others, but -

On Fri, Sep 9, 2016 at 5:22 PM, Brian Dunavant  wrote:

> I'm making the assumption that you only have one ip/user in words_users.
>
> with lockrow as (
>SELECT g.gid, u.ip
>FROM   words_games g join words_users u
> ON (g.player1 = u.uid)
>   WHERE  g.finished IS NULL
> ANDg.player1 <> in_uid
> ANDg.played1 IS NOT NULL
> ANDg.player2 IS NULL
>LIMIT  1
>FORUPDATE SKIP LOCKED
> ), do_the_update as (
>UPDATE words_games g1
>SETplayer2 = in_uid
>FROM lockrow g2
>WHERE g1.gid = g2.gid
>RETURNING g1.gid, g1.player2
> )
> select m.gid into out_gid, u.ip into out_uip
> from do_the_update m
>   join lockrow u on (gid)
> ;
>
> The general idea being lock the row in the first CTE, update it in the
> second, returning your values, and then query against those in the
> final select to get the ip.  If it didn't update anything, you'll get
> no results.
>

unfortunately, the above query does not seem to ensure, that players with
same ip can not join the same game, which is actually my question...

But thanks for showing the CTE for UPDATE ... RETURNING - that is probably
the way to go for me

Regards
Alex

[GENERAL] Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

[Alexander Farber]

Good afternoon,

I have a question please.

In one table I store user ids and their IP addresses -

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL
);

And in another table I keep 2-player games and timestamps of last moves
(NULL if a player hasn't played yet):

CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
finished timestamptz,

player1 integer REFERENCES words_users(uid) ON DELETE
CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE
CASCADE,

played1 timestamptz,
played2 timestamptz
);

When a user wants to start a new game, I first check if there is maybe a
new game already available - with just 1 player while the other "seat" is
vacant:

UPDATE words_games g1
SETplayer2 = in_uid
FROM (
SELECT gid
FROM   words_games
WHERE  finished IS NULL
ANDplayer1 <> in_uid
ANDplayed1 IS NOT NULL
ANDplayer2 IS NULL
LIMIT  1
FORUPDATE SKIP LOCKED
) g2
WHERE g1.gid = g2.gid
RETURNING g1.gid
INTO  out_gid;

This code works great, but now I am trying to add an (obviously not solving
all cheating/proxy/etc. problems) check, that the IP addresses of both
users must be different.

Fetching "ip" in the internal SELECT statement is trivial with:

UPDATE words_games g1
SETplayer2 = in_uid
FROM (
SELECT g.gid, u.ip
FROM   words_games g, words_users u
WHERE  g.finished IS NULL
ANDg.player1 <> in_uid
ANDg.played1 IS NOT NULL
ANDg.player2 IS NULL
ON (g.player1 = u.uid)
LIMIT  1
FORUPDATE SKIP LOCKED
) g2
WHERE g1.gid = g2.gid
RETURNING g1.gid
INTO  out_gid;

But how to fetch the "ip" column in the surrounding UPDATE statement?

Thank you
Alex

Re: [GENERAL] Passing varchar parameter to INTERVAL

[Alexander Farber]

Thank you, this works well now and comments about IN is appreciated too :-)

Re: [GENERAL] Passing varchar parameter to INTERVAL

[Alexander Farber]

Hello Rob,

On Wed, Sep 7, 2016 at 3:24 PM, rob stone  wrote:

>
> I think the interval values need to be quoted.
> In any event I always use the P or T syntax. 'P1D' means add one day,
>

unfortunately, I can not call INTERVAL 'in_until', that wouldn't work.

Also 'P1D' vs. '1 day' seems to be just a matter of taste.

Finally, to provide more context to my question -

I would prefer to call my custom function as

select words_ban_user(1, '1 day', 'attacking other users');

and not as

select words_ban_user(1, CURRENT_TIMESTAMP + '1 day', 'attacking other
users');

because additionally to the temporary ban I would like to prolong
VIP-periods for paying users (to avoid discussions) - and that would be
more difficult in the latter case (would require more date acrobatics)...

Regards
Alex

[GENERAL] Passing varchar parameter to INTERVAL

[Alexander Farber]

Good afternoon,

when trying to create a custom function to temporary ban a user:

CREATE OR REPLACE FUNCTION words_ban_user(
IN in_uid integer,
IN in_until varchar,-- '1 week' OR '1 month' OR '1 year'
IN in_reason varchar)
RETURNS void AS
$func$
BEGIN

UPDATE words_users SET
banned_until  = CURRENT_TIMESTAMP + INTERVAL in_until,
banned_reason = in_reason,
vip_until = vip_until + INTERVAL in_until,-- for
paying user
grand_until   = grand_until + INTERVAL in_until
WHERE uid = in_uid;

END
$func$ LANGUAGE plpgsql;

in 9.5.4 I unfortunately get the error:

ERROR:  syntax error at or near "in_until"
LINE 69: ...   banned_until = CURRENT_TIMESTAMP + INTERVAL in_until,
   ^
Is there please a better way here?

Thank you
Alex

Re: [GENERAL] Check if there 6 last records of same type without gaps

[Alexander Farber]

Sandor, this has worked, thank you -

On Tue, Sep 6, 2016 at 3:35 PM, Sándor Daku  wrote:

>
> Of course you need the played field you relied on it in the order by
> clause. You can use the result of a select in a from clause of another
> select.
>
> SELECT SUM(skips) from (SELECT CASE WHEN action='skip' THEN 1 ELSE 0 END
> as skips, played
>  FROM words_moves
>  WHERE gid = 3
>  ORDER BY played DESC
>  LIMIT 6) as skipscount;
>
>
words=> SELECT SUM(skips) from (SELECT CASE WHEN action='skip' THEN 1 ELSE
0 END as skips, played
words(>  FROM words_moves
words(>  WHERE gid = 3
words(>  ORDER BY played DESC
words(>  LIMIT 6) as skipscount;
 sum
-
   6
(1 row)

Re: [GENERAL] Check if there 6 last records of same type without gaps

[Alexander Farber]

Also tried the second suggestion:

words=> select count(action='skip') from words_moves where gid=3 group by
played order by played desc limit 6;
 count
---
 1
 1
 1
 1
 1
 1
(6 rows)

Re: [GENERAL] Check if there 6 last records of same type without gaps

[Alexander Farber]

Hello Charles and other, please excuse my stupidity, but -

On Tue, Sep 6, 2016 at 2:52 PM, Charles Clavadetscher <
clavadetsc...@swisspug.org> wrote:

>
> You must group by played, as the message suggests. You are implicitly
> selecting the column through order by, although you don't have it in the
> list of selected columns.
>
>

Here I have 7 "skip" events for gid=3 ("game id") in the table:

words=> select mid, action, gid, uid from words_moves order by played desc;
 mid | action | gid | uid
-++-+-
  15 | skip   |   3 |   1
  14 | skip   |   3 |   2
  13 | skip   |   3 |   1
  12 | skip   |   3 |   2
  11 | skip   |   3 |   1
  10 | skip   |   3 |   2
   9 | skip   |   3 |   1
   6 | play   |   3 |   2
   5 | play   |   4 |   1
   3 | swap   |   3 |   1
   2 | play   |   2 |   1
   1 | play   |   1 |   1
(12 rows)

And then I try the suggestion I got in this mailing list:

words=> SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
words-> FROM words_moves
words-> WHERE gid = 3
words-> GROUP BY played
words-> ORDER BY played DESC
words-> LIMIT 6;
 sum
-
   1
   1
   1
   1
   1
   1
(6 rows)

I guess I need ASC in the last statement, but main problem is how to get
the total sum...

Regards
Alex

Re: [GENERAL] Check if there 6 last records of same type without gaps

[Alexander Farber]

No, I am sorry - for struggling with probably basic questions, but without
GROUP BY I get another error:

org.postgresql.util.PSQLException: ERROR: column "words_moves.played" must
appear in the GROUP BY clause or be used in an aggregate function|
Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL
statement


On Tue, Sep 6, 2016 at 2:30 PM, Sándor Daku <daku.san...@gmail.com> wrote:

> On 6 September 2016 at 14:23, Alexander Farber <alexander.far...@gmail.com
> > wrote:
>
>>
>> On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku <daku.san...@gmail.com>
>> wrote:
>>
>>>
>>> Get the last 6 record and
>>>
>>> 1.  ... action='SKIP' as isskip ... then you can group on and count the
>>> skip moves. If there is 6 of them the game ends.
>>>
>>> 2.  ... sum(case when action='SKIP' then 1 else 0 end) ... If the result
>>> is 6 the game ends
>>>
>>>
>> SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
>> FROM words_moves
>> GROUP BY action
>> ORDER BY played DESC
>> LIMIT 6
>> INTO _sum;
>>
>> RAISE NOTICE '_sum = %', _sum;
>>
>> IF _sum = 6 THEN
>> _finished = CURRENT_TIMESTAMP;
>> END IF;
>>
>> but get the error -
>>
>> org.postgresql.util.PSQLException: ERROR:
>> column "words_moves.played" must appear in the GROUP BY clause or be used
>> in an aggregate function|
>> Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL
>> statement
>>
>

> P.S: Here is the table in question
>>
>>   Table "public.words_moves"
>>  Column |   Type   | Modifiers
>> +--+
>> ---
>>  mid| integer  | not null default
>> nextval('words_moves_mid_seq'::regclass)
>>  action | words_action | not null
>>  gid| integer  | not null
>>  uid| integer  | not null
>>  played | timestamp with time zone | not null
>>  tiles  | jsonb|
>>  score  | integer  |
>> Indexes:
>> "words_moves_pkey" PRIMARY KEY, btree (mid)
>> Check constraints:
>> "words_moves_score_check" CHECK (score > 0)
>> Foreign-key constraints:
>> "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
>> ON DELETE CASCADE
>> "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
>> ON DELETE CASCADE
>> Referenced by:
>> TABLE "words_games" CONSTRAINT "words_mid_fk" FOREIGN KEY (mid)
>> REFERENCES words_moves(mid)
>> TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
>> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>>
>>
> Sry! I wasn't clear enough.
>
> Those are two separate solutions. Pick one!
>
> In this case you don't need the group by
>
> SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
> FROM words_moves
> ORDER BY played DESC
> LIMIT 6
> INTO _sum
>
>

Re: [GENERAL] Check if there 6 last records of same type without gaps

[Alexander Farber]

Thank you, Sandor -

On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku  wrote:

>
> Get the last 6 record and
>
> 1.  ... action='SKIP' as isskip ... then you can group on and count the
> skip moves. If there is 6 of them the game ends.
>
> 2.  ... sum(case when action='SKIP' then 1 else 0 end) ... If the result
> is 6 the game ends
>
>
I am trying

SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
FROM words_moves
GROUP BY action
ORDER BY played DESC
LIMIT 6
INTO _sum;

RAISE NOTICE '_sum = %', _sum;

IF _sum = 6 THEN
_finished = CURRENT_TIMESTAMP;
END IF;

but get the error -

org.postgresql.util.PSQLException: ERROR:
column "words_moves.played" must appear in the GROUP BY clause or be used
in an aggregate function|
Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL
statement

Regards
Alex

P.S: Here is the table in question

  Table "public.words_moves"
 Column |   Type   | Modifiers
+--+---
 mid| integer  | not null default
nextval('words_moves_mid_seq'::regclass)
 action | words_action | not null
 gid| integer  | not null
 uid| integer  | not null
 played | timestamp with time zone | not null
 tiles  | jsonb|
 score  | integer  |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
Check constraints:
"words_moves_score_check" CHECK (score > 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_games" CONSTRAINT "words_mid_fk" FOREIGN KEY (mid)
REFERENCES words_moves(mid)
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE

[GENERAL] Check if there 6 last records of same type without gaps

[Alexander Farber]

Good afternoon,

for a 2-player game I store moves in the following 9.5.4 table:

CREATE TYPE words_action AS ENUM ('play', 'skip', 'swap', 'resign');

CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK (score > 0)
);

ALTER TABLE words_games ADD CONSTRAINT words_mid_fk FOREIGN KEY (mid)
REFERENCES words_moves;

And then I have a custom function for skipping a move (and inserting a
'skip' into the above table):

CREATE OR REPLACE FUNCTION words_skip_game(
IN in_uid integer,
IN in_gid integer,
OUT out_gid integer)
RETURNS integer AS
$func$
DECLARE
_finished timestamptz;
_score1   integer;
_score2   integer;
_uid2 integer;
BEGIN
INSERT INTO words_moves (
action,
gid,
uid,
played
) VALUES (
'skip',
in_gid,
in_uid,
CURRENT_TIMESTAMP
);

Could you please suggest a good way to check that the last 6 moves where
'skip', so that I can end the game when each player skipped her move 3
times in a row?

IF /* there are 6 'skip's - how to do it please? */ THEN
_finished = CURRENT_TIMESTAMP;
END IF;

Below is the rest of my function, thank you for any ideas -

Regards
Alex

UPDATE words_games SET
finished = _finished,
played1  = CURRENT_TIMESTAMP
WHERE
gid  = in_gid AND
player1  = in_uid AND
finished IS NULL AND
-- and it is first player's turn
(played1 IS NULL OR played1 < played2)
RETURNING
gid,
score1,
score2,
player2
INTO
out_gid,
_score1, -- my score
_score2, -- her score
_uid2;

IF NOT FOUND THEN
UPDATE words_games SET
finished = _finished,
played2  = CURRENT_TIMESTAMP
WHERE
gid  = in_gid AND
player2  = in_uid AND
finished IS NULL AND
-- and it is second player's turn
(played2 IS NULL OR played2 < played1);
RETURNING
gid,
score2,  -- swapped
score1,
player1
INTO
out_gid,
_score1,  -- my score
_score2,  -- her score
_uid2;
END IF;

IF NOT FOUND THEN
RAISE EXCEPTION 'Game % not found for user %', in_gid,
in_uid;
END IF;

-- game over, update win/loss/draw stat for both players
IF _finished IS NOT NULL THEN
IF _score1 > _score2 THEN

UPDATE words_users SET
win = win + 1
WHERE uid = in_uid;

UPDATE words_users SET
loss = loss + 1
WHERE uid = _uid2;

ELSIF _score1 < _score2 THEN

UPDATE words_users SET
loss = loss + 1
WHERE uid = in_uid;

UPDATE words_users SET
win = win + 1
WHERE uid = _uid2;
ELSE
UPDATE words_users SET
draw = draw + 1
WHERE uid = in_uid OR uid = _uid2;
END IF;
END IF;
END
$func$ LANGUAGE plpgsql;

Re: [GENERAL] RETURNS TABLE function returns nothingness

[Alexander Farber]

If I'd like to always return exactly 1 row -
why wouldn't just RETURN work?
(That's what I kept trying)

On Fri, Sep 2, 2016 at 7:27 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2016-09-02 19:21 GMT+02:00 Alexander Farber <alexander.far...@gmail.com>:
>
>>  why doesn't this simple test function return a row with 42, NULL values:
>>
>> CREATE OR REPLACE FUNCTION words_merge_users_2(
>> IN in_users jsonb,
>> IN in_ip inet
>> ) RETURNS TABLE (
>> out_uid integer,
>> out_banned varchar
>> ) AS
>> $func$
>> DECLARE
>> _user  jsonb;
>> _uids  integer[];
>> _created   timestamptz;
>> _vip   timestamptz;
>> _grand timestamptz;
>> _banned_until  timestamptz;
>> _banned_reason varchar;
>> BEGIN
>> out_uid := 42;
>> END
>> $func$ LANGUAGE plpgsql;
>>
>>
>> Here I call it at PostgreSQL 9.5.4 prompt in MacOS:
>>
>> # select * from words_merge_users_2('[{"given"
>> :"Abcde","social":1,"auth":"07f0254f5e55413dec7f32c8ef4ee5d3
>> ","stamp":1470237061,"female":0,"sid":"1"}]
>> '::jsonb, '1.1.1.1'::inet);
>>  out_uid | out_banned
>> -+
>> (0 rows)
>>
>>
>> Thank you (I am probably missing something very obvious)
>>
>
> There is not RETURN NEXT statement - so output is zero rows.
>
>

[GENERAL] RETURNS TABLE function returns nothingness

[Alexander Farber]

Good evening,

please help me to figure out, why doesn't this simple test function return
a row with 42, NULL values:

CREATE OR REPLACE FUNCTION words_merge_users_2(
IN in_users jsonb,
IN in_ip inet
) RETURNS TABLE (
out_uid integer,
out_banned varchar
) AS
$func$
DECLARE
_user  jsonb;
_uids  integer[];
_created   timestamptz;
_vip   timestamptz;
_grand timestamptz;
_banned_until  timestamptz;
_banned_reason varchar;
BEGIN
out_uid := 42;
END
$func$ LANGUAGE plpgsql;


Here I call it at PostgreSQL 9.5.4 prompt in MacOS:

# select * from
words_merge_users_2('[{"given":"Abcde","social":1,"auth":"07f0254f5e55413dec7f32c8ef4ee5d3","stamp":1470237061,"female":0,"sid":"1"}]
  '::jsonb, '1.1.1.1'::inet);
 out_uid | out_banned
-+
(0 rows)


Thank you (I am probably missing something very obvious)
Alex

Re: [GENERAL] How to retrieve jsonb column through JDBC

[Alexander Farber]

On Mon, Aug 29, 2016 at 7:50 PM, Merlin Moncure <mmonc...@gmail.com> wrote:

> On Sat, Aug 27, 2016 at 5:39 AM, Alexander Farber
> <alexander.far...@gmail.com> wrote:
> >
> >  List last_tiles = (List) JSON.parse(rs.getString("last_
> tiles"));
> >
> > has not work for me even though the string is:
> >
> >  [{"col": 7, "row": 8, "value": 1, "letter": "A"}, {"col": 7, "row": 7,
> > "value": 2, "letter": "B"}, {"col": 7, "row": 9, "value": 2, "letter":
> "C"}]
> >
> > but it is probably the problem of the Jetty class I am using and not of
> > JDBC...
>
> huh.  what exactly is failing? are you getting a parse exception?
>

http://download.eclipse.org/jetty/9.3.11.v20160721/apidocs/org/eclipse/jetty/util/ajax/JSON.html#parse-java.lang.String-

fails with:

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to
java.util.List

Regards
Alex

Re: [GENERAL] a column definition list is required for functions returning "record"

[Alexander Farber]

Thank you for your comments!
I have switched to SQL function now
(I didn't realize it is better performancewise) -

CREATE OR REPLACE FUNCTION words_select_games(IN in_uid integer)
RETURNS TABLE(
out_gid integer,
out_created integer,
out_player1 integer,
out_player2 integer,
out_played1 integer,
out_played2 integer,
out_score1 integer,
out_score2 integer,
out_hand1 text,
out_hand2 text,
out_letters varchar[15][15],
out_values integer[15][15],
out_bid integer,
out_last_tiles jsonb,
out_last_score integer
) AS
$func$
SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int,
g.player1,
g.player2, -- can be NULL
EXTRACT(EPOCH FROM g.played1)::int,
EXTRACT(EPOCH FROM g.played2)::int,
g.score1,
g.score2,
ARRAY_TO_STRING(g.hand1, ''),
REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g'),
g.letters,
g.values,
g.bid,
m.tiles,
m.score
FROM words_games g LEFT JOIN words_moves m USING(mid)
WHERE g.player1 = in_uid
UNION SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int,
g.player2,
g.player1, -- can not be NULL
EXTRACT(EPOCH FROM g.played2)::int,
EXTRACT(EPOCH FROM g.played1)::int,
g.score2,
g.score1,
ARRAY_TO_STRING(g.hand2, ''),
REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?', 'g'),
g.letters,
g.values,
g.bid,
m.tiles,
m.score
FROM words_games g LEFT JOIN words_moves m USING(mid)
WHERE g.player2 = in_uid;

$func$ LANGUAGE sql;

[GENERAL] How to retrieve jsonb column through JDBC

[Alexander Farber]

Hello,

what do you use to retrieve a jsonb column using JDBC?

I have tried

Object last_tiles = rs.getObject("last_tiles");

and the resulting Object seems to be a String.

Then I have called (using Jetty JSON class here):

 Object last_tiles = JSON.parse(rs.getString("last_tiles"));

And it seems to work ok... but I am still curious, what is the recommended
(or maybe planned for future) way for retrieving jsonb data in Java.

Also

 List last_tiles = (List) JSON.parse(rs.getString("last_tiles"));

has not work for me even though the string is:

 [{"col": 7, "row": 8, "value": 1, "letter": "A"}, {"col": 7, "row": 7,
"value": 2, "letter": "B"}, {"col": 7, "row": 9, "value": 2, "letter": "C"}]

but it is probably the problem of the Jetty class I am using and not of
JDBC...

Regards
Alex

Re: [GENERAL] a column definition list is required for functions returning "record"

[Alexander Farber]

Thank you, I was just wondering if there is a simpler way... but ok

On Fri, Aug 26, 2016 at 5:29 PM, Tom Lane  wrote:

>
> I think you are looking for the RETURNS TABLE syntax.
>

[GENERAL] a column definition list is required for functions returning "record"

[Alexander Farber]

Good afternon,

in 9.5.3 I have defined the following custom function:

CREATE OR REPLACE FUNCTION words_select_games(IN in_uid integer)
RETURNS SETOF RECORD AS
$func$
BEGIN
RETURN QUERY SELECT
g.gid AS gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
g.player1 AS player1,
COALESCE(g.player2, 0) AS player2,
COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played1,
COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS played2,
ARRAY_TO_STRING(g.hand1, '') AS hand1,
REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g')
AS hand2,
g.letters AS letters, /* is a varchar[15][15] */
g.values AS values,/* is an integer[15][15] */
g.bid AS bid,
m.tiles AS last_tiles,
m.score AS last_score
FROM words_games g LEFT JOIN words_moves m USING(mid)
WHERE g.player1 = in_uid
UNION SELECT
g.gid AS gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
g.player2 AS player1,
COALESCE(g.player2, 0) AS player1,
COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS played1,
COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played2,
ARRAY_TO_STRING(g.hand2, '') AS hand1,
REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?', 'g')
AS hand2,
g.letters AS letters,
g.values AS values,
g.bid AS bid,
m.tiles AS last_tiles,
m.score AS last_score
FROM words_games g LEFT JOIN words_moves m USING(mid)
WHERE g.player2 = in_uid;
END
$func$ LANGUAGE plpgsql;

but calling it gives me errors:

words=> select * from words_select_games(1);
ERROR:  a column definition list is required for functions returning
"record"
LINE 1: select * from words_select_games(1);
  ^
words=> select gid, bid from words_select_games(1);

  ERROR:  a column definition list is required for
functions returning "record"
LINE 1: select gid, bid from words_select_games(1);
 ^

I have also unsuccessfully tried

RETURNS SETOF words_games, words_moves AS

and without the comma:

RETURNS SETOF words_games words_moves AS

How would you recommend to fix my declaration problem please?

Regards
Alex

Re: [GENERAL] Forward declaration of table

[Alexander Farber]

Thank you and apologies for the misformated mail - I kept fixing it for
many minutes, but once I hit the "Send" button in Gmail it went south again.

Re: [GENERAL] Forward declaration of table

[Alexander Farber]

Hello again,

I have went the ALTER TABLE route to add my 2 "cyclic" FKs:

https://gist.github.com/afarber/c40b9fc5447335db7d24

And now I have these 2 tables in my 9.5.3 database:

#TABLE words_moves;

 mid | action | gid | uid |played |

  tiles

   | score

-++-+-+---++---

   2 | play   |   1 |   1 | 2016-08-24 20:36:39.888224+02 | [{"col": 7,
"row": 8, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 1,
"letter": "Е"}, {"col": 7, "row": 6, "value": 10, "letter": "Ф"}]

|13

   3 | play   |   2 |   1 | 2016-08-24 21:48:14.448361+02 | [{"col": 7,
"row": 12, "value": 5, "letter": "Ь"}, {"col": 7, "row": 10, "value": 1,
"letter": "Е"}, {"col": 7, "row": 9, "value": 1, "letter": "О"}, {"col": 7,
"row": 11, "value": 10, "letter": "Ш"}, {"col": 7, "row": 8, "value": 2,
"letter": "Р"}, {"col": 7, "row": 7, "value": 2, "letter": "П"}] |31

   4 | play   |   1 |   2 | 2016-08-24 21:50:55.231266+02 | [{"col": 8,
"row": 8, "value": 2, "letter": "Й"}, {"col": 8, "row": 7, "value": 1,
"letter": "А"}, {"col": 8, "row": 6, "value": 2, "letter": "Р"}, {"col": 8,
"row": 5, "value": 2, "letter": "С"}]
|33

(3 rows)

# SELECT
  gid,

EXTRACT(EPOCH FROM
created)::int AS created,
  player1,

COALESCE(player2, 0) AS player2,
  COALESCE(EXTRACT(EPOCH FROM
played1)::int, 0) AS played1,
COALESCE(EXTRACT(EPOCH FROM played2)::int, 0) AS played2,
  ARRAY_TO_STRING(hand1, '') AS hand1,
  ARRAY_TO_STRING(hand2,
'') AS hand2,
bid

FROM words_games
  WHERE player1 = 1

  OR player2 = 1;

 gid |  created   | player1 | player2 |  played1   |  played2   |  hand1  |
 hand2  | bid
-++-+-+++-+-+-
   2 | 1472068074 |   1 |   0 | 1472068094 |  0 | ЫТОВЕРЛ |
ЕНХЯЭАК |   1
   1 | 1472063658 |   1 |   2 | 1472063800 | 1472068255 | ВГЦЕСИУ |
ННДНСВТ |   1
(2 rows)

Then I am trying to perform the LEFT JOIN to return active games and recent
moves for player 1, but for some reason the first two columns are empty:

# SELECT
  m.tiles,

  m.score,

g.gid,

EXTRACT(EPOCH FROM g.created)::int AS created,
g.player1,

  COALESCE(g.player2, 0) AS player2,

COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played1,
  COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS
played2,   ARRAY_TO_STRING(g.hand1, '') AS
hand1,
  ARRAY_TO_STRING(g.hand2, '') AS hand2,
g.bid

FROM words_games g LEFT JOIN words_moves m
ON (g.mid1 = m.mid OR g.mid2 = m.mid)
  WHERE g.player1 = 1

OR g.player2 = 1;

 tiles | score | gid |  created   | player1 | player2 |  played1   |
 played2   |  hand1  |  hand2  | bid
---+---+-++-+-+++-+-+-
   |   |   2 | 1472068074 |   1 |   0 | 1472068094 |
   0 | ЫТОВЕРЛ | ЕНХЯЭАК |   1
   |   |   1 | 1472063658 |   1 |   2 | 1472063800 |
1472068255 | ВГЦЕСИУ | ННДНСВТ |   1
(2 rows)

Why aren't m.tiles and m.score returned please?

Regards
Alex

Re: [GENERAL] Forward declaration of table

[Alexander Farber]

Hi Igor,

On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman <iney...@perceptron.com> wrote:

> mailto:pgsql-general-ow...@postgresql.org] *On Behalf Of *Alexander Farber
>
>
https://gist.github.com/afarber/c40b9fc5447335db7d24
>
>
>
> Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on
> delete cascade).
>
> So, you don’t need mid1, mid2 columns in WORD_GAMES table.
>
> What you need is this column in WORD_MOVES table:
>
>
>
> gid integer REFERENCES WORD_GAMES ON DELETE CASCADE
>
>
>

you are correct, but I need to send most recent move in each game together
with the other game data.

If I don't store the recent moves in mid1, mid2 then I'd have to retrieve
them every time dynamically with

WITH last_moves AS (
  SELECT *
  FROM words_moves wm1
  WHERE
played = (SELECT max(played)
  FROM words_moves wm2
  WHERE wm1.gid = wm2.gid))
SELECT *
FROM words_games wg
  LEFT JOIN last_moves lm
ON (wg.gid = lm.gid)
WHERE
  player1 = 1 OR
  player2 = 1;


Regards
Alex

[GENERAL] Forward declaration of table

[Alexander Farber]

Good evening,

with PostgreSQL 9.5.3 I am using the following table to store 2-player
games:

DROP TABLE IF EXISTS words_games;
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT
NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
score1 integer NOT NULL CHECK(score1 >= 0),
score2 integer NOT NULL CHECK(score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile  varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);


This has worked well for me (when a user connects to the game server, I
send her all games she is taking part in), but then I have decided to add
another table to act as a "logging journal" for player moves:

DROP TABLE IF EXISTS words_moves;

DROP TYPE IF EXISTS words_action;
CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK(score > 0)
);

Also, in the former table words_games I wanted to add references to the
latest moves performed by players:

-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,

The intention is: whenever a player connects to the server, sent her all
active games and status updates on the recent opponent moves.

However the 2 added columns do not work:

ERROR:  relation "words_moves" does not exist
ERROR:  relation "words_games" does not exist
ERROR:  relation "words_moves" does not exist


So my question is if I can somehow "forward declare" the words_moves table?

Here are all tables of my game for more context:
https://gist.github.com/afarber/c40b9fc5447335db7d24

Thank you
Alex

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

[Alexander Farber]

Thank you Craig, this has worked in my custom function too:

BEGIN
PERFORM check_positions(in_uid, in_gid, in_tiles);

CREATE TEMP TABLE _words ON COMMIT DROP AS
SELECT
out_word AS word,
max(out_score) AS score
FROM check_words(in_uid, in_gid, in_tiles)
GROUP BY word, gid;

PL/pgSQL is weird, but fun :-)

I like that I can RAISE EXCEPTION in my custom function and PostgreSQL
rolls everything back.

Regards
Alex

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

[Alexander Farber]

Thank you,  I have rewritten it into:

BEGIN
PERFORM check_positions(in_uid, in_gid, in_tiles);

CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT
DROP;

INSERT INTO _words
SELECT
out_word AS word,
max(out_score) AS score
FROM check_words(in_uid, in_gid, in_tiles)
GROUP BY word, gid;

Regards
Alex

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

[Alexander Farber]

Francisco, thanks, but -

On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte 
wrote:

>
> https://www.postgresql.org/docs/9.5/static/plpgsql-
> statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
>
>
but the custom function I am trying to call (from another function) does
not return one row, but several rows, which I'd like to store into a temp
table:

SELECT
out_word AS word,
max(out_score) AS score
INTO TEMP TABLE _words ON COMMIT DROP
FROM check_words(in_uid, in_gid, in_tiles)
GROUP BY word, gid;

Regards
Alex

[GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

[Alexander Farber]

Good morning,

why does this syntax fail in 9.5.3 please?

I am trying to call 2 custom functions from a third one with:

CREATE OR REPLACE FUNCTION play_game(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb,
OUT out_gid integer)
RETURNS integer AS
$func$
DECLARE

BEGIN
PERFORM check_positions(in_uid, in_gid, in_tiles);

SELECT
out_word AS word,
max(out_score) AS score
INTO TEMP TABLE _words ON COMMIT DROP
FROM check_words(in_uid, in_gid, in_tiles)
GROUP BY word, gid;
...
END
$func$ LANGUAGE plpgsql;

But get the errors (I tried TEMP, TEMPORARY, with and without TABLE):

words=> \i play_game.sql
psql:play_game.sql:166: ERROR:  "temp" is not a known variable
LINE 29: INTO TEMP TABLE _words ON COMMIT DROP
  ^

words=> \i play_game.sql
psql:play_game.sql:166: ERROR:  "temporary" is not a known variable
LINE 29: INTO TEMPORARY TABLE _words ON COMMIT DROP
  ^

The doc https://www.postgresql.org/docs/9.5/static/sql-selectinto.html just
says:
" read the doc
https://www.postgresql.org/docs/9.5/static/sql-createtable.html "

Thank you
Alex

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

[Alexander Farber]

No, actually both variants work for me right now at 9.5.3 on Mac -

On Wed, Aug 10, 2016 at 8:31 PM, Adrian Klaver 
wrote:

>
>> Given what you are doing, RETURN TABLE it will not work there for the
> same reason it does not work in 9.5:
>
> https://www.postgresql.org/docs/9.6/static/plpgsql-control-
> structures.html#PLPGSQL-STATEMENTS-RETURNING
>
> "If you declared the function with output parameters, write just RETURN
> NEXT with no expression. On each execution, the current values of the
> output parameter variable(s) will be saved for eventual return as a row of
> the result. Note that you must declare the function as returning SETOF
> record when there are multiple output parameters, or SETOF sometype when
> there is just one output parameter of type sometype, in order to create a
> set-returning function with output parameters."
>

Either:

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb
OUT out_word varchar,
OUT out_score integer
) RETURNS SETOF RECORD AS
$func$


Or:

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb
) RETURNS TABLE (out_word varchar, out_score integer) AS
$func$


And then I assign values to the variables and call RETURN NEXT:

  out_word := ... ;
  out_score := ... ;
  RETURN NEXT;

Regards
Alex

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

[Alexander Farber]

Both variants have worked for me, thanks

I am using 9.5.3 on CentOS 7 (my "production server" and Win 7, Mac (my
"dev machines") :)

Where does RETURN NEXT EXPRESSION work, on 9.6?

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

[Alexander Farber]

Thank you Adrian and others -

I am trying to replace INSERT into temp table in my custom function by
RETURN NEXT, but get an error:

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb)
RETURNS TABLE(word varchar, score integer) AS
$func$
...

-- INSERT INTO _words(word, score)
-- VALUES (upper(_word), _score);

RETURN NEXT (word, score);


ERROR:  RETURN NEXT cannot have a parameter in function with OUT parameters
LINE 98: RETURN NEXT (word, score);

Regards
Alex

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

[Alexander Farber]

There is still 1 open question -

In my custom function:

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb)
RETURNS TABLE(word varchar, score integer) AS
$func$


I iterate through tiles passed as last argument and store words built by
them at the game board into a temporary table:

CREATE TEMPORARY TABLE _words (word varchar, score integer) ON
COMMIT DROP;

FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
LOOP
  .
IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM
words_nouns */ THEN
INSERT INTO _words(word, score)
VALUES (upper(_word), _score);
END IF;
END LOOP;


And at the end I perform SELECT from the temp table:

RETURN QUERY
SELECT w.word, max(w.score) as score
FROM _words w
GROUP BY w.word;
END
$func$ LANGUAGE plpgsql;


The question is: if it is possible to get rid of the temp table and instead
add records to the implicit table being returned?

Thank you
Alex

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

[Alexander Farber]

Thank you for confirming, I supposed I have to use RETURN QUERY and now it
works.

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

[Alexander Farber]

Thank you -

On Wed, Aug 10, 2016 at 3:18 PM, Charles Clavadetscher <
clavadetsc...@swisspug.org> wrote:

>
> #variable_conflict [use_column|use_variable] before BEGIN:
>
> - http://dba.stackexchange.com/questions/105831/naming-
> conflict-between-function-parameter-and-result-of-join-with-using-clause
> - https://www.postgresql.org/docs/current/static/plpgsql-
> implementation.html
>
>
now I have changed my last statement to:

SELECT w.word, max(w.score) as score
FROM _words w
GROUP BY w.word;

And get the next error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function words_check_words(integer,integer,jsonb) line
131 at SQL statement

However I do not want to discard my results, but return them by my custom
function...

Regards
Alex


> > CREATE OR REPLACE FUNCTION words_check_words(
> > IN in_uid integer,
> > IN in_gid integer,
> > IN in_tiles jsonb)
> > RETURNS TABLE(word varchar, score integer) AS $func$ DECLARE
> > _tilejsonb;
> > _letter  varchar;
> > _letter2 varchar;
> > _value   integer;
> > _value2  integer;
> > _col integer;
> > _col2integer;
> > _row integer;
> > _row2integer;
> > _letters varchar[][];
> > _values  integer[][];
> > _multvarchar[][];
> > _factor  integer;
> > _score   integer;
> > _wordvarchar;
> > BEGIN
> > SELECT
> > g.letters,
> > g.values,
> > b.mult
> > INTO
> > _letters,
> > _values,
> > _mult
> > FROM words_games g, words_boards b WHERE
> > g.gid = in_gid AND
> > g.bid = b.bid  AND
> > g.player1 = in_uid AND
> > -- and it is first player's turn
> > (g.played1 IS NULL OR g.played1 < g.played2);
> >
> > IF NOT FOUND THEN
> > SELECT
> > g.letters,
> > g.values,
> > b.mult
> > INTO
> > _letters,
> > _values,
> > _mult
> > FROM words_games g, words_boards b WHERE
> > g.gid = in_gid AND
> > g.bid = b.bid  AND
> > g.player2 = in_uid AND
> > -- and it is first player's turn
> > (g.played2 IS NULL OR g.played2 < g.played1);
> > END IF;
> >
> > IF NOT FOUND THEN
> > RAISE EXCEPTION 'Game % not found for user %', in_gid,
> in_uid;
> > END IF;
> >
> > CREATE TEMPORARY TABLE _words (word varchar, score integer) ON
> COMMIT DROP;
> >
> > FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
> > LOOP
> > _letter :=  _tile->>'letter';
> > _value  := (_tile->>'value')::int;
> > _col:= (_tile->>'col')::int + 1;
> > _row:= (_tile->>'row')::int + 1;
> >
> > _letters[_col][_row] := _letter;
> > -- multiply the new letter value with premium
> > _values[_col][_row]  := _value *
> words_letter_mult(_mult[_col][_row]);
> >
> > _word   := _letter;
> > _score  := _values[_col][_row];
> > _factor := words_word_mult(_mult[_col][_row]);
> >
> > -- go left and prepend letters
> > FOR _col2 IN REVERSE (_col - 1)..1 LOOP
> > _letter2 := _letters[_col2][_row];
> > EXIT WHEN _letter2 IS NULL;
> > _value2  := _values[_col2][_row];
> > _word:= _letter2 || _word;
> > _score   := _score + _value2;
> > _factor  := _factor *
> words_word_mult(_mult[_col2][_row]);
> > END LOOP;
> >
> > -- go right and append letters
> > FOR _col2 IN (_col + 1)..15 LOOP
> > _letter2 := _letters[_col2][_row];
> > EXIT WHEN _letter2 IS NULL;
> > _value2  := _values[_col2][_row];
> > _word:= _word || _letter2;
> > _score   := _score + _value2;
> > _factor  := _factor *
> words_word_mult(_mult[_col2][_row]);
> > END LOOP;
> >
> > IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM
> words_nouns */ THEN
> > INSERT INTO _words(word, score)
> > VALUES (upper(_word), _score);
> > END IF;
> >
> > _word   := _letter;

[GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

[Alexander Farber]

Good afternoon,

In PostgreSQL 9.5.3 I have created a function (full source code at the
bottom), which goes through an 15 x 15 varchar array and collects words
played horizontally and vertically.

I have declared the function as:

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb)
RETURNS TABLE(word varchar, score integer) AS
$func$

CREATE TEMPORARY TABLE _words (word varchar, score integer) ON
COMMIT DROP;
...
SELECT word, max(score) as score FROM _words GROUP BY word;
END
$func$ LANGUAGE plpgsql;

And when I call it as:

select * from words_check_words(2, 1,
'[{"col":11,"letter":"A","row":8,"value":1},{"col":11,"letter":"B","row":7,"value":3}]'::jsonb);

then it fails with:

ERROR:  column reference "word" is ambiguous
LINE 1: SELECT word, max(score) as score FROM _words GROUP BY word
   ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

As I understand the "word" is used both by the RETURN TYPE and my TEMP
TABLE.

How to resolve this "naming conflict" best or maybe there is some better
way like using some "internal" table implicitly created by the type
declaration?

Thank you
Alex

P.S. Below is my full source code and the full log output -

CREATE OR REPLACE FUNCTION words_check_words(
IN in_uid integer,
IN in_gid integer,
IN in_tiles jsonb)
RETURNS TABLE(word varchar, score integer) AS
$func$
DECLARE
_tilejsonb;
_letter  varchar;
_letter2 varchar;
_value   integer;
_value2  integer;
_col integer;
_col2integer;
_row integer;
_row2integer;
_letters varchar[][];
_values  integer[][];
_multvarchar[][];
_factor  integer;
_score   integer;
_wordvarchar;
BEGIN
SELECT
g.letters,
g.values,
b.mult
INTO
_letters,
_values,
_mult
FROM words_games g, words_boards b WHERE
g.gid = in_gid AND
g.bid = b.bid  AND
g.player1 = in_uid AND
-- and it is first player's turn
(g.played1 IS NULL OR g.played1 < g.played2);

IF NOT FOUND THEN
SELECT
g.letters,
g.values,
b.mult
INTO
_letters,
_values,
_mult
FROM words_games g, words_boards b WHERE
g.gid = in_gid AND
g.bid = b.bid  AND
g.player2 = in_uid AND
-- and it is first player's turn
(g.played2 IS NULL OR g.played2 < g.played1);
END IF;

IF NOT FOUND THEN
RAISE EXCEPTION 'Game % not found for user %', in_gid,
in_uid;
END IF;

CREATE TEMPORARY TABLE _words (word varchar, score integer) ON
COMMIT DROP;

FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
LOOP
_letter :=  _tile->>'letter';
_value  := (_tile->>'value')::int;
_col:= (_tile->>'col')::int + 1;
_row:= (_tile->>'row')::int + 1;

_letters[_col][_row] := _letter;
-- multiply the new letter value with premium
_values[_col][_row]  := _value *
words_letter_mult(_mult[_col][_row]);

_word   := _letter;
_score  := _values[_col][_row];
_factor := words_word_mult(_mult[_col][_row]);

-- go left and prepend letters
FOR _col2 IN REVERSE (_col - 1)..1 LOOP
_letter2 := _letters[_col2][_row];
EXIT WHEN _letter2 IS NULL;
_value2  := _values[_col2][_row];
_word:= _letter2 || _word;
_score   := _score + _value2;
_factor  := _factor *
words_word_mult(_mult[_col2][_row]);
END LOOP;

-- go right and append letters
FOR _col2 IN (_col + 1)..15 LOOP
_letter2 := _letters[_col2][_row];
EXIT WHEN _letter2 IS NULL;
_value2  := _values[_col2][_row];
_word:= _word || _letter2;
_score   := _score + _value2;
_factor  := _factor *
words_word_mult(_mult[_col2][_row]);
END LOOP;

IF LENGTH(_word) > 1 /* AND EXISTS SELECT 1 FROM
words_nouns */ THEN
INSERT INTO _words(word, score)
VALUES (upper(_word), _score);

Re: [GENERAL] select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0

[Alexander Farber]

Thank you, so should I maybe switch to cardinality then?

[GENERAL] select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0

[Alexander Farber]

Good evening,

I wonder, why the following returns NULL and not 0 in 9.5.3?

# select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1);
 array_length
--

(1 row)


# select array_length(array_remove(ARRAY[3,3,3],3), 1);
 array_length
--

(1 row)

In a code for a word game (could be a card game too)
I remove played letter tiles from player's hand using
array_position and finally "compress" it using array_remove:

FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
LOOP
_letter :=  _tile->>'letter';
_value  := (_tile->>'value')::int;
_col:= (_tile->>'col')::int + 1;
_row:= (_tile->>'row')::int + 1;

IF _value = 0 THEN
_pos = ARRAY_POSITION(_hand, '*');
ELSE
_pos = ARRAY_POSITION(_hand, _letter);
END IF;

IF _pos >= 1 THEN
_hand[_pos] := NULL;
ELSE
RAISE EXCEPTION 'Tile % not found in hand %',
_tile, _hand;
END IF;

_letters[_col][_row] := _letter;
_values[_col][_row]  := _value;
END LOOP;

-- remove played tiles from player hand
_hand := ARRAY_REMOVE(_hand, NULL);
-- move up to 7 missing tiles from pile to hand
_hand_len := ARRAY_LENGTH(_hand, 1);   -- OOPS can be NULL
_pile_len := ARRAY_LENGTH(_pile, 1);-- OOPS can be NULL
_move_len := LEAST(7 - _hand_len, _pile_len);
_hand := _hand || _pile[1:_move_len];
_pile := _pile[(1 + _move_len):_pile_len];

I understand that I have to wrap ARRAY_LENGTH calls
with COALESCE, but I am just curious why isn't 0 returned
in the first place...

Regards
Alex