Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?
Tom, you are so eagle eyed - On Mon, Mar 8, 2021 at 8:53 PM Tom Lane wrote: > Alexander Farber writes: > > 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? > > plpgsql initializes them to null by default, I believe, just like ordinary > local variables. > > > org.postgresql.util.PSQLException: ERROR: column "out_uid" does not > exist| > > This isn't related to what your function does internally. > > I think the issue is you renamed out_uid to uid in your SELECT: > > >String SQL_JOIN_GAME = > >"SELECT " + > >"out_uidAS uid," + > > thank you and sorry for my silly mistake
Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?
Alexander Farber writes: > 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? plpgsql initializes them to null by default, I believe, just like ordinary local variables. > org.postgresql.util.PSQLException: ERROR: column "out_uid" does not exist| This isn't related to what your function does internally. I think the issue is you renamed out_uid to uid in your SELECT: >String SQL_JOIN_GAME = >"SELECT " + >"out_uidAS uid," + regards, tom lane
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.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_uidinteger,
> -- the id of the created game
> out_gidinteger,
> out_fcmtext,
> out_apns text,
> out_admtext,
> out_hmstext,
> -- the most recently used social network and the user id
> there
> out_social integer,
> out_sidtext,
> -- 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
> ANDbid = 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_uidAS uid," +
> // the id of the new game is never NULL, but the other
> columns can be NULL
> "out_gidAS gid," +
> "out_fcmAS fcm," +
> "out_apns AS apns, " +
> "out_admAS adm," +
> "out_hmsAS hms," +
> "out_social AS social, " +
> "out_sidAS 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
>
>
Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?
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_uidinteger,
-- the id of the created game
out_gidinteger,
out_fcmtext,
out_apns text,
out_admtext,
out_hmstext,
-- the most recently used social network and the user id
there
out_social integer,
out_sidtext,
-- 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
ANDbid = 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_uidAS uid," +
// the id of the new game is never NULL, but the other
columns can be NULL
"out_gidAS gid," +
"out_fcmAS fcm," +
"out_apns AS apns, " +
"out_admAS adm," +
"out_hmsAS hms," +
"out_social AS social, " +
"out_sidAS 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
