Hi po 8. 3. 2021 v 19:20 odesÃlatel Alexander Farber < alexander.far...@gmail.com> napsal:
> 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. > This is same like CREATE OR REPLACE FUNCTION words_join_new_game(IN in_uid int, IN in_bid integer, OUT out_uid int, OUT ....) > 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: > > 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) > are you sure so you have not more functions with the same name? Regards Pavel > > Thank you > Alex > >