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

Pavel Stehule <pavel.stehule@xxxxxxxxx> · Mon, 8 Mar 2021 20:31:58 +0100

Hi

po 8. 3. 2021 v 19:20 odesílatel Alexander Farber <alexander.farber@xxxxxxxxx> 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