How to create procedure and call/execute in postgresql?

@elise_daugherty 

To create a procedure in PostgreSQL, you can use the CREATE OR REPLACE PROCEDURE statement. Here is an example of how to create a simple procedure that accepts two parameters and returns their sum:

1
2
3
4
5
6
7

CREATE OR REPLACE PROCEDURE add_numbers(a INT, b INT)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN a + b;
END;
$$;

To call or execute the procedure you just created, you can use the CALL statement followed by the procedure name and its parameters. Here is an example of how to call the add_numbers procedure:

1

CALL add_numbers(10, 20);

This will return the sum of 10 and 20, which is 30. You can also store the result of the procedure call in a variable like this:

1
2
3
4
5
6
7
8

DO $$
DECLARE
  result INT;
BEGIN
  result := (SELECT * FROM add_numbers(10, 20));
  RAISE NOTICE 'The result is %', result;
END;
$$;

This will store the result of the add_numbers procedure call in the result variable and then display it using the RAISE NOTICE statement.