尝试使用PL/PgSQL创建动态查询字符串，以便在PostgreSQL 9.6中生成干函数

I have tables that contain the same type of data for every year, but the data gathered varies slightly in that they may not have the same fields.

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

d_abc_2016
d_def_2016
d_ghi_2016
d_jkl_2016

There are certain constants for each table: company_id, employee_id, salary.

每个表都有特定的常量:company_id、employee_id、salary。

However, each one might or might not have these fields that are used to calculate total incentives: bonus, commission, cash_incentives. There are a lot more, but just using these as a examples. All numeric

然而，每个人可能有也可能没有这些字段用于计算总激励:奖金、佣金、现金奖励。还有很多，但只是用它们作为例子。所有的数字

I should note at this point, users only have the ability to run SELECT statements.

我应该注意到，此时用户只能运行SELECT语句。

What I would like to be able to do is this:

我想做的是:

Give the user the ability to call in SELECT and specify their own fields in addition to the call
赋予用户在调用之外调用SELECT和指定自己字段的能力
Pass the table name being used into the function to use in conditional logic to determine how the query string should be constructed for the eventual total_incentives calculation in addition to passing the whole table so a ton of arguments don't have to be passed into the function
将正在使用的表名传递到函数中，以便在条件逻辑中使用，以确定除了传递整个表之外，还应该如何构造查询字符串，以便不需要向函数传递大量的参数

Basically this:

基本上是这样的:

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

So the function being called will calculate total_incentives which is numeric for that employee_id and also show their salary. But the user might choose to add other fields to look at.

因此被调用的函数将计算total_motivation，它是employee_id的数值，也显示他们的薪水。但是用户可能会选择添加其他字段来查看。

For the function, because the fields used in the total_incentives function will vary from table to table, I need to create logic to construct the query string dynamically.

对于函数，因为total_激励函数中使用的字段会因表而异，所以我需要创建逻辑来动态构造查询字符串。

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;

This results in an:

这样的结果是:

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

Since it should be returning a set of numeric values. Change it to the following:

因为它应该返回一组数值。将其更改为以下内容:

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

Get the same error.

得到同样的错误。

Figure well, maybe it is a table it is trying to return.

好吧，也许这是它要返回的一个表。

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

Get the same error.

得到同样的错误。

Really, any variation produces that result. So really not sure how to get this to work.

真的，任何变化都会产生这样的结果。所以我真的不知道该怎么做。

Look at RESULT QUERY, RESULT NEXT, or RESULT QUERY EXECUTE.

查看结果查询、结果下一步或结果查询执行。

https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html

RESULT QUERY won't work because it takes a hard coded query from what I can tell, which won't take in variables.

结果查询不起作用，因为它从我所知道的地方获取了一个硬编码查询，它不包含变量。

RESULT NEXT iterates through each record, which I don't think will be suitable for my needs and seems like it will be really slow... and it takes a hard coded query from what I can tell.

结果接下来遍历每条记录，我觉得不适合我的需要，而且看起来很慢……它从我所知道的信息中获取一个硬编码的查询。

RESULT QUERY EXECUTE sounds promising.

结果查询执行听起来很有希望。

-- 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;

And get:

并获得:

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

It should be returning numeric.

它应该返回数值。

Lastly, I can get this to work, but it won't be DRY. I'd rather not make a bunch of separate functions for each table with duplicative code. Most of the working examples I have seen have the whole query in the function and are called like such:

最后，我可以让它工作，但它不会是干的。我不希望为每个表创建一堆具有重复代码的独立函数。我所见过的大多数工作示例在函数中都有完整的查询，它们的名称是这样的:

SELECT total_incentives(d_abc_2016, 'd_abc_2016');

So any additional columns would have to be specified in the function as:

因此，任何附加列都必须在函数中指定为:

EXECUTE format('SELECT employee_id...)

Given the users will only be able to run SELECT in query this really isn't an option. They need to specify any additional columns they want to see inside a query.

如果用户只能在查询中运行SELECT，这真的不是一个选项。他们需要指定他们希望在查询中看到的任何其他列。

I've posted a similar question but was told it was unclear, so hopefully this lengthier version will more clearly explain what I am trying to do.

我也贴了一个类似的问题，但被告知不清楚，所以希望这个更长的版本能更清楚地解释我在做什么。

1 个解决方案

#1

The column names and tables names should not be used as query parameters passed by USING clause.

列名称和表名不应该用作使用子句传递的查询参数。

Probably lines:

可能线路:

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

should be:

应该是:

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

This case is example why too DRY principle is sometimes problematic. If you write it directly, then your code will be simpler, cleaner and probably shorter.

这个例子就是为什么过于干燥的原则有时会有问题。如果您直接编写它，那么您的代码将更简单、更干净，而且可能更短。

Dynamic SQL is one from last solution - not first. Use dynamic SQL only when your code will be significantly shorter with dynamic sql than without dynamic SQL.

动态SQL是最后一种解决方案，而不是第一种。只有当使用动态SQL的代码比不使用动态SQL的代码要短得多时，才使用动态SQL。

#1