sql - 尝试使用PL/PgSQL创建动态查询字符串以在PostgreSQL 9.6中创建DRY函数

我有一些表包含每年相同类型的数据，但是收集的数据略有不同，因为它们可能没有相同的字段。

d_abc_2016
d_def_2016
d_ghi_2016
d_jkl_2016

每个表都有特定的常数：company_id，employee_id，salary。
但是，每个人可能有或可能没有这些用于计算总激励的字段：bonus，commission，cash_incentives。还有很多，但只是作为一个例子。全部numeric
在这一点上，我应该注意到，用户只能运行SELECT语句。
我想做的是：
让用户能够调用SELECT并在调用之外指定自己的字段
传递要在条件逻辑中使用的函数中使用的表名，以确定除了传递整个表之外，还应如何为最终的total_incentives计算构造查询字符串，以便不必向函数传递大量参数
基本上是这样的：

SELECT employee_id, salary, total_incentives(t, 'd_abc_2016')
FROM d_abc_2016 t;

因此，被调用的函数将计算total_incentives，也就是numeric，并显示它们的employee_id。但是用户可以选择添加其他字段来查看。
对于函数，由于salary函数中使用的字段因表而异，因此我需要创建逻辑来动态构造查询字符串。

CREATE OR REPLACE FUNCTION total_incentives(ANYELEMENT, t text)
    RETURNS numeric AS
$$
DECLARE
    -- table name lower case in case user typed wrong
    tbl          varchar(255) := lower($2;

    -- parse out the table code to use in conditional logic
    tbl_code     varchar(255) := split_part(survey, '_', 2);

    -- the starting point if the query string
    base_calc    varchar(255) := 'salary + '

    -- query string
    query_string varchar(255);

    -- have to declare this to put computation INTO
    total_incentives_calc numeric;
BEGIN
    IF tbl_code = 'abc' THEN
        query_string := base_calc || 'bonus';
    ELSIF tbl_code = 'def' THEN
        query_string := base_calc || 'bonus + commission';
    ELSIF tbl_code = 'ghi' THEN
        -- etc...
    END IF;

    EXECUTE format('SELECT $1 FROM %I', tbl)
    INTO total_incentives_calc
    USING query_string;

    RETURN total_incentives_calc;
END;
$$
LANGUAGE plpgsql;

这将导致：

ERROR:  invalid input syntax for type numeric: "salary + bonus"
CONTEXT:  PL/pgSQL function total_incentives(anyelement,text) line 16 at EXECUTE

因为它应该返回一组total_incentives值。更改为：

CREATE OR REPLACE FUNCTION total_incentives(ANYELEMENT, t text)
    RETURNS SETOF numeric AS
$$
...
    RETURN;

得到同样的错误。
好吧，也许这是一张它想要返回的桌子。

CREATE OR REPLACE FUNCTION total_incentives(ANYELEMENT, t text)
    RETURNS TABLE(tot_inc numeric) AS
$$
...

得到同样的错误。
实际上，任何变化都会产生这种结果。所以真的不知道该怎么做。
查看numeric、RESULT QUERY或RESULT NEXT。
https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html
RESULT QUERY EXECUTE不起作用，因为它需要一个硬编码的查询，从我所知道的，它不会接受变量。
RESULT QUERY遍历每个记录，我认为这些记录不适合我的需要，而且看起来会很慢……从我所知，这需要一个硬编码的查询。
RESULT NEXT听起来很有希望。

-- EXECUTE format('SELECT $1 FROM %I', tbl)
-- INTO total_incentives_calc
-- USING query_string;

RETURN QUERY
    EXECUTE format('SELECT $1 FROM %I', tbl)
    USING query_string;

得到：

ERROR:  structure of query does not match function result type
DETAIL:  Returned type character varying does not match expected type numeric in column 1.
CONTEXT:  PL/pgSQL function total_incentives(anyelement,text) line 20 at RETURN QUERY

它应该返回RESULT QUERY EXECUTE。
最后，我可以让这个工作，但它不会干。我不想用重复的代码为每个表创建一堆单独的函数。我所看到的大多数工作示例都在函数中包含整个查询，调用方式如下：

SELECT total_incentives(d_abc_2016, 'd_abc_2016');

因此，任何其他列都必须在函数中指定为：

EXECUTE format('SELECT employee_id...)

考虑到用户只能在查询中运行numeric，这确实不是一个选项。它们需要指定希望在查询中看到的任何其他列。
我发布了一个类似的问题，但被告知这是不清楚的，所以希望这个较长的版本将更清楚地解释我正在做什么。

最佳答案

列名和表名不应用作USING子句传递的查询参数。
可能行：

RETURN QUERY
    EXECUTE format('SELECT $1 FROM %I', tbl)
    USING query_string;

应该是：

RETURN QUERY
    EXECUTE format('SELECT %s FROM %I', query_string, tbl);

这个例子说明了为什么有时过于枯燥的原则是有问题的。如果您直接编写，那么您的代码将更简单、更干净，而且可能更短。
动态sql是最后一个解决方案，而不是第一个。只有当使用动态sql的代码比不使用动态sql的代码短得多时，才使用动态sql。

关于sql - 尝试使用PL/PgSQL创建动态查询字符串以在PostgreSQL 9.6中创建DRY函数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46247549/