我有一个表address_all
,它被几个地址表继承。 address_history
继承父表history_all
并保留当前地址信息。我正在创建新表,该表继承address_all
表并将信息从address_history
复制到新表。
我的存储过程如下所示。当我调用它时,我遇到了一些错误。为了更好地解释错误,我使用了行号。
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
试试这个大大简化的形式:
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 中分配变量。简化代码。
使用
to_char()
设置日期格式。简单得多。now()
和CURRENT_TIMESTAMP
也这样做。不要引用
'now()'
,如果需要当前时间戳,请使用now()
(不带引号)。将
USING
子句与EXECUTE
一起使用,这样您就不必将timestamp
转换为text
并返回 - 可能会像您一样遇到引用问题。更快、更简单、更安全。在
LANGUAGE plpgsql
中,plpgsql
是一个关键字,不应该被引用。您可能想检查该表是否已与
CREATE TABLE IF NOT EXISTS
一起存在,自 PostgreSQL 9.1 起可用。
您需要引用备份天数,否则它不被视为从何处解析时间戳的字符串。
您正在使用字符串操作构建 SQL,因此您必须像任何其他语言一样正确引用所有内容。您需要了解一些函数:
-
quote_ident
:引用标识符,例如表名。 -
quote_literal
:引用字符串以用作字符串文字。 -
quote_nullable
:quote_literal
,但也正确处理空值。
像这样的东西会更好地为你服务:
EXECUTE 'create table ' || quote_ident(addresstablename) || ...
EXECUTE 'insert into ' || quote_ident(addresstablename) || ... || quote_literal(backupdays) ...
在您的情况下,quote_ident
电话不是必需的,但它们是一个好习惯。