postgresql - 如何在 plpgsql 过程中使用 SELECT INTO？

Question

我正在尝试使用 SELECT INTO在 plpgsql 过程中。

CREATE OR REPLACE FUNCTION reset_data()
RETURNS void AS $$
BEGIN
DROP TABLE IF EXISTS experiment;
SELECT * INTO experiment FROM original;
END;
$$ LANGUAGE plpgsql;

这会导致错误:

ERROR:  "experiment" is not a known variable
LINE 5: SELECT * INTO experiment FROM original;
                      ^

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

ERROR: "experiment" is not a known variable
SQL state: 42601
Character: 113

显然，我们不能像这样使用 SELECT INTO。我们如何做到这一点？

Answer 1

不鼓励使用select into 来创建基于select 语句的表。

建议使用(符合标准)create table as .

select into 的文档 explicitly mentions PL/pgSQL as one of the reasons :

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently

所以你的函数应该是:

CREATE OR REPLACE FUNCTION reset_data()
RETURNS void AS $$
BEGIN
  DROP TABLE IF EXISTS experiment;
  create table experiment 
  as 
  SELECT * FROM original;
END;
$$ LANGUAGE plpgsql;