无法在 PostgreSQL 8.2 中运行块 [英] Not able to run block in PostgreSQL 8.2

问题描述

我无法在 PostgreSQL 8.2 中运行这个块.

I can't run this block in PostgreSQL 8.2.

DECLARE
        curtime char;
BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
        RETURN curtime;
END;

当我尝试时显示错误:

ERROR: syntax error at or near "char"
SQL state: 42601

推荐答案

听起来您正在尝试运行 PL/PgSQL 代码块独立，无需使用 创建或替换函数.这行不通，您需要将它包含在一个函数中或(来自 PostgreSQL 9.0)一个 DO 块.PL/PgSQL 和普通 SQL 是不同的语言，所以你不能直接运行 PL/PgSQL 代码.

It sounds like you're trying to run a PL/PgSQL code block stand-alone, without wrapping it up in a function using CREATE OR REPLACE FUNCTION. That won't work, you need to include it in a function or (from PostgreSQL 9.0) a DO block. PL/PgSQL and plain SQL are different languages so you can't just run PL/PgSQL code directly.

如果您解释了为什么您正在尝试编写您粘贴的代码，这会有所帮助.我怀疑您正在尝试解决一个可以更好地使用 触发函数处理的问题 就像一个审计触发器.

It'd help if you explained why you're trying to write the code you pasted. I suspect you're trying to solve a problem that's better handled with a trigger function like an audit trigger.

您需要更新 PostgreSQL:PostgreSQL 8.2 已经过时并且不支持.不再发布安全和错误修复.紧急升级到受支持的版本，但请务必阅读发行说明对于每个主要的.0"版本，如8.3.0"、8.4.0"等，以获得迁移和兼容性建议.

You need to update PostgreSQL: PostgreSQL 8.2 is dangerously out of date and unsupported. security and bug fixes are no longer being released. Upgrade urgently to a supported version, but make sure to read the release notes for each major ".0" version like "8.3.0", "8.4.0", etc for migration and compatibility advice.

避免使用 'now':此外，您通常应该使用 当前日期/时间函数，尤其是current_timestamp.

current_timestamp 是稳定的:你正在做的跳圈可能是不必要的，因为 current_timestamp(和 'now'::timestamp) 在交易期间不会改变.例如:

current_timestamp is stable: The hoop-jumping you are doing is probably unnecessary because the value of current_timestamp (and 'now'::timestamp) doesn't change for the duration of a transaction. Eg:

regress=# BEGIN;
regress=# SELECT current_timestamp;
 2012-08-14 14:52:43.382596+08
regress=# SELECT pg_sleep(5);
regress=# SELECT current_timestamp;
 2012-08-14 14:52:43.382596+08

详情

您的意图似乎类似于以下(不正确，请勿使用)代码:

CREATE OR REPLACE FUNCTION some_function(logtxt text) RETURNS timestamptz AS $$
DECLARE
        curtime char;
BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
        RETURN curtime;
END;
$$ LANGUAGE 'plpgsql';

但是您误用了 char 数据类型，它需要一个长度参数.如果未提供，则默认为 1，因此您将获得:

but you've misused the char datatype, which requires a length parameter. It defaults to 1 if not supplied so you'll get:

regress=# SELECT some_function();
ERROR:  value too long for type character(1)
CONTEXT:  PL/pgSQL function "some_function" line 5 at assignment

从不在 SQL 中使用 char 数据类型；使用 varchar 或 text.对于跨数据库可移植性 varchar(n) 其中 n 是需要的最大长度；如果不需要可移植性，请使用 text.

NEVER use the char datatype in SQL; use varchar or text. For cross-database portability varchar(n) where n is a maximum length is required; if portability isn't needed use text.

如果你把上面的char改成text，你的代码可能会运行，但仍然没有任何意义.我强烈怀疑你真的想写:

If you change char to text in the above, your code might run, but it still doesn't make any sense. I strongly suspect that you really want to write:

CREATE OR REPLACE FUNCTION some_function(logtxt text) RETURNS timestamptz AS $$
BEGIN
        INSERT INTO logtable VALUES (logtxt, current_timestamp);
        RETURN current_timestamp;
END;
$$ LANGUAGE 'plpgsql';

...但你不知道 当前日期/时间函数.

即使这样也太多了，真的.我认为您正在尝试解决一个更适合触发器的问题.

Even that's too much, really. I think you're trying to solve a problem that's a better fit for a trigger.

这篇关于无法在 PostgreSQL 8.2 中运行块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！