postgresql SQL 状态：“11”处或附近出现 42601 语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11850194/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL state: 42601 syntax error at or near "11"
提问by prakashpoudel
I have a table address_alland it is inherited by several address tables. address_historyinherits from parent table history_alland keeps current address information. I am creating new table which inherits address_alltable and copies information from address_historyto new table.
我有一个表address_all，它由几个地址表继承。address_history从父表继承history_all并保留当前地址信息。我正在创建继承address_all表并将信息复制address_history到新表的新表。
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;
When I run:
当我运行时：
select somefunc()
I get this error:
我收到此错误：
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
回答by Erwin Brandstetter
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 >= '
USING date_trunc('hour', now() - interval '7 days');
END
$func$ LANGUAGE plpgsql;
Major points:
要点：
You can assign variables in plpgsql at declaration time. Simplifies code.
Use
to_char()to format your date. Much simpler.now()andCURRENT_TIMESTAMPdo the same.Don't quote
'now()', usenow()(without quotes) if you want the current timestamp.Use the
USINGclause withEXECUTE, so you don't have to convert thetimestamptotextand back - possibly running into quotingissues like you did. Faster, simpler, safer.In
LANGUAGE plpgsql,plpgsqlis a keyword and should not be quoted.You may want to check if the table already exists with
CREATE TABLE IF NOT EXISTS, available since PostgreSQL 9.1.
您可以在声明时在 plpgsql 中分配变量。简化代码。
使用
to_char()格式化你的日期。简单多了。now()并CURRENT_TIMESTAMP做同样的事情。不要引用
'now()'，now()如果您想要当前时间戳，请使用（不带引号）。使用
USINGwith 子句EXECUTE，这样您就不必转换timestamptotext和 back - 可能会遇到像您一样的引用问题。更快、更简单、更安全。In
LANGUAGE plpgsql,plpgsql是关键字，不应被引用。您可能想要检查该表是否已经存在
CREATE TABLE IF NOT EXISTS，从 PostgreSQL 9.1 开始可用。
回答by LSerni
Apparently you need to quote backupdays, or it is not seen as a string from where to parse a timestamp.
显然，您需要引用 backupdays，否则它不会被视为解析时间戳的字符串。
回答by mu is too short
You're building SQL using string manipulation so you have to properly quote everything just like in any other language. There are a few functions that you'll want to know about:
您正在使用字符串操作构建 SQL，因此您必须像使用任何其他语言一样正确引用所有内容。这里有一些功能，你会想知道：
quote_ident: quote an identifier such as a table name.quote_literal: quote a string to use as a string literal.quote_nullable: asquote_literalbut properly handles NULLs as well.
quote_ident: 引用一个标识符，例如表名。quote_literal: 引用一个用作字符串文字的字符串。quote_nullable: asquote_literal但也能正确处理 NULL。
Something like this will server you better:
这样的事情会更好地为您服务：
EXECUTE 'create table ' || quote_ident(addresstablename) || ...
EXECUTE 'insert into ' || quote_ident(addresstablename) || ... || quote_literal(backupdays) ...
The quote_identcalls aren't necessary in your case but they're a good habit.
该quote_ident电话是不是在你的情况下，必要的，但他们是一个很好的习惯。
