HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlCritical

What are the differences between "Stored Procedures" and "Stored Functions"?

Submitted by: @import:stackexchange-dba··
0
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 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 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.