node.jspostgresqlstored-proceduresstored-functions

How to convert this into postgresql stored procedures in nodejs


I want to change the postgresql queries to stored procedures in Nodejs.

app.post("/add", async(req, res) => {
try {
    const { name, email, hobby } = req.body;
    const newStudent = await pool.query("INSERT INTO student (name, email, hobby) VALUES ($1, $2, $3) RETURNING *", [name, email, hobby]);
    res.json(newStudent.rows);
} catch (err) {
    console.error(err.message);
}

})


Solution

  • -- Create a stored procedure to insert a new student
    
    CREATE OR REPLACE FUNCTION add_student (name VARCHAR, email VARCHAR, hobby VARCHAR)
    RETURNS TABLE(id INT, name VARCHAR, email VARCHAR, hobby VARCHAR)
    AS $$
    BEGIN
        INSERT INTO student (name, email, hobby) VALUES (name, email, hobby) RETURNING *;
        RETURN QUERY SELECT * FROM student;
    END;
    $$ LANGUAGE plpgsql;
    
    -- Execute the stored procedure to add a new student
    
    SELECT add_student('John Doe', 'john@example.com', 'Gardening');