Using user defined function to update table data : Update « Insert Delete Update « PostgreSQL






  1. PostgreSQL
  2. Insert Delete Update
  3. Update

Using user defined function to update table data

postgres=# CREATE TABLE emp (
postgres(#    name        text,
postgres(#    salary      numeric,
postgres(#    age         integer,
postgres(#    cubicle     point
postgres(# );
CREATE TABLE
postgres=#
postgres=# insert into emp (salary) values(100);
INSERT 0 1
postgres=# insert into emp (salary) values(200);
INSERT 0 1
postgres=# insert into emp (salary) values(300);
INSERT 0 1
postgres=#
postgres=# CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
postgres$#    SELECT $1.salary * 2 AS salary;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
postgres-#    FROM emp;
 name | dream
------+-------
      | 220.0
      | 440.0
      | 660.0
(3 rows)

postgres=#
postgres=# drop function double_salary(emp);
DROP FUNCTION
postgres=# drop table emp;
DROP TABLE
postgres=#
postgres=#








Related examples in the same category

1.Update a single row
2.Using expression in update statement
3.Update two columns in one single statement
4.A simple UPDATE: change one cell
5.Updating entire columns
6.Updating Several Columns
7.Using UPDATE with several Employees
8.Update in a slice