33

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" 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 seem 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?

41

Officially, PostgreSQL only has "functions". Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning. Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc. That's a holdover from PostQUEL. Any features that some people (possibly with experience in different database systems) might associate with procedures, such as their relevance to preventing SQL injections or the use of output parameters, also apply to functions as they exist in PostgreSQL.

Now, when people in the PostgreSQL community talk about "stored procedures" or "real stored procedures", however, they often mean a hypothetical feature of a function-like object that can start and stop transactions in its body, something that current functions cannot do. The use of the term "stored procedure" in this context appears to be by analogy to other database products. See this mailing list thread for a vague idea.

In practice, however, this distinction of function versus procedure in terms of their transaction-controlling capabilities is not universally accepted, and certainly many programmers without database bias will take a Pascal-like interpretation of a procedure as a function without return value. (The SQL standard appears to take a middle ground, in that a procedure by default has a different transaction behavior than a function, but this can be adjusted per object.) So in any case, and especially when looking at questions on Stack Exchange with a very mixed audience, you should avoid assuming too much and use clearer terms or define the properties that you expect.

13

In terms of DDL, Postgres does not have procedure objects, only functions. Postgres functions can return value(s) or void so they take on the roles of both functions and procedures in other RDBMSs. The word 'procedure' in the create trigger refers to a function.

In terms of the Postgres documentation, 'procedure' is also a synonym for the database object called a function, eg: "A trigger procedure is created with the CREATE FUNCTION command".

Trigger 'procedures' do have particular rules: they must be declared as a function with no arguments and a return type of trigger. Example here.

8

The terms "stored procedure" and "stored function" are used interchangeably in PostgreSQL and are generally taken to mean the same thing. Other databases may differentiate between a procedure and function (much like how VB differentiates between subroutines and functions).

As long as a function in PostgreSQL returns something that resembles a table, you can use the output of that function as if it were a standard table. The CREATE TRIGGER syntax is a bit confusing, but I suspect it may have been in place before the ANSI standard was finalized. I only have a copy of SQL:2003, so I can't do much more than speculate why the nomenclature is weird.

TL;DR version: with PostgreSQL "procedure" is equivalent to "function".

6

In MSSQL, a stored procedure is a pre-compiled set of sql commands.
A stored procedure:

 - can have many input and output paramters
 - can be used to modify database tables/structures/data
 - are not normally used inside insert/update/delete/select statements
User defined functions come in several flavors. Depending on the type of function written, functions:
  - can have multiple input parameters, but only return a single value (i.e. string concatenation)
  - can accept a set as an input, return a single value (i.e. dbo.FindLargestPig(ListOfPigs)  )
  - return a table (i.e. select * from dbo.ExplodeString("this is a list of words")  )
  - can be used in select/insert/update/delete statements
  - CANNOT be used to modify database tables/structures/data

5

The short answer is that a function returns a value, but a procedure doesn't.

That distinction was present in Persistent Stored Modules (SQL/PSM), which was proposed for SQL 1992. I don't know whether SQL/PSM ever made it into the standards.

  • by 2003 I believe – xenoterracide Apr 25 '11 at 11:50
  • That sounds remarkably like the syntax for VB. A function returns a value and a procedure doesn't (so if you had a return value in a function it would blow up on the compiler) – jcolebrand Apr 25 '11 at 21:28
  • @jcolebrand Actually, the names are more apparent in Pascal. A procedure does not return a result, while a function does. For historical reasons, VBA uses the language of FORTRAN, which calls (called?) them SUBROUTINE & FUNCTION. Modern C-type languages just have functions, but typed languages have the option of void functions which are procedures by another name. A popular trend is to just use normal functions for everything and return something which can be ignored if you want to. – Manngo Feb 17 '17 at 23:38
2

Comparing the accepted answer from abstract conceptual level, I understand the difference from functionality and input/output perspective. Below I used sp and f to represent stored procedure and function, respectively.

  1. Use in an expression: sp cannot be used in an expression while function can, which means you can use your returned value from a f inside other statements, like

    select * 
    from table 
    where col_a < (select col_A from f())
    
  2. return a value: sp does not automatically return a value unless you specify refcursor return type, open and return a cursor; f returns the result in the last statement where a 'return' clause is embedded, like a select clause.

  3. return single/multiple result sets: here result sets refer a list of results that may differ in format, like set of single integer, text array and two tables. sp can return multiple sets as long as you specify refcursor return type, open and return a cursor. However, f can only return one type of set.

Usually, stored procedures are used to modify the database data or structure where return value is not needed, like delete, update, drop, etc; or situations where multiple result sets are required. Function, on the other hand, is mostly chosen for plain queries.

For more details regarding my explanation, please refer to this link: Stored Procedures and Functions in PostgreSQL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.