sql - Why is selecting from stored procedure not supported in relational databases?

Question

It is well known that you cannot perform a SELECT from a stored procedure in either Oracle or SQL Server (and presumably most other mainstream RDBMS products).

Generally speaking, there are several obvious "issues" with selecting from a stored procedure, just two that come to mind:

a) The columns resulting from a stored procedure are indeterminate (not known until runtime)

b) Because of the indeterminate nature of stored procedures, there would be issues with building database statistics and formulating efficient query plans

As this functionality is frequently desired by users, a number of workaround hacks have been developed over time:

http://www.club-oracle.com/threads/select-from-stored-procedure-results.3147/

http://www.sommarskog.se/share_data.html

SQL Server in particular has the function OPENROWSET that allows you to join to or select from almost anything: https://msdn.microsoft.com/en-us/library/ms190312.aspx

....however, DBA's tend to be very reluctant to enable this for security reasons.

So to my question: while there are some obvious issues or performance considerations involved in allowing joins to or selects from stored procedures, is there some fundamental underlying technical reason why this capability is not supported in RDBMS platforms?

EDIT:
A bit more clarification from the initial feedback....yes, you can return a resultset from a stored procedure, and yes, you can use a (table valued) function rather than a stored procedure if you want to join to (or select from) the resultset - however, this is not the same thing as JoiningTo / SelectingFrom a stored procedure. If you are working in a database that you have complete control over, then you have the option of using a TVF. However, it is extremely common that you find yourself working in a 3rd party database and you are forced to call pre-existing stored procedures; or, often times you would like to join to system stored procedures such as: sp_execute_external_script (https://msdn.microsoft.com/en-us/library/mt604368.aspx).

EDIT 2:
On the question of whether PostgreSQL can do this, the answer is also no: Can PostgreSQL perform a join between two SQL Server stored procedures?

Answer 1

TL;DR: you can select from (table-valued) functions, or from any sort of function in PostgreSQL. But not from stored procedures.

Here's an "intuitive", somewhat database-agnostic explanation, for I believe that SQL and its many dialects is too much of an organically grown language / concept for there to be a fundamental, "scientific" explanation for this.

Procedures vs. Functions, historically

I don't really see the point of selecting from stored procedures, but I'm biased by years of experience and accepting the status quo, and I certainly see how the distinction between procedures and functions can be confusing and how one would wish them to be more versatile and powerful. Specifically in SQL Server, Sybase or MySQL, procedures can return an arbitrary number of result sets / update counts, although this is not the same as a function that returns a well-defined type.

Think of procedures as imperative routines (with side effects) and of functions as pure routines without side-effects. A SELECT statement itself is also "pure" without side-effects (apart from potential locking effects), so it makes sense to think of functions as the only types of routines that can be used in a SELECT statement.

In fact, think of functions as being routines with strong constraints on behaviour, whereas procedures are allowed to execute arbitrary programs.

4GL vs. 3GL languages

Another way to look at this is from the perspective of SQL being a 4th generation programming language (4GL). A 4GL can only work reasonably if it is restricted heavily in what it can do. Common Table Expressions made SQL turing-complete, yes, but the declarative nature of SQL still prevents its being a general-purpose language from a practical, every day perspective.

Stored procedures are a way to circumvent this limitation. Sometimes, you want to be turing complete and practical. So, stored procedures resort to being imperative, having side-effects, being transactional, etc.

Stored functions are a clever way to introduce some 3GL / procedural language features into the purer 4GL world at the price of forbidding side-effects inside of them (unless you want to open pandora's box and have completely unpredictable SELECT statements).

The fact that some databases allow for their stored procedures to return arbitrary numbers of result sets / cursors is a trait of their allowing arbitrary behaviour, including side-effects. In principle, nothing I said would prevent this particular behaviour also in stored functions, but it would be very unpractical and hard to manage if they were allowed to do so within the context of SQL, the 4GL language.

Thus:

• Procedures can call procedures, any function and SQL
• "Pure" functions can call "pure" functions and SQL
• SQL can call "pure" functions and SQL

But:

• "Pure" functions calling procedures become "impure" functions (like procedures)

And:

• SQL cannot call procedures
• SQL cannot call "impure" functions

Examples of "pure" table-valued functions:

Here are some examples of using table-valued, "pure" functions:

Oracle

CREATE TYPE numbers AS TABLE OF number(10);
/

CREATE OR REPLACE FUNCTION my_function (a number, b number)
RETURN numbers
IS
BEGIN
    return numbers(a, b);
END my_function;
/

And then:

SELECT * FROM TABLE (my_function(1, 2))

SQL Server

CREATE FUNCTION my_function(@v1 INTEGER, @v2 INTEGER)
RETURNS @out_table TABLE (
    column_value INTEGER
)
AS
BEGIN
    INSERT @out_table
    VALUES (@v1), (@v2)
    RETURN
END

And then

SELECT * FROM my_function(1, 2)

PostgreSQL

Let me have a word on PostgreSQL.

PostgreSQL is awesome and thus an exception. It is also weird and probably 50% of its features shouldn't be used in production. It only supports "functions", not "procedures", but those functions can act as anything. Check out the following:

CREATE OR REPLACE FUNCTION wow ()
RETURNS SETOF INT
AS $$
BEGIN
    CREATE TABLE boom (i INT);

    RETURN QUERY
    INSERT INTO boom VALUES (1)
    RETURNING *;
END;
$$ LANGUAGE plpgsql;

Side-effects:

• A table is created
• A record is inserted

Yet:

SELECT * FROM wow();

Yields

wow
---
1