How to create a conditional insert function in Postgresql?

Question

I try to write a function to insert some values in column3 in my table based on values inside this table, but I'm not so familiar with writing functions in 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;

The result should be a table that looks like this:

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

While calling new_function I get the error column1 doesn't exist.

GMB · Accepted Answer · 2020-06-05T17:10:39.640

You seem to be actually looking for an update (that changes values on existing rows) rather than an insert (that creates new rows).

But bottom line, I would suggest just using a computed column:

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

When rows are inserted or updated in the table, the database automatically adjusts the computed column accordingly, so it is always up to date.

Demo on DB Fiddle:

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

select * from test;

column1 | column2 | column3
------: | ------: | :------
  -8020 |      15 | small  
   -200 |       1 | nohello
     23 |     115 | nodata

Note that generated columns are available starting Postgres 12 only. In earlier versions, one alternative is have just the first two columns in the table, and to create a view to expose the third column:

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

You can then query the view instead of the table to display your data.

Create table test gives me "syntax error at or near "generated"." Maybe correlated to my Postgresql version? — DrSnuggles, Jun 05 '20 at 16:59
@DrSnuggles: indeed, computed column came in version 12. I edited my answer to suggest another solution, that works on earlier versions. — GMB, Jun 05 '20 at 17:11

Vivek S. · Answer 2 · 2020-06-05T16:41:55.600

3

GMB's answer is the perfect solution, though you may update the table by using CASE conditional expression as shown below

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

edited Jun 05 '20 at 16:41

answered Jun 05 '20 at 16:32

Vivek S.

19,945
7
68
85

How to create a conditional insert function in Postgresql?

2 Answers2