使用Xmltable postgres从xml在临时表中插入值

Question

我正试图将xml节点作为参数传递，并从中提取值并将其插入表中。如果我传递整个xml节点，我可以这样做，但如果我试图通过一个变量传递它，我将面临语法错误。由于我将拥有需要解析的动态xml，所以我只能通过变量传递它。我在下面提供了一个我正在努力实现的目标的简化版本。我得到一个语法错误"语法错误在或接近"；xmlvalue"'

CREATE TEMPORARY TABLE SAMPLE(Id text,Author text,Title text);
xmlvalue text := '<Book><Id>1</Id><Author>Subhrendu</Author><Title>Postgre</Title></Book>';
with data as ( 
select xmlvalue::xml val)
--select '<Book><Id>1</Id><Author>Subhrendu</Author><Title>Postgre</Title></Book>'::xml val)
INSERT INTO SAMPLE(Id, Author,Title)
SELECT Id, Author,Title 
FROM   data x, 
XMLTABLE('/Book' 
PASSING val 
COLUMNS  
Id    text  PATH 'Id', 
Author text  PATH 'Author', 
Title text PATH 'Title' )   
; 
select * from sample 

编辑1：正如建议的那样，我现在正试图将上述代码封装在函数中，因为我们不能在过程/函数之外使用变量。

create or replace function xml()
returns table(Id text,Author Text,Title Text)
as $$
declare
xmlvalue text := '<Book><Id>1</Id><Author>Subhrendu</Author><Title>Postgre</Title></Book>';
begin
CREATE TEMPORARY TABLE SAMPLE(Id text,Author text,Title text);
with data as ( 
select xmlvalue::xml val)
INSERT INTO SAMPLE(Id, Author,Title)
SELECT Id, Author,Title 
FROM   data x, 
XMLTABLE('/Book' 
PASSING val 
COLUMNS  
Id    text  PATH 'Id', 
Author text  PATH 'Author', 
Title text PATH 'Title' )   
; 
return query
select s.Id,s.Author,s.Title from sample s ;
end;
$$
language plpgsql

在尝试执行上述函数时，我遇到了以下错误。我从错误中了解到，我必须提供表别名来引用列。

ERROR:  column reference "id" is ambiguous
LINE 4: SELECT Id, Author,Title 
^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  with data as ( 
select xmlvalue::xml val)
INSERT INTO SAMPLE(Id, Author,Title)
SELECT Id, Author,Title 
FROM   data x, 
XMLTABLE('/Book' 
PASSING val 
COLUMNS  
Id    text  PATH 'Id', 
Author text  PATH 'Author', 
Title text PATH 'Title' )
CONTEXT:  PL/pgSQL function xml() line 6 at SQL statement
SQL state: 42702

Answer 1

这很有效。

create or replace function xml()
returns table(Id text,Author Text,Title Text)
as $$
declare
xmlvalue text := '<Book><Id>1</Id><Author>Subhrendu</Author><Title>Postgre</Title></Book>';
begin
CREATE TEMPORARY TABLE SAMPLE(Id text,Author text,Title text);
with data as ( 
select xmlvalue::xml val)
INSERT INTO SAMPLE(Id, Author,Title)
SELECT d.Id,d.Author,d.Title 
FROM   data x, 
XMLTABLE('/Book' 
PASSING val 
COLUMNS  
Id    text  PATH 'Id', 
Author text  PATH 'Author', 
Title text PATH 'Title' ) as d
; 
return query
select s.Id,s.Author,s.Title from sample s ;
end;
$$
language plpgsql