我在 Postgres 15 数据库中有两个表:
CREATE TABLE apps (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamp with time zone NOT NULL DEFAULT now(),
updated_at timestamp with time zone NOT NULL DEFAULT now(),
production_version_id bigint REFERENCES app_versions(id),
development_version_id bigint REFERENCES app_versions(id),
last_published timestamp with time zone
);
和
CREATE TABLE app_versions (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamp with time zone NOT NULL DEFAULT now(),
app_id bigint NOT NULL REFERENCES apps(id),
updated_at timestamp with time zone NOT NULL DEFAULT now(),
published_date timestamp with time zone
);
我创建了一个函数,旨在返回以下内容的发布日期app
:
CREATE OR REPLACE FUNCTION generate_app_published_date (app_id apps.id%TYPE)
RETURNS app_versions.published_date%TYPE
LANGUAGE SQL
SECURITY DEFINER
SET search_path = public VOLATILE
AS $$
SELECT
app_versions.published_date
FROM
app_versions
LEFT JOIN apps ON apps.production_version_id = app_versions.id
WHERE
apps.id = app_id;
$$;
但是,无论我传入的值如何,此函数都会返回相同的结果:
SELECT generate_app_published_date (1);
=> 2023-04-01 12:00:14.789647+00
SELECT generate_app_published_date (2);
=> 2023-04-01 12:00:14.789647+00
SELECT generate_app_published_date (8675309); // not an actual app id
=> 2023-04-01 12:00:14.789647+00
如果我运行等效查询:
SELECT app_versions.published_date FROM app_versions LEFT JOIN apps ON apps.production_version_id = app_versions.id WHERE apps.id = 1;
=> 2023-04-01 12:00:14.789647+00
SELECT app_versions.published_date FROM app_versions LEFT JOIN apps ON apps.production_version_id = app_versions.id WHERE apps.id = 1;
=> 2023-04-01 12:11:00+00
SELECT app_versions.published_date FROM app_versions LEFT JOIN apps ON apps.production_version_id = app_versions.id WHERE apps.id = 8675309;
=>
我无法理解这里发生了什么。它是一个VOLATILE
函数,所以它似乎不太可能是一个“缓存”值。
有什么建议么?
app_id
既是函数的参数,又是函数(app_versions.app_id
)内查询中的列名,列名优先。这是一个常见的问题。避免这种情况的一种方法是对函数参数使用命名约定,以将它们与列名区分开来。