PL/Pgsql more than one row returned by a subquery

Question

I have 2 tables. Table1 and Table2. Only table 1 gets populated and then by a trigger I populate values of table2.

So, I have trigger like this,

CREATE OR REPLACE FUNCTION function_copy() RETURNS TRIGGER AS                                                                                                                                  
$BODY$                                                                                                                                                                                         
BEGIN                                                                                                                                                                                          
  INSERT INTO table2 (name) values ((select unnest(xpath('/attendee/bio/name/text()',data)) from table1));                                                                                    

  RETURN new;                                                                                                                                                                                  
END;                                                                                                                                                                                           
$BODY$                                                                                                                                                                                         
language plpgsql;        

Trigger function

CREATE TRIGGER trig_copy                                                                                                                                                                       
AFTER INSERT ON table1                                                                                                                                                                       
FOR EACH ROW                                                                                                                                                                                   
  EXECUTE PROCEDURE function_copy();        

So, when I insert more than 1 row into table1 I keep getting this error

ERROR: more than one row returned by a subquery used as an expression

Answer 1

@Gordon addresses your immediate SQL error with a set-returning function. But the whole approach is badly broken. You process the whole table for every inserted row, which creates a steadily increasing stream of duplicates. You later commented how you fix redundant inserts:

TRUNCATE TABLE2; INSERT INTO table2(name)...

Kind of .. creative. :) but don't. Only process the single inserted row. The special variable NEW is for this purpose exactly:

CREATE OR REPLACE FUNCTION function_copy()
  RETURNS trigger AS
$func$
BEGIN
  INSERT INTO table2 (name)
  SELECT *
  FROM   unnest(xpath('/attendee/bio/name/text()', NEW.data))

  RETURN NULL;  -- irrelevant for AFTER trigger
END
$func$ LANGUAGE plpgsql;

Answer 2

Don't use insert . . . values. Use insert . . . select:

INSERT INTO table2 (name) 
    select unnest(xpath('/attendee/bio/name/text()', data))
    from table1; 

The values clause assumes that there is only one value. Your query suggests that there are multiple rows to be inserted.