创建引用不存在的表或列的SQL函数(尚未) [英] Create SQL function referring to a table or column that does not exist (yet)

问题描述

我想通过psql在空数据库中加载一些SQL函数:

I want to load some SQL functions in a empty database through psql:

psql -d my_database -f fuctions.sql --set ON_ERROR_STOP=1 

我使用--set ON_ERROR_STOP=1是因为如果脚本包含错误，我希望psql失败.

I use --set ON_ERROR_STOP=1 because I want that psql fails if the script contains errors.

functions.sql的内容是:

CREATE or REPLACE FUNCTION test_function() RETURNS INT AS $$
  SELECT id from test_table;
$$ LANGUAGE sql;

我的问题是，psql在加载函数时检查test_table是否存在，并因以下错误而失败:

My problem is, that psql checks if test_table exists when loading function and fails with this error:

ERROR:  relation "test_table" does not exist LINE 2: SELECT id from test_table;

但是我不希望psql检查表是否存在，因为稍后我将创建该表.

But I don't want that psql check if the table exists because I will create this table later.

以下解决方法可以工作，但我不能使用它们:

Following workarounds would work but I cannot use them:

• 忽略错误.如果脚本包含sql语法错误，我希望psql退出并显示错误消息.
• 使用plpgsql函数代替sql.当然可以，但是简单的sql函数通常是最佳选择.
• 首先创建表.实际上，我的实际情况比此示例还要复杂.

推荐答案

错误消息来自Postgres，而不是psql.

The error message comes from Postgres, not from psql.

如果您不能首先创建表(无论出于何种原因)，可以伪造它直到创建".:创建一个具有匹配结构的临时表.您只需要匹配列名称和类型即可.对于您的示例功能:

If you cannot create the table first (for what ever reason), you could "fake it until you make it": Create a temporary table with matching structure. You only need column names and types to match. For your example function:

CREATE TEMP TABLE test_table (id int);

然后CREATE FUNCTION通过.禁止以后放桌子. Postgres不会在函数体中保存代码的依赖项.因此，一旦创建函数，就可以删除表.如果在删除临时表后调用该函数，则会收到错误消息.

Then CREATE FUNCTION goes through. Dropping the table later is not prohibited. Postgres does not save dependencies for code in the function body. So you can drop the table once the function is created. If you call the function after dropping the temp table, you get an error.

稍后创建实际表后，该功能将正常工作.

After you create the actual table later, the function will work normally.

据我所知，这不可能.也许Postgres有一个编译时选项可以禁用它. 该手册建议在类似您的情况下使用PL/PgSQL :

To my knowledge, this is not possible. Maybe there is a compile-time option for Postgres to disable it. The manual advises to use PL/PgSQL for cases like yours:

注意:先解析SQL函数的整个主体，然后再对其进行分析 被执行.虽然SQL函数可以包含更改 系统目录(例如CREATE TABLE)，此类命令的效果 在后面的命令的解析分析过程中将不可见 功能.因此，例如，如果CREATE TABLE foo (...); INSERT INTO foo VALUES(...);打包到单个SQL中，将无法按预期工作 函数，因为INSERT命令解析后foo尚不存在. 在此建议建议使用PL/PgSQL代替SQL函数 情况类型.

Note: The entire body of a SQL function is parsed before any of it is executed. While a SQL function can contain commands that alter the system catalogs (e.g., CREATE TABLE), the effects of such commands will not be visible during parse analysis of later commands in the function. Thus, for example, CREATE TABLE foo (...); INSERT INTO foo VALUES(...); will not work as desired if packaged up into a single SQL function, since foo won't exist yet when the INSERT command is parsed. It's recommended to use PL/PgSQL instead of a SQL function in this type of situation.

强调粗体.

这篇关于创建引用不存在的表或列的SQL函数(尚未)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！