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;

标签： postgresqlstored-procedures

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();