您如何扩展“压缩”广告资源？ PostgreSQL排成单独的列？ [英] How can you expand a "condensed" PostgreSQL row into separate columns?

问题描述

我有一个返回表的函数。

I have a function which returns a table.

如果运行 SELECT * FROM some_function（12345）结果是：

object_id | name
----------------
    12345 | "B"

如果运行 SELECT some_function（12345）结果是：

some_function
-------------
(12345,"B")

问题是我想要原始形式（所以我可以访问各个列的值），但 some_function（）的参数来自表中的列。我可以从事物中执行 SELECT some_function（thing_id），但这返回：

The problem is that I want the original form (so that I can access individual column values), but have the argument to some_function() come from a column in a table. I can execute SELECT some_function(thing_id) FROM things but this returns:

some_function
-------------
(12345,"B")
(12346,"C")
(12347,"D")

我要返回的内容是：

object_id | name
----------------
    12345 | "B"
    12346 | "C"
    12347 | "D"

那么一个嵌套或扩展这样的压缩行怎么办？ p>

So how can one "unnest" or "expand" such a condensed row?

推荐答案

9.3及更高版本：横向查询

在PostgreSQL 9.3或更高版本中隐式横向查询：

9.3 and above: lateral query

In PostgreSQL 9.3 or newer use an implicit lateral query:

SELECT f.* FROM things t, some_function(t.thing_id) f;

对于所有新查询，请首选此公式。上面是标准格式。

Prefer this formulation for all new queries. The above is the standard formulation.

它也可以与返回表或返回记录集以及带有 RETURNS RECORD 的带参数的功能。

It also works properly with functions that RETURNS TABLE or RETURNS SETOF RECORD as well as funcs with out-params that RETURNS RECORD.

简写为：

SELECT f.*
FROM things t
CROSS JOIN LATERAL some_function(t.thing_id) f;

9.3之前版本：通配符扩展（小心）

以前的版本，导致对 some_function 进行多重评估，如果 some_function 不起作用c>返回一个集，不要使用此：

Pre-9.3: wildcard expansion (with care)

Prior versions, causes multiple-evaluation of some_function, does not work if some_function returns a set, do not use this:

SELECT (some_function(thing_id)).* FROM things;

以前的版本，避免了 some_function 使用第二个间接层。仅在必须支持相当老的PostgreSQL版本时使用此选项。

Prior versions, avoids multiple-evaluation of some_function using a second layer of indirection. Only use this if you must support quite old PostgreSQL versions.

SELECT (f).*
FROM (
  SELECT some_function(thing_id) f
  FROM things
) sub(f);

演示：

设置：

Demo:

Setup:

CREATE FUNCTION some_function(i IN integer, x OUT integer, y OUT text, z OUT text) RETURNS record LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE 'evaluated with %',i;
  x := i;
  y := i::text;
  z := 'dummy';
  RETURN;
END;
$$;

create table things(thing_id integer);
insert into things(thing_id) values (1),(2),(3);

试运行：

demo=>     SELECT f.* FROM things t, some_function(t.thing_id) f;
NOTICE:  evaluated with 1
NOTICE:  evaluated with 2
NOTICE:  evaluated with 3
 x | y |   z   
---+---+-------
 1 | 1 | dummy
 2 | 2 | dummy
 3 | 3 | dummy
(3 rows)

demo=>     SELECT (some_function(thing_id)).* FROM things;
NOTICE:  evaluated with 1
NOTICE:  evaluated with 1
NOTICE:  evaluated with 1
NOTICE:  evaluated with 2
NOTICE:  evaluated with 2
NOTICE:  evaluated with 2
NOTICE:  evaluated with 3
NOTICE:  evaluated with 3
NOTICE:  evaluated with 3
 x | y |   z   
---+---+-------
 1 | 1 | dummy
 2 | 2 | dummy
 3 | 3 | dummy
(3 rows)

demo=>  SELECT (f).*
    FROM (
      SELECT some_function(thing_id) f
      FROM things
    ) sub(f);
NOTICE:  evaluated with 1
NOTICE:  evaluated with 2
NOTICE:  evaluated with 3
 x | y |   z   
---+---+-------
 1 | 1 | dummy
 2 | 2 | dummy
 3 | 3 | dummy
(3 rows)

这篇关于您如何扩展“压缩”广告资源？ PostgreSQL排成单独的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！