PostgreSQL Type Method

Question

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

You do realize that PostgreSQL is SQL database, and not object oriented programming framework? — , Feb 12 '13 at 00:08
Oracle `object` types can have methods, but I am unaware of a `type` in Postgres with this behaviour. Instead you would create a function to accept the two parameters: `integer` and `Employee`. — Glenn, Feb 12 '13 at 01:56
As a side note, what you calculate in your method doesn't make a lot of sense. Would have to be `self.salary * ((100::numeric + percent) / 100)`. — Erwin Brandstetter, Feb 12 '13 at 09:29

score 2 · Answer 1 · edited May 23 '17 at 12:27

You have been told in the comments, that Postgres doesn't have type methods.

However, Postgres 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" in this related answer:
Store common query as column?

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 AS
$func$
  UPDATE employee
  SET    salary = salary * 1.1   -- constant raise of 10%
  WHERE  name = ($1).name
  RETURNING salary;
$func$ LANGUAGE sql;

Call:

SELECT *, e.giveraise10 FROM employee e;

Result:

 name | salary | giveraise10
------+--------+-------------
 foo  |    100 |       110.0
 bar  |    200 |       220.0

->sqlfiddle
The SELECT displays the pre-UPDATE value for salary, but the field has actually been updated!

SELECT *, e.giveraise10 FROM employee e;

 name | salary 
------+--------
 foo  |  110.0 
 bar  |  220.0

Whether it's wise to use such trickery is for you to decide. There are more efficient and transparent ways to update a table.

PostgreSQL Type Method

1 Answers1