使用PostgreSQL的XMLTABLE从XML向临时表插入数据的问题

Let's break down and fix your issues step by step:

1. Why your initial standalone SQL failed #

PostgreSQL's regular SQL doesn't support declaring variables directly outside of functions/stored procedures. That's why using xmlvalue text := ... outside a PL/pgSQL block threw a syntax error—you need to wrap variable-driven logic in a function or procedure, which you already started doing.

2. Fixing the "column reference is ambiguous" error #

The error happens because your function defines output columns Id, Author, and Title (via returns table(...)), and these act as variables within the PL/pgSQL block. When you write SELECT Id, Author, Title without qualifying where these columns come from, PostgreSQL can't tell if you're referring to the XMLTABLE results or the function's output variables.

The fix is simple: assign an alias to the XMLTABLE result set and explicitly reference the columns using that alias. Also, you can simplify your code by skipping the temporary table entirely—you can return the XML-parsed results directly without inserting into a temp table first.

Option 1: Fixed version with temporary table (matches your original approach)

create or replace function parse_book_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 IF NOT EXISTS SAMPLE(Id text, Author text, Title text);
  TRUNCATE TABLE SAMPLE; -- Optional: clear temp table before inserting if reusing the function

  with data as (select xmlvalue::xml val)
  INSERT INTO SAMPLE(Id, Author, Title)
  SELECT xt.Id, xt.Author, xt.Title
  FROM data x, XMLTABLE('/Book' PASSING val 
    COLUMNS 
      Id text PATH 'Id', 
      Author text PATH 'Author', 
      Title text PATH 'Title'
  ) AS xt; -- Alias for XMLTABLE results

  return query select s.Id, s.Author, s.Title from sample s;
end;
$$ language plpgsql;

Option 2: Simplified version (no temporary table needed)

This is more efficient since we avoid the extra insert/select step on the temp table:

create or replace function parse_book_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
  return query
  SELECT xt.Id, xt.Author, xt.Title
  FROM XMLTABLE('/Book' PASSING xmlvalue::xml
    COLUMNS 
      Id text PATH 'Id', 
      Author text PATH 'Author', 
      Title text PATH 'Title'
  ) AS xt;
end;
$$ language plpgsql;

How to use the function #

Call it just like you would a table:

SELECT * FROM parse_book_xml();

Key notes for dynamic XML parsing #

Since you mentioned needing to handle dynamic XML later:

• You can modify the function to accept an XML string as a parameter instead of hardcoding it:

  create or replace function parse_book_xml(p_xml text) returns table(Id text, Author text, Title text) as $$
  begin
    return query
    SELECT xt.Id, xt.Author, xt.Title
    FROM XMLTABLE('/Book' PASSING p_xml::xml
      COLUMNS 
        Id text PATH 'Id', 
        Author text PATH 'Author', 
        Title text PATH 'Title'
    ) AS xt;
  end;
  $$ language plpgsql;
  

• Then call it with your dynamic XML:

  SELECT * FROM parse_book_xml('<Book><Id>2</Id><Author>New Author</Author><Title>Advanced Postgres</Title></Book>');
  

内容的提问来源于stack exchange，提问作者subhrendu