函数返回给定表名和列名的表

Question

假设我在两个不同的数据库中有一个名为static的表 and 。

静态表包含房屋的静态信息，如house_size、no_rooms、池、spa。

< code>houses数据库中的< code>static表具有如下列:

pool    spa house_size  sauna   no_rooms
   1    1         25    1       2
   1    0         35    1       3

< code>apartments数据库中的< code>static表具有如下列:

pool    spa house_size  sauna   
   1    1         25    1       
   1    0         35    1       

我想在不引发任何错误的情况下运行下面的查询。目前，我得到错误，因为no_rooms列不存在于apartments.public.static中。

select pool, case when spa = 1 then 1 else 0 end as has_spa,
      sauna, house_size, case when no_rooms > 2 then 1 else 0 end as rooms 
from static;

我尝试的解决方案：

WITH static_new AS (SELECT s.*     
FROM (SELECT 0 AS no_rooms) AS dummy 
LEFT JOIN LATERAL
( SELECT
      pool, spa, sauna, house_size, no_rooms
  FROM static
)  AS s on true)
SELECT * FROM static_new;

它有效，但是当涉及更多列时，此查询会变得混乱。

我在寻找什么：

> < li>

创建一个函数，该函数采用列名和表名，然后执行我在上述查询中执行的联接，并返回一个表。(应该是通用的，适用于参数中给定的列名和表名，并返回一个表。)

还有其他漂亮整洁的解决方案吗？

Answer 1

不要纠结于查询。向包含所有列的两个数据库添加一个视图。在第一个数据库中：

create view v_static as
    select pool, spa house_size, sauna, no_rooms
    from status;

在第二个：

create view v_static as
    select pool, spa house_size, sauna, null as no_rooms
    from status;

然后使用视图而不是基表。

Answer 2

SQL是一种严格类型化的语言，Postgres函数必须声明它们的返回类型。从函数中返回可变数量的列只有通过变通方法才有可能，比如多态类型。参见:

• 如何在PL/pgSQL中按行类型返回表

但是在您的情况下，我们不能使用行类型，因为这因数据库而异。剩下的选择是：返回匿名记录，并为每次调用提供列定义列表。我通常不建议这样做，因为每次调用都提供列定义列表可能很乏味——而且通常毫无意义。但您的可能是少数有意义的用例之一。

尽管如此，您仍然必须知道可能丢失列的数据类型。为了本演示的目的，我将假设integer。否则，您必须额外传递数据类型并相应地构建查询。

CREATE OR REPLACE FUNCTION f_dynamic_select(_tbl regclass
                                          , _cols VARIADIC text[])  -- ①
  RETURNS SETOF record     -- ② anonymous records
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE    -- ③ dynamic SQL
   format(
      'SELECT %s FROM %s'  -- ④ safe against SQLi
    , (
      SELECT string_agg(COALESCE(quote_ident(a.attname)
                              , '0 AS ' || quote_ident(t.col)  -- assuming integer!
                                ), ', ' ORDER  BY t.ord) -- ⑤
      FROM   unnest(_cols) WITH ORDINALITY t(col, ord)   -- ⑤
      LEFT   JOIN pg_attribute a ON a.attrelid = _tbl    -- ⑥
                                AND a.attnum > 0
                                AND NOT a.attisdropped 
                                AND a.attname = t.col
      )
    , _tbl
   );
END
$func$;

打电话（重要！）

SELECT *
FROM   f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int); -- ② column definition list

您的示例调用，使用基于以下列的表达式：

SELECT pool, case when spa = 1 then 1 else 0 end as has_spa  -- ⑦ expressions
     , sauna, house_size
     , case when no_rooms > 2 then 1 else 0 end as rooms 
FROM f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int);

数据库

（1） 该函数将表名作为注册表类型。看：

< li >作为PostgreSQL函数参数的表名

...然后是任意的列名列表——按照有意义的顺序。< code>VARIADIC对此应该很方便。参见:

• 在单个参数中传递多个值

请注意，我们将列名作为区分大小写的单引号字符串传递。不是（双引号）标识符。

（2）这可能是我第一次建议从函数返回匿名记录 - 在[plpgsql]标签上接近1000个答案之后。手册：

如果函数已定义为返回记录数据类型，则必须存在别名或关键字AS，然后是（column_namedata_type[，…]）形式的列定义列表。列定义列表必须与函数返回的列的实际数量和类型相匹配。

③动态SQL手册。

④ 对于SQL注入是安全的，因为表名作为＜code＞regclass＜/code＞传递，并且＜code＞SELECT＜/code＞列表使用＜code＞quote_ident（）＜/code>小心地连接。请参见：

< li >将表名和列名定义为plpgsql函数中的参数？

（5） 使用 WITH ORDINALITY 以保持列的原始顺序。看：

• 具有元素编号的PostgreSQL unest（）

（6） 左联接到系统目录pg_attribute以标识现有列。看：

• 在PostgreSQL中选择具有特定列名的列

（7） 将基于传递的列构建的表达式移动到外部 SELECT。

免责声明：如果有必要，我只会介绍这种复杂程度。也许您毕竟可以在每个数据库中使用简单的视图？