sql - 与更新 Postgres 文件夹中的表的日期转换相关的类型错误

Question

在 Postgres 数据库中导入数据后，我运行一个函数来规范化来自 ODBC 源的一些数据。一个字段是一个日期，但它以 varchar 的形式出现，并且对于某些行它是空的，所以当我尝试将它转换为日期类型时它失败了。我意识到这并不重要，因为数据仅用于显示，但我需要它采用有用的格式，所以我找到了以下 SQL:

UPDATE "MyTable" SET "VisitDate" =  to_char( DATE("VisitDate"), 'DD Mon YYYY' )  WHERE "VisitDate" <> '';

如果我直接在 SQL 浏览器中运行它，这是可行的，但是当我执行该函数时，它会这样说:

ERROR:  table row type and query-specified row type do not match
DETAIL:  Table has type character varying at ordinal position 6, but query expects text.

据我所知，这是因为 to_char() 函数返回文本，但该字段是一个 varchar(并且给定内容是合乎逻辑的)所以我想也许我可以做以下事情:

ALTER TABLE "MyTable" ALTER "VisitDate" TYPE "text";
UPDATE "MyTable" SET "VisitDate" =  to_char( DATE("VisitDate"), 'DD Mon YYYY' )  WHERE "VisitDate" <> '';
ALTER TABLE "MyTable" ALTER "VisitDate" TYPE varchar(12);

当然，这在函数中不起作用，因为 - 据我了解 - Postgres 在单个事务中运行整个过程，因此 ALTER 指令不会在 UPDATE 之前发生，因此会出现相同的错误。

如果我尝试使用 varchar( to_char( ...etc )) 它只会提示 varchar 函数的内容。

在下面的评论之后，我尝试在函数中只包含该行并且它成功，如果我在函数中包含所有表更改语句它成功，如果我同时包含它则失败。该函数的基本形式是这样的:

CREATE OR REPLACE FUNCTION at_ebc_alterscorescolumns() RETURNS void AS
$BODY$
ALTER TABLE "MyTable" ALTER "Postcode" TYPE varchar(10);
UPDATE "MyTable" SET "VisitDate" =  to_char( DATE("VisitDate"), 'DD Mon YYYY' )  WHERE "VisitDate" <> '';
$BODY$
LANGUAGE sql VOLATILE
COST 100;

这让我想知道是否不可能在单个函数中更改表和更改数据。

我的问题是:如何在不导致此错误的情况下更改字段的格式？

Answer 1

执行此操作时我可以看到 PG 8.4 的错误:

create table foo(somedate text);

create function alter_foo() returns void as
$$
alter table foo alter somedate type varchar(10);
update foo set somedate = to_char(date(somedate),'DD Mon YYYY' );
$$
language sql;

select alter_foo();

函数调用失败并显示此消息:

ERROR: table row type and query-specified row type do not match DETAIL: Table has type character varying at ordinal position 1, but query expects text. CONTEXT: SQL function "alter_foo" statement 2

简单的解决方法是选择 plpgsql 语言而不是 sql 语言。前者是过程性的，而后者更适合单行代码和适合自动内联的内容。我猜 sql 语言处理器以某种方式预先解析查询，如果在函数执行期间更改数据类型，它会感到困惑。

无论如何，如果你这样声明它:

create function alter_foo() returns void as
$$
 begin
   alter table foo alter somedate type varchar(10);
   update foo set somedate = to_char(date(somedate),'DD Mon YYYY' );
 end;
$$ language plpgsql;

然后它似乎按预期工作。