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