如何在PostgreSQL中创建一个有条件的插入函数？

Question

我试图编写一个函数，在我的表中基于表内的值向column3插入一些值，但是我不太熟悉在Postgresql 9.6中编写函数。

--Create some table

    CREATE TABLE test(column1 INT, column2 INT, column3 TEXT) 
    INSERT INTO test VALUES(-8020,15),(-200,1),(23,115)

--Build function

    CREATE OR REPLACE FUNCTION new_function()
    RETURNS TEXT AS 
    $$
    BEGIN

        IF test(column1) <= -7000 THEN INSERT INTO test(column3) VALUES('small');
        ELSIF test(column1) >= -7000 AND test(column2) <= 15 THEN INSERT INTO test(column3) VALUES('nohello');
        ELSIF test(column1) >= -7000 ANDtable(column2) >= 15 THEN INSERT INTO test(column3) VALUES('test');
        ELSE INSERT INTO test(column6) VALUES("nodata");
        END IF;

    END;
    $$
    LANGUAGE plpgsql;

结果应该是一个看起来像这样的表格：

Column1 | Column2 | Column3
---------------------------
 -8020  |    15   |  small
  -200  |     1   |  nohello
    23  |   115   |  test

在调用new_function时，我遇到了错误column1不存在。

- DrSnuggles

Answer 1

你似乎实际上是在寻找一种更新（更改现有行上的值）而不是插入（创建新行）的方法。

但归根结底，我建议只使用计算列：

create table test(
    column1 int, 
    column2 int, 
    column3 text generated always as (
        case 
            when column1 <= -7000 then 'small'
            when column1 <= 15    then 'nohello'
            else 'nodata'
        end
    ) stored
);

当表格中插入或更新行时，数据库会自动调整计算列，使其始终保持最新状态。

在DB Fiddle上演示：

insert into test(column1, column2) values(-8020,15),(-200,1),(23,115);

select * from test;

列1 | 列2 | 列3
----: | ----: | :------
-8020 | 15 | 小
-200 | 1 | 没有问候
23 | 115 | 没有数据
请注意，生成的列仅在Postgres 12及更高版本中可用。在早期版本中，一种替代方法是只在表中使用前两列，并创建视图以公开第三列。

create view myview as 
select
    column1,
    column2,
    case 
        when column1 <= -7000 then 'small'
        when column1 <= 15    then 'nohello'
        else 'nodata'
    end as column3
from mytable

您可以查询视图而不是表来显示您的数据。

- GMB

---

创建表test时出现了“在generated附近的语法错误”。可能与我的Postgresql版本有关吗？ - undefined

@DrSnuggles：确实，计算列是在版本12中引入的。我编辑了我的答案，提出了另一种解决方案，适用于早期版本。 - undefined

Answer 2

GMB的答案是完美的解决方案，但您可以通过使用下面所示的CASE条件表达式来更新表格。

update test
set column3 = case 
                when column1 <= - 7000 then 'small'
                when (column1 >= - 7000 and column2 <= 15) then 'nohello'
                when (column1 >= - 7000 and column2 >= 15) then 'test'
                else 'nodata'
              end;

Demo

- Vivek S.