1

This question is related to an earlier one: Why is selecting from stored procedure not supported in relational databases?

On SQL Server you cannot perform a join to (or a select from) a stored procedure (please note: a stored procedure is distinctly different from a function (table-valued function in SQL Server terminology) - with a function, you know the columns being returned at design time, but with a procedure, the specific columns to be returned are not known until runtime).

With SQL Server, there does exist a "generally not allowed by DBA's" method where one can accomplish such a join: OPENROWSET

So the questions are:

  1. Can PostgreSQL perform a join between two procedures where the columns are not known until runtime?

  2. Can it do the same, except using stored procedures that reside in an external 3rd party database (perhaps via foreign data wrappers or some other mechanism)?

Community
  • 1
  • 1
tbone
  • 5,715
  • 20
  • 87
  • 134
  • why is this tagged with Oracle? – Bryan Dellinger Nov 24 '15 at 14:25
  • 1
    What are you trying to do and why do you want to join *stored procedures*? If you can't write a function or a view, it's a very strong possibility that you are doing something wrong – Panagiotis Kanavos Nov 24 '15 at 14:32
  • DBAs and senior developers hate `OPENROWSET` because it means no optimization or indexing is possible and all data has to be processed in a cursor-like (ie slow) loop. – Panagiotis Kanavos Nov 24 '15 at 14:36
  • "What are you trying to do" - Many times you are not writing code from scratch, you have to consume SP's written by third parties, or system procedures (that probably should have been written as functions but weren't.) – tbone Nov 24 '15 at 16:13

1 Answers1

5
  1. Can PostgreSQL perform a join between two ~procedures where the columns are not known until runtime?

The basic answer is simple because there currently are no stored procedures in Postgres (up to Postgres 10), just functions - which provide almost but not quite the same functionality, as you have laid out in the question.

And any function can be used in the FROM clause of a SELECT query like any other table.

Update:
SQL procedures ("stored procedures") are introduced with Postgres 11.
The manual for CREATE PROCEDURE.

SQL itself demands to know the return type at runtime. There is a border-case: you can declare the return type with the function call using polymorphic types. Detailed instructions here (the last chapter being most relevant to you):

  1. Can it do the same, except using stored procedures that reside in an external 3rd party database (perhaps via foreign data wrappers or some other mechanism)?

That's a NO, too, based on the same principle. If you use foreign tables, you must provide a clearly defined return type one or the other way.

You might be able to lump the whole row resulting from an SQL-Server-stored-procedure into a single tab-delimited text representation, but then (besides being error-prone and inefficient) you have a single column and need the meta information defining individual columns one or the other way to extract columns - catch 22.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 4
    Even in SQL Server, if joins are needed, one *shouldn't* be using stored procedures but views or functions. There's a reason DBAs don't like OPENROWSET - it means the results have to be dumped somewhere and queried without benefit of indexes – Panagiotis Kanavos Nov 24 '15 at 14:35