Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

Hi

po 8. 3. 2021 v 19:20 odesílatel Alexander Farber <
alexander(dot)farber(at)gmail(dot)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
>
>