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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?
Date: 2021-03-08 18:20:09
Message-ID: CAADeyWghZVDjeD7zRQGze6eeyrbiqmqJTq4yeSDXUn+HpZjyxw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bysani, Ram 2021-03-08 18:22:11 Question about when PostgreSQL 11.0 was released
Previous Message Geoff Winkless 2021-03-08 16:15:51 updating PGDG 12 devel on centos7 requires llvm5.0