SQL状态：42601或接近“11”的语法错误 [英] SQL state: 42601 syntax error at or near "11"

问题描述

我有一个表 address_all ，它被几个地址表继承。 address_history 继承自父表 history_all 并保留当前地址信息。我正在创建继承 address_all 表的新表，并将信息从 address_history 复制到新表。

I have a table address_all and it is inherited by several address tables. address_history inherits from parent table history_all and keeps current address information. I am creating new table which inherits address_all table and copies information from address_history to new table.

我的存储过程如下所示。当我打电话给我时，我有一些错误。为了更好地解释错误，我使用行号。

My stored procedure is like this below. I am having some error when I call it. To better explain error I am using line number.

1  CREATE OR REPLACE FUNCTION somefunc()
2  RETURNS void AS
3  $BODY$
4  DECLARE
5   year_id INTEGER;
6   month_id INTEGER;
7   week_id INTEGER;
8   addresstablename text; 
9   backupdays text;
10 BEGIN
11  week_id := EXTRACT(DAY FROM TIMESTAMP 'now()');
12  month_id := EXTRACT(MONTH FROM TIMESTAMP 'now()');
13  year_id := EXTRACT(YEAR FROM TIMESTAMP 'now()');
14  addresstablename := 'address_history_' || week_id || '_' || month_id || '_' || year_id;
15  backupdays:= date_trunc('hour',CURRENT_TIMESTAMP - interval '7 days');
16  EXECUTE 'create table ' || addresstablename || '() INHERITS (address_all)';
17  EXECUTE 'insert into ' || addresstablename || ' select * from address_history where address_timestamp >= ' || backupdays || ''; --AS timestamp without time zone);  
18 END;
19 $BODY$
20 LANGUAGE 'plpgsql' VOLATILE;

当我运行：

select somefunc()

我收到这个错误：

ERROR:  syntax error at or near "12"
LINE 1: ...story where address_timestamp >= 2012-07-31 12:00:00-0...
                                                         ^
QUERY:  insert into address_history_7_8_2012 select * from address_history where address_timestamp >= 2012-07-31 12:00:00-04
CONTEXT:  PL/pgSQL function "somefunc" line 14 at EXECUTE statement

 ********** Error **********

ERROR: syntax error at or near "12"
SQL state: 42601
Context: PL/pgSQL function "somefunc" line 14 at EXECUTE statement

推荐答案

尝试这个大致简化的形式：

Try this largely simplified form:

CREATE OR REPLACE FUNCTION somefunc()
  RETURNS void AS
$func$
DECLARE
 addresstablename text := 'address_history_' || to_char(now(), 'FMDD_MM_YYYY');

BEGIN
 EXECUTE 
 'CREATE TABLE ' || addresstablename || '() INHERITS (address_all)';

 EXECUTE
 'INSERT INTO ' || addresstablename || '
  SELECT *
  FROM   address_history
  WHERE  address_timestamp >= $1'
 USING date_trunc('hour', now() - interval '7 days');

END
$func$ LANGUAGE plpgsql;

主要要点：

• 您可以在声明时间在plpgsql中分配变量。简化代码。

  Major points:

  • You can assign variables in plpgsql at declaration time. Simplifies code.

    使用 to_char（） 格式化您的日期。更简单。

    Use to_char() to format your date. Much simpler.

    now（）和 CURRENT_TIMESTAMP 执行同样的操作。

    不要引用'now（）'，使用 now（）（不带引号）如果你想要当前的时间戳。

    Don't quote 'now()', use now() (without quotes) if you want the current timestamp.

    使用 USING 子句与 EXECUTE ，因此您不必将时间戳转换为文本并返回 - 可能会像你一样遇到引用问题。

    Use the USING clause with EXECUTE, so you don't have to convert the timestamp to text and back - possibly running into quoting issues like you did. Faster, simpler, safer.

    在 LANGUAGE plpgsql ， plpgsql 是一个关键字，不应该被引用。

    In LANGUAGE plpgsql, plpgsql is a keyword and should not be quoted.

    您可能需要检查表中是否存在 CREATE TABLE IF NOT EXISTS ，available因为PostgreSQL 9.1。

    You may want to check if the table already exists with CREATE TABLE IF NOT EXISTS, available since PostgreSQL 9.1.

    这篇关于SQL状态：42601或接近“11”的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！