patternsqlCritical
What are the differences between "Stored Procedures" and "Stored Functions"?
Viewed 0 times
storedproceduresthewhataredifferencesbetweenandfunctions
Problem
So a comment to this question mentions, that there is a slight difference between "Stored Procedures" and "Stored Functions" in PostgreSQL.
The comment links to a Wikipedia article but some of this don't seem to apply (e.g. that they can be used in a
The syntax itself seems to be a little bit confusing:
You create a
So what's the difference between these two?
The comment links to a Wikipedia article but some of this don't seem to apply (e.g. that they can be used in a
SELECT statement).The syntax itself seems to be a little bit confusing:
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
[...]
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();You create a
FUNCTION but refer to it as a PROCEDURE.So what's the difference between these two?
Solution
PostgreSQL 11 added stored procedures as a new schema object. You can create a new procedure by using the
Stored procedures differ from functions in the following ways:
-
Stored procedures do not have to return anything, and only return a single row when using
-
You can commit and rollback transactions inside stored procedures, but not in functions.
-
You execute a stored procedure using the
-
Unlike functions, procedures cannot be invoked in DML commands (
CREATE PROCEDURE statement.Stored procedures differ from functions in the following ways:
-
Stored procedures do not have to return anything, and only return a single row when using
INOUT parameters.-
You can commit and rollback transactions inside stored procedures, but not in functions.
-
You execute a stored procedure using the
CALL statement rather than a SELECT statement.-
Unlike functions, procedures cannot be invoked in DML commands (
SELECT, INSERT, UPDATE, DELETE).Context
StackExchange Database Administrators Q#2357, answer score: 71
Revisions (0)
No revisions yet.