How do I create type methods in PostgreSQL?
Lets take the following type for example:
create type Employee as (
name varchar(20),
salary integer)
How do I do this?
create method giveraise (percent integer) for Employee
begin
set self.salary = self.salary + (self.salary * percent) / 100;
end
Postgres doesn't have type methods per se. However, it supports attribute notation for the execution of functions with a single parameter. This looks almost exactly like a method for the type. Consider this simple example:
CREATE OR REPLACE FUNCTION raise10(numeric)
RETURNS numeric LANGUAGE sql AS 'SELECT $1 * 1.1';
Call:
SELECT (100).raise10;
Result:
raise10
---------
110.0
A major limitation is that this only works for a single parameter. No way to pass in additional parameters like a percentage for a variable raise.
Works for composite types just as well. More about "computed fields":
To take this one step further, one can even call an UPDATE on the row and persist the change:
CREATE TABLE employee (
name text PRIMARY KEY
, salary numeric
);
INSERT INTO employee VALUES
('foo', 100)
, ('bar', 200)
;
CREATE OR REPLACE FUNCTION giveraise10(employee)
RETURNS numeric
LANGUAGE sql AS
$func$
UPDATE employee
SET salary = salary * 1.1 -- constant raise of 10%
WHERE name = ($1).name
RETURNING salary;
$func$;
Call:
SELECT *, e.giveraise10 FROM employee e;
Result:
name | salary | giveraise10
------+--------+-------------
foo | 100 | 110.0
bar | 200 | 220.0
SELECT displays the pre-UPDATE value for salary, but the field has actually been updated!
SELECT * FROM employee e;
name | salary
------+--------
foo | 110.0
bar | 220.0
Whether it's wise to use such trickery is for you to decide. I probably wouldn't go there. There are more efficient and transparent ways to update a table.
objecttypes can have methods, but I am unaware of atypein Postgres with this behaviour. Instead you would create a function to accept the two parameters:integerandEmployee.self.salary * ((100::numeric + percent) / 100).