返回类型的简单方法是SETOF表加上其他字段? [英] Easy way to have return type be SETOF table plus additional fields?

问题描述

我正在编写一个PL/pgSQL存储过程，该存储过程将返回一组记录.每个记录都包含现有表的所有字段(将其称为零售商"，它具有两个字段:retailer_key和retailer_name).当然，这可行:

I'm writing a PL/pgSQL stored procedure that will return a set of records; each record contains all the fields of an existing table (call it Retailer, which has two fields: retailer_key and retailer_name). This, of course, works:

CREATE FUNCTION proc_Find_retailers
 (IN p_Store_key INT)
   RETURNS SETOF Retailer
   AS $$ ...`

现在，我想更新sp，以便它在每个返回记录的'end'处返回另外两个字段.我可以做一些事情，例如:

Now I want to update the sp so that it returns an additional two fields to the 'end' of each returned record. I can do something such as:

CREATE FUNCTION proc_Find_store
 (IN p_Store_key INT)
   RETURNS TABLE (
      retailer_key int,
      retailer_name varchar(50),
      addl_field_1 int,
      addl_field_2 double precision)
   AS $$ ...

在现实世界中，我的Retailer表具有50个字段(在我的示例中不是两个)，因此枚举RETURNS TABLE子句中的所有这些字段很繁琐.有没有捷径可走，所以我可以说些什么((我意识到我在这里编造的东西在语法上是非法的，但是我正在这样做是为了给您我想要的东西的味道):

In the real world, my Retailer table has 50 fields (not the two in my example), so enumerating all those fields in the RETURNS TABLE clause is tedious. Is there any shortcut to this, so that I might say something such as (I realize I'm making stuff up here that's syntactically illegal, but I'm doing it to give you the flavor of what I'm looking for):

CREATE FUNCTION proc_Find_store
 (IN p_Store_key INT)
   RETURNS (SETOF Store,
      addl_field_1 int,
      addl_field_2 double precision)
   AS $$ ...

推荐答案

您可以返回整行作为复合类型并添加更多内容:

You could return a whole row as composite type and add some more:

CREATE OR REPLACE FUNCTION f_rowplus()
  RETURNS TABLE (rec demo, add_int int, add_txt text) AS
$func$
SELECT d, 5, 'baz'::text FROM demo d;
$func$  LANGUAGE sql;

但是，当您使用简单调用时:

But then, when you use the simple call:

SELECT * FROM f_rowplus();

您从表demo中获得该行作为单独的复合类型.您必须致电:

You get the row from table demo as separate composite type. You'd have to call:

SELECT (rec).*,  add_int, add_txt FROM f_rowplus();

获取所有单个列.需要括号.

to get all individual columns. Parentheses required.

此外:这仍然只评估函数一次-而像这样的直接调用将对返回类型中的每一列评估一次:

Aside: this still evaluates the function once only - while a direct call like this would evaluate once for every column in the return type:

SELECT (f_rowplus()).*;

详细信息:

• 如何在SQL查询中使用(func()).*语法避免使用多个函数评估?

Postgres在这里有点不一致.如果您使用

Postgres is a bit inconsistent here. If you create a function with

CREATE OR REPLACE FUNCTION f_row2()
  RETURNS TABLE (rec demo) AS
...

然后将其默默转换为单独的列(已分解).仍然没有链接到原始复合类型.您根本无法引用声明的输出列rec，因为该列已被分解类型的列替换.此调用将导致错误消息:

then that is silently converted into individual columns (decomposed). Not link to the original composite type remains. You cannot reference the declared output column rec at all, since that has been replaced with the columns of the decomposed type. This call would result in an error message:

SELECT rec FROM f_row2();

与此处相同:

CREATE OR REPLACE FUNCTION f_row3(OUT rec demo)
  RETURNS SETOF demo AS
...

但是，一旦您添加了任何个OUT列，复合类型就会保留为已声明(未分解)的形式，并且您可以:

However, as soon as you add any more OUT columns, the composite type is preserved as declared (not decomposed) and you can:

SELECT rec FROM f_rowplus();

具有第一个功能.

我创建了一个 SQL小提琴 来演示变体

I created an SQL Fiddle demonstrating the variants.

旁边
使用函数返回FROM列表中的多列(作为表函数)并在SELECT列表中分解时，如下所示:

Aside
When using a function returning multiple columns in the FROM list (as table function) and decomposing in the SELECT list like this:

SELECT (rec).* FROM f_rowplus();

...该函数仅在一次时才被求值-调用和会直接在SELECT列表中分解，如下所示:

... the function is still evaluated once only - while a calling and decomposing in the SELECT list directly like this:

SELECT (f_rowplus()).*;  -- also: different result

...将对返回类型的每一列一次评估 .详细信息:

... would evaluate once for every column in the return type. Details:

• 如何在SQL查询中使用(func()).*语法避免使用多个函数评估?

这篇关于返回类型的简单方法是SETOF表加上其他字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！