How to create a conditional insert function in Postgresql?

user_12637180 0
Viewed 1302

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.

sql-function case sql-update postgresql sql
2 Answers

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.

user_106767160

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

user_36825990
Related