O PostgreSQL usa NEW na consulta para o gatilho INSTEAD OF

NEWé um registro , não uma tabela . Fundamentos:

• Use NEW na cláusula FROM no gatilho do Postgres?

Configuração ligeiramente modificada

CREATE TABLE product (
  product_id serial PRIMARY KEY,
  product_name text UNIQUE NOT NULL  -- must be UNIQUE
);

CREATE TABLE purchase (
  purchase_id serial PRIMARY KEY,
  product_id  int REFERENCES product,
  when_bought date
);

CREATE VIEW purchaseview AS
SELECT pu.purchase_id, pr.product_name, pu.when_bought
FROM   purchase     pu
LEFT   JOIN product pr USING (product_id);

INSERT INTO product(product_name) VALUES ('foo');

product_nametem que ser UNIQUE, ou a pesquisa nesta coluna poderia encontrar várias linhas, o que levaria a todos os tipos de confusão.

1. Solução simples

Para o seu exemplo simples, procurando apenas a coluna única product_id, uma subconsulta correlacionada é mais simples e rápida:

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase(product_id, when_bought)
   SELECT (SELECT product_id FROM product WHERE product_name = NEW.product_name), NEW.when_bought
   RETURNING purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
INSTEAD OF INSERT ON purchaseview
FOR EACH ROW EXECUTE PROCEDURE insert_purchaseview_func();

Sem variáveis ​​adicionais. Sem CTE (só acrescentaria custo e ruído). As colunas de NEWsão escritas apenas uma vez (seu ponto 1 ).

O anexo RETURNING purchase_id INTO NEW.purchase_idcuida do seu ponto 2 : agora, a linha retornada inclui o recém-geradopurchase_id .

Caso o produto não seja encontrado ( NEW.product_namenão existe na tabela product), a compra ainda é inserida e product_idéNULL . Isso pode ou não ser desejável.

2.

Para pular a linha (e possivelmente aumentar um WARNING/ EXCEPTION):

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,     when_bought)
   SELECT pr.product_id, NEW.when_bought
   FROM   product pr
   WHERE  pr.product_name = NEW.product_name
   RETURNING pu.purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   IF NOT FOUND THEN  -- insert was canceled for missing product
      RAISE WARNING 'product_name % not found! Skipping INSERT.', quote_literal(NEW.product_name);
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Isso pega carona NEWnas colunas para SELECT .. FROM product. Se o produto for encontrado, tudo ocorre normalmente. Se não, nenhuma linha é retornada do SELECTe não INSERTacontece. A variável especial PL/pgSQL FOUNDsó é verdadeira se a última consulta SQL processou pelo menos uma linha.

Pode ser EXCEPTIONem vez de WARNINGgerar um erro e reverter a transação. Mas prefiro declarar purchase.product_id NOT NULLe inserir incondicionalmente (consulta 1 ou similar), para o mesmo efeito: gera uma exceção se product_idfor NULL. Mais simples, mais barato.

3. Para várias pesquisas

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,   when_bought)     -- more columns?
   SELECT pr.product_id, i.when_bought      -- more columns?
   FROM  (SELECT NEW.*) i                   -- see below
   LEFT   JOIN product  pr USING (product_name)
-- LEFT   JOIN tbl2     t2 USING (t2_name)  -- more lookups?
   RETURNING pu.purchase_id                 -- more columns?
   INTO   NEW.purchase_id;                  -- more columns?

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Os LEFT JOINs tornam o INSERTincondicional novamente. Em vez disso, use JOINpara pular se não for encontrado.

FROM (SELECT NEW.*) itransforma o registro NEW em uma tabela derivada com uma única linha , que pode ser usada como qualquer tabela noFROM cláusula - o que você procurava, inicialmente.

db<>mexa aqui

Conforme sugerido nos comentários, parece que o mais próximo que posso fazer do que quero é (corrigindo minha abordagem original na pergunta):

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$BODY$
DECLARE
tmp RECORD;
BEGIN
    WITH input  (product_name, when_bought) as (
       values (NEW.product_name, NEW.when_bought)
    ) 
    INSERT INTO Purchase(product_id, when_bought)
    SELECT product_id, when_bought
    FROM input
    LEFT JOIN Product USING (product_name)
    RETURNING purchase_id INTO tmp;
    NEW.purchase_id = tmp.purchase_id;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

Isso pelo menos faz com que a RETURNINGcláusula funcione corretamente. Parece que os atributos de NEWdevem ser explicitamente declarados. A seguir:

-- Using NEW.* in CTE doesn't work
CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$BODY$
DECLARE
tmp RECORD;
BEGIN
    WITH input  as (
       values (NEW.*)
    ) 
    INSERT INTO Purchase(product_id, when_bought)
    SELECT product_id, when_bought
    FROM input
    LEFT JOIN Product USING (product_name)
    RETURNING purchase_id INTO tmp;
    NEW.purchase_id = tmp.purchase_id;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

resulta emERROR: column "product_name" specified in USING clause does not exist in left table quando o gatilho é disparado.