Good evening,
in PostgreSQL 13.2 I have a custom stored function:
CREATE OR REPLACE FUNCTION words_join_new_game(
in_uid integer,
in_bid integer
) RETURNS table (
-- the player to be notified (sometimes there is no such user)
out_uid integer,
-- the id of the created game
out_gid integer,
out_fcm text,
out_apns text,
out_adm text,
out_hms text,
-- the most recently used social network and the user id there
out_social integer,
out_sid text,
-- the push notification text: the opponent has joined
out_body text
) AS
$func$
....
$func$ LANGUAGE plpgsql;
However there are cases, when I only have the out_gid value, I do not want to return any other values.
My question is: do I have to set the other OUT params explicitly to NULL?
For example here:
-- case 1
SELECT gid
INTO out_gid
FROM words_games
WHERE finished IS NULL
AND bid = in_bid
AND (
(player1 = in_uid AND played1 IS NULL) OR
(player2 = in_uid AND played2 IS NULL)
) LIMIT 1;
IF out_gid IS NOT NULL THEN
-- should I set all the other OUT params to NULL here? <-----------
RETURN;
END IF;
I was expecting to check for out_uid, if it is a positive number in my Java code with:
String SQL_JOIN_GAME =
"SELECT " +
"out_uid AS uid, " +
// the id of the new game is never NULL, but the other columns can be NULL
"out_gid AS gid, " +
"out_fcm AS fcm, " +
"out_apns AS apns, " +
"out_adm AS adm, " +
"out_hms AS hms, " +
"out_social AS social, " +
"out_sid AS sid, " +
"out_body AS body " +
"FROM words_join_new_game(?::int, ?::int)";
int gid = 0;
try (Connection db = DriverManager.getConnection(mDatabaseUrl);
PreparedStatement st = db.prepareStatement(SQL_JOIN_GAME)) {
st.setInt(1, mUid);
st.setInt(2, bid);
ResultSet rs = st.executeQuery();
if (rs.next()) {
// get the id of the new game
gid = rs.getInt(KEY_GID);
// get the id of the opponent
int uid = rs.getInt(KEY_UID);
// send notification to the other player
if (uid > 0) {
Notification n = new Notification(
uid,
gid,
rs.getString(COLUMN_FCM),
rs.getString(COLUMN_APNS),
rs.getString(COLUMN_ADM),
rs.getString(COLUMN_HMS),
rs.getInt(COLUMN_SOCIAL),
rs.getString(COLUMN_SID),
rs.getString(COLUMN_BODY)
);
mServlet.sendNotification(n);
}
}
}
but I am getting the error:
but I am getting the error:
org.postgresql.util.PSQLException: ERROR: column "out_uid" does not exist| Position: 8
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
at de.afarber.WordsListener.handleNewGame(WordsListener.java:216)
at de.afarber.WordsListener.onWebSocketText(WordsListener.java:101)
Thank you
Alex
