I have been trying to access a psql function using the db.func function.
The following is my query
CREATE OR REPLACE FUNCTION play_game(game INT, IN userid INT, IN card INT, OUT uid INT, OUT cid INT, OUT cardfile varchar(255) ) RETURNS SETOF record as
$$
DECLARE
playorder integer;
cardtype varchar(6);
cardcolor varchar(10);
cardvalue integer;
l_cardtype varchar(6);
l_cardcolor varchar(10);
l_cardvalue integer;
cardid integer;
Begin
-- check if this is the user who has to play
SELECT play_order INTO playorder FROM user_game WHERE user_id = userid;
IF playorder = 1 THEN
-- get card attributes
SELECT card_type, card_color, card_value
INTO cardtype, cardcolor, cardvalue
FROM card
WHERE card_id = card;
-- get the attributes of the last card that was played for this game
select c.card_id, c.card_type, c.card_color, c.card_value
INTO cardid, l_cardtype, l_cardcolor, l_cardvalue
FROM play_card pc, card c
WHERE pc.card_id = c.card_id
AND pc.game_id = game
AND pc.id = (SELECT MAX(id) FROM play_card WHERE game_id = game);
Begin
-- check this is the right card to play
IF cardcolor = l_cardcolor AND
cardtype = 'NOM' THEN
INSERT INTO play_card (game_id, user_id, card_id) VALUES (game, userid, card);
-- delete card from draw card
DELETE FROM game_card WHERE game_id = game and card_id = card;
END IF;
commit;
End;
-- move play to the next player
Begin
IF cardtype = 'NOM' THEN
UPDATE user_game
SET play_order = play_order - 1
WHERE game_id = game;
UPDATE user_game
SET play_order = (SELECT count(user_id) from user_game WHERE game_id = game)
WHERE game_id = game;
ELSE
UPDATE user_game
SET play_order = 1 + (SELECT count(user_id) from user_game WHERE game_id = game) - play_order
WHERE game_id = game;
END IF;
End;
End if;
-- get game state and return to Front END
RETURN QUERY SELECT gc.user_id, gc.card_id, c.image_file FROM game_card gc, card c WHERE gc.card_id = c.card_id AND gc.game_id = game ORDER BY user_id;
END;
$$ LANGUAGE plpgsql;
This is my javascript function
db.func("play_game", { gameId, userId, cardId })
.then((results) => {
let card = results;
})
.catch((error) => {
console.log("error in play_game databse function: ", error);
});
I have even tried passing the parameters without the object but I get the error saying “invalid query result mask”
It also gives the following hint:
No function matches the given name and argument types. You might need to add explicit type casts.
Please let me know what to do. Any help is appreciated. Thank you 🙂
