python - 在 Python 上加载/使用 SQL 函数(避免往返)

Question

我正在编写的 Python 代码与 PostgreSQL 数据库建立了一个 psycopg2 连接。我需要根据这个数据库中的数据构建一些报告，所以我有几个 Python procs，它们会定期运行并根据一些表和漂亮的查询创建一个 csv 文件。

我在这里面临的问题是我需要在我的 csv 报告中包含一个列，该列是存储在 PostgreSQL 数据库中的函数的结果。该数据库由另一组人员管理，因此我无法写入。我可以很容易地看到该函数的内容并模拟 Python 上的行为，并获得我需要计算的列值，但在这种情况下，该函数会定期更改，因此持续更新 Python 函数没有意义。

所以我的问题是，每次我的代码连接到数据库时，是否有可能以某种方式将数据库函数加载到 Python 代码中。我可以在数据库本身上实际使用该函数，但想象一下，从 Python 代码对数据库函数进行 900K 次调用来计算一个值，根本无法扩展。

编辑:添加sql函数

CREATE OR REPLACE FUNCTION public.p_start(integer, integer)
 RETURNS numeric
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT CASE WHEN $1 = 0 AND $2 = 0 THEN 0.2760
            WHEN $1 = 0 AND $2 = 1 THEN 0.0684
            WHEN $1 = 0 AND $2 = 2 THEN 0.0277
            WHEN $1 = 0 AND $2 = 3 THEN 0.0189
            WHEN $1 = 0 AND $2 = 4 THEN 0.0038
            WHEN $1 = 0 AND $2 = 5 THEN 0.0098
            WHEN $1 = 1 AND $2 = 1 THEN 0.5501
            WHEN $1 = 1 AND $2 = 2 THEN 0.2264
            WHEN $1 = 1 AND $2 = 3 THEN 0.1203
            WHEN $1 = 1 AND $2 = 4 THEN 0.0804
            WHEN $1 = 1 AND $2 = 5 THEN 0.0839
            ELSE 0.1 END;
$function$

谢谢

Answer 1

这是一个普通的 sql 语言函数。

除非你可以依赖它的结构保持足够稳定，以至于你可以为函数体编写一个简单的解析器，从 pg_proc 的 prosrc 列获取它的源代码，否则我认为你不走运。我不想这样做，因为它非常脆弱。

你应该要求数据库团队将这个映射存储在一个小表而不是一个函数中，然后你可以只查询表内容并缓存它们。他们仍然可以有一个函数返回从表中选择的结果，并且在大多数情况下它会被有效地内联，并且它不会影响与使用该函数的现有应用程序的兼容性。例如。如果信息在表格 probabilities 中，函数将是:

CREATE OR REPLACE FUNCTION public.p_start(integer, integer)
 RETURNS numeric
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT coalesce(
  SELECT probability FROM probabilities WHERE a = $1 and b = $2
0.1)
$function$

否则，如果可能的输入值范围事先已知，您可以生成全功能域的映射并将其缓存在您的应用中，例如:

test=> SELECT startval, endval, p 
       FROM generate_series(0,1) startval 
       cross join generate_series(0,5) endval 
       cross join p_start(startval, endval) p;

 startval | endval |   p    
----------+--------+--------
        0 |      0 | 0.2760
        1 |      0 |    0.1
        0 |      1 | 0.0684
        1 |      1 | 0.5501
        0 |      2 | 0.0277
        1 |      2 | 0.2264
        0 |      3 | 0.0189
        1 |      3 | 0.1203
        0 |      4 | 0.0038
        1 |      4 | 0.0804
        0 |      5 | 0.0098
        1 |      5 | 0.0839
(12 rows)

将其转换为本地查找的 Python 字典是微不足道的。

这仅在函数的域是有限且已知的情况下才有效。

---

顺便说一句，将其定义为IMMUTABLE 然后重新定义它是错误的，除非他们DROP 并重新CREATE 以确保没有任何问题依赖于旧的定义。它应该声明为 STABLE。如果在任何表达式索引中使用该函数，以可以产生不同值的方式重新定义 IMMUTABLE 函数将导致不正确的查询结果。