英文:
Why is the target table not recognized as table in select insert into procedure?
问题
我们正在从表TBL_bkmvdata_131_B110构建表TBL_bkmvdata_131_B110_L0，使用select insert into，但目标表不被识别为表。
我们得到了以下错误消息：
错误: "tbl_bkmvdata_131_b110_l0" 不是已知变量
行 15: INTO TBL_bkmvdata_131_B110_L0
^
SQL 状态: 42601
字符: 645
只是为了确保其他一切都正常... 如果我们取消注释declare TBL_bkmvdata_131_B110_L0 varchar;，
我们会得到：
CREATE PROCEDURE
查询在 43 毫秒内成功返回。
英文:
We are building a table TBL_bkmvdata_131_B110_L0 from table TBL_bkmvdata_131_B110 using the select insert into but the target table is not recognized as table.
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;
$$
We get:
ERROR: "tbl_bkmvdata_131_b110_l0" is not a known variable
LINE 15: INTO TBL_bkmvdata_131_B110_L0
^
SQL state: 42601
Character: 645
Just to make sure everything else works.. if we release the comment from
declare TBL_bkmvdata_131_B110_L0 varchar;
We get
CREATE PROCEDURE
Query returned successfully in 43 msec.
答案1
得分: 0
根据文档 43.5.3. 使用单行结果执行命令:
>SELECT select_expressions INTO [STRICT] target FROM ...;
这意味着您需要使用 CREATE TABLE ... AS SELECT 从旧表中构建一个新表。当您取消注释 -- declare TBL_bkmvdata_131_B110_L0 varchar; 时，使用了 plpgsql 形式的 SELECT INTO，并且将查询的结果分配给了 TBL_bkmvdata_131_B110_L0。
英文:
Per docs 43.5.3. Executing a Command with a Single-Row Result:
>SELECT select_expressions INTO [STRICT] target FROM ...;
<...>
>Tip
>Note that this interpretation of SELECT with INTO is quite different from PostgreSQL's regular SELECT INTO command, wherein the INTO target is a newly created table. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT.
This means you will need to use CREATE TABLE ... AS SELECT to build a new table from an old in the function. When you uncommented -- declare TBL_bkmvdata_131_B110_L0 varchar; then the plpgsql form of SELECT INTO was used and the result of the query was assigned to TBL_bkmvdata_131_B110_L0.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论