Below is the procedure declaration on postgresql.
CREATE OR REPLACE FUNCTION Treasure.RecordReview
(huntId INTEGER, playerName VARCHAR, pTime VARCHAR, pRate VARCHAR, pDesc VARCHAR, OUT status BOOLEAN ) AS $$
DECLARE
status BOOLEAN;
cur_stat_value INTEGER;
BEGIN
INSERT INTO Treasure.review VALUES(huntId, playerName, pTime, pRate, pDesc);
SELECT stat_value INTO cur_stat_value
FROM TreasureHunt.playerstats
WHERE player = $2 AND stat_name = 'review_posts';
IF(cur_stat_value > 0) THEN UPDATE Treasure.playerstats SET stat_value = stat_value + 1 WHERE player = $2 AND stat_name = 'review_posts';
ELSE INSERT INTO Treasure.playerstats VALUES($2, 'review_posts',1);
END IF;
status := true;
END;
$$ LANGUAGE plpgsql;
Below is the php code using PDO to access the procedure.
$current_time = date('Y-m-d G:i:s');
$rating = $_POST['rate'];
$desc = $_POST['desc'];
$status;
$cstmt = $conn->prepare("CALL Treasure.RecordReview(?,?,?,?,?,?)");
$cstmt->bindParam(1, $hunt);
$cstmt->bindParam(2, $user);
$cstmt->bindParam(3, $current_time);
$cstmt->bindParam(4, $rating);
$cstmt->bindParam(5, $desc);
$cstmt->bindParam(6, $status, PDO::PARAM_BOOL|PDO::PARAM_INPUT_OUTPUT);
$cstmt->execute();
When I execute my code it return an error:
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "CALL" LINE 1: CALL Treasure.RecordReview($1,$2,$3,$4,$5,$6) ^
[EDIT] Change "CALL" to "SELECT" as suggested. But another error has come up.
Error Connecting to Database: SQLSTATE[08006] [7] FATAL: remaining connection slots are reserved for non-replication superuser connections
Then
SQLSTATE[42883]: Undefined function: 7 ERROR: function treasure.recordreview(unknown, unknown, unknown, unknown, unknown, unknown) does not exist LINE 1: SELECT Treasure.RecordReview($1,$2,$3,$4,$5,$6); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
