我们正在使用select insert into从表TBL_bkmvdata_131_B110构建表TBL_bkmvdata_131_B110_L0，但目标表未被识别为表。
create or replace procedure gla_first_step()
language plpgsql
as $$
-- declare TBL_bkmvdata_131_B110_L0 varchar;
begin
select *,
CASE WHEN (B110TrialBalanceCodeText1406 LIKE N'%DATA%' and B110TrialBalanceCodeText1406 not LIKE N'%DATA%'
and B110TrialBalanceCodeText1406 not LIKE N'%DATA%') OR
(B110TrialBalanceCodeText1406 LIKE N'%DATA%' and B110TrialBalanceCodeText1406 not like N'%DATA%') OR
B110TrialBalanceCodeText1406 LIKE N'%DATA%' OR
B110TrialBalanceCodeText1406 LIKE '%EXPENSE%' OR
B110TrialBalanceCodeText1406 LIKE '%INCOME%'
THEN 'P' else 'B' end as accountType
INTO TBL_bkmvdata_131_B110_L0
from TBL_bkmvdata_131_B110;
end;
$$我们得到：
ERROR: "tbl_bkmvdata_131_b110_l0" is not a known variable
LINE 15: INTO TBL_bkmvdata_131_B110_L0
^
SQL state: 42601
Character: 645只是为了确保其他一切正常。如果我们发布declare TBL_bkmvdata_131_B110_L0 varchar;的评论
我们得到
CREATE PROCEDURE
Query returned successfully in 43 msec.
1条答案
按热度按时间jckbn6z71#
依据文档43.5.3。执行具有单行结果的命令：
SELECT select_expressions INTO [STRICT] target FROM ...;
<...>
尖端
注意，这种对SELECT和INTO的解释与PostgreSQL的常规SELECT INTO命令完全不同，后者的INTO目标是一个新创建的表。如果要从PL/pgSQL函数中的SELECT结果创建表，请使用语法CREATE TABLE... AS SELECT.
这意味着您需要使用
CREATE TABLE ... AS SELECT从函数中的旧表构建新表。当您取消注解-- declare TBL_bkmvdata_131_B110_L0 varchar;时，将使用SELECT INTO的plpgsql形式，并将查询结果分配给TBL_bkmvdata_131_B110_L0。