postgresql - 更新语句到函数 PostgreSQL

Question

我有以下有效的代码:

UPDATE backup_factura 
    SET tipo = CASE 
                 WHEN total_fact <=100 THEN 'X'
                 WHEN total_fact <=200 THEN 'Y'
                 ELSE 'Z'
               END
RETURNING *; 

我的老师让我把它转换成一个函数。我尝试了以下方法:

CREATE Function sp_test_case () returns void as $$ 
BEGIN

  UPDATE backup_factura 
    SET tipo = CASE 
                 WHEN total_fact <=100 THEN 'X'
                 WHEN total_fact <=200 THEN 'Y'
                 ELSE 'Z'
               END
  RETURNING *;

RETURN;   
END;   
$$ LANGUAGE plpgsql; 

但是当我执行函数时，我得到:

********** Error **********
ERROR: query has no destination for result data
SQL state: 42601
Context: PL / pgSQL sp_test_case () function in line 4 SQL statement

我也尝试过更复杂的方法:

CREATE Function sp_test_case () returns void as $$ 
DECLARE
    cont int=(Select MAX(id_fact)from backup_factura);
BEGIN

  while cont>0
  LOOP

    UPDATE backup_factura
    SET tipo= CASE 
            WHEN ((total_fact) <=100) THEN 'X'
            WHEN ((total_fact) <=200) THEN 'Y'
            ELSE 'Z'
           END;

    WHERE id_fact=cont;

    cont:=cont-1;

   END LOOP;

RETURN;   
END;   
$$ LANGUAGE plpgsql; 

但是我得到:

********** Error **********
ERROR: syntax error at or near "WHERE"
SQL state: 42601
Character: 283

关键是要显示这样的东西:

Answer 1

您可以使用return query 语法并返回一个setof 您的表:

CREATE Function sp_test_case () RETURNS SETOF backup_factura AS $$ 
BEGIN

  RETURN QUERY
  UPDATE backup_factura 
    SET tipo = CASE 
                 WHEN total_fact <=100 THEN 'X'
                 WHEN total_fact <=200 THEN 'Y'
                 ELSE 'Z'
               END
  RETURNING *;
END;   
$$ LANGUAGE plpgsql;