postgresql - 存储过程中的建表语句怎么写

Question

如何在 postgresql 的存储过程中编写以下查询？

 create table data1 as
 select A.*,
 case when score >=940 then 1
 when score between 600 and 746 then 2
 when bureau_score between 599 and 630 then 4 else 5 end as score_level,
 case when band between -1 and 5 then 1
 when band between 6 and 20 then 2
 when band between 21 and 35 then 3 else 4 end as band_level
 from data A;

Answer 1

Postgresql 本身没有存储过程，只有函数，所以。

如果它是简单的 SQL，您可以简单地将其包装在 SQL 函数定义中。

create or replace function foo () returns void language sql as $$

  create table data1 as
  select A.*,
  case when score >=940 then 1
  when score between 600 and 746 then 2
  when bureau_score between 599 and 630 then 4 else 5 end as score_level,
  case when band between -1 and 5 then 1
  when band between 6 and 20 then 2
  when band between 21 and 35 then 3 else 4 end as band_level
  from data A;

$$;

调用它 SELECT foo();