PostgreSQL能否执行两个SQL Server存储过程之间的联接？ [英] Can PostgreSQL perform a join between two SQL Server stored procedures?

问题描述

此问题与较早的问题相关：为什么从存储过程中选择在关系数据库中不支持？

在SQL Server上，不能执行连接到存储过程（请注意：存储过程与函数（SQL Server术语中的表值函数）截然不同 - 使用函数，您知道返回的列设计时间，但使用过程，要返回的特定列在运行时之前是未知的）。

对于SQL Server，确实存在一个 方法，其中可以完成这样的连接： OPENROWSET

所以问题是：

PostgreSQL可以在两个过程之间执行连接，其中列在运行时之前是未知的。



1. 除了使用驻留在外部第三方数据库中的存储过程（可能通过外部数据包装器或其他一些机制）之外，它可以做同样的事吗？

解决方案

1. PostgreSQL可以执行两个程序之间的联接，其中
   列

基本答案很简单，因为 在Postgres（直到并包括Postgres 9.5）中都没有存储过程，只是函数 - 它们提供了几乎但不完全相同的功能，正如你在问题中所阐述的那样。

任何函数都可以在 SELECT 查询的 FROM 像任何其他表一样。

SQL本身需要知道运行时的返回类型。有一个 border-case ：你可以使用 多态类型 。此处的详细说明（最后一章与您最相关）：

• 重构PL / pgSQL函数以返回各种SELECT查询的输出< a>

2. 除非使用驻留在
   中的存储过程外部第三方数据库（可能通过外部数据封装或
   某种其他机制）。

< blockquote>

这也是一个否，基于相同的原则。如果您使用外表，则必须提供清晰定义的回报类型一或其他方式。

您可以能够将由SQL服务器存储过程产生的整行整合到单个制表符分隔的文本表示中，但是（除了容易出错和效率低下）你有一个列，需要元信息定义单个列一个或另一种方式提取列 - catch 22。

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)?

解决方案

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 and including Postgres 9.5), 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.

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):

• Refactor a PL/pgSQL function to return the output of various SELECT queries

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)?

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.

这篇关于PostgreSQL能否执行两个SQL Server存储过程之间的联接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！