1
---------------------------------------------------------------------------
4
-- Tutorial on using functions in POSTGRES.
7
-- Copyright (c) 1994-5, Regents of the University of California
9
-- src/tutorial/funcs.source
11
---------------------------------------------------------------------------
13
-----------------------------
14
-- Creating SQL Functions on Base Types
15
-- a CREATE FUNCTION statement lets you create a new function that
16
-- can be used in expressions (in SELECT, INSERT, etc.). We will start
17
-- with functions that return values of base types.
18
-----------------------------
21
-- let's create a simple SQL function that takes no arguments and
24
CREATE FUNCTION one() RETURNS integer
25
AS 'SELECT 1 as ONE' LANGUAGE SQL;
28
-- functions can be used in any expressions (eg. in the target list or
31
SELECT one() AS answer;
34
-- here's how you create a function that takes arguments. The following
35
-- function returns the sum of its two arguments:
37
CREATE FUNCTION add_em(integer, integer) RETURNS integer
38
AS 'SELECT $1 + $2' LANGUAGE SQL;
40
SELECT add_em(1, 2) AS answer;
42
-----------------------------
43
-- Creating SQL Functions on Composite Types
44
-- it is also possible to create functions that return values of
46
-----------------------------
48
-- before we create more sophisticated functions, let's populate an EMP
58
INSERT INTO EMP VALUES ('Sam', 1200, 16, '(1,1)');
59
INSERT INTO EMP VALUES ('Claire', 5000, 32, '(1,2)');
60
INSERT INTO EMP VALUES ('Andy', -1000, 2, '(1,3)');
61
INSERT INTO EMP VALUES ('Bill', 4200, 36, '(2,1)');
62
INSERT INTO EMP VALUES ('Ginger', 4800, 30, '(2,4)');
64
-- the argument of a function can also be a tuple. For instance,
65
-- double_salary takes a tuple of the EMP table
67
CREATE FUNCTION double_salary(EMP) RETURNS integer
68
AS 'SELECT $1.salary * 2 AS salary' LANGUAGE SQL;
70
SELECT name, double_salary(EMP) AS dream
72
WHERE EMP.cubicle ~= '(2,1)'::point;
74
-- the return value of a function can also be a tuple. However, make sure
75
-- that the expressions in the target list is in the same order as the
78
CREATE FUNCTION new_emp() RETURNS EMP
79
AS 'SELECT ''None''::text AS name,
82
''(2,2)''::point AS cubicle'
85
-- you can then project a column out of resulting the tuple by using the
86
-- "function notation" for projection columns. (ie. bar(foo) is equivalent
87
-- to foo.bar) Note that we don't support new_emp().name at this moment.
89
SELECT name(new_emp()) AS nobody;
91
-- let's try one more function that returns tuples
92
CREATE FUNCTION high_pay() RETURNS setof EMP
93
AS 'SELECT * FROM EMP where salary > 1500'
96
SELECT name(high_pay()) AS overpaid;
99
-----------------------------
100
-- Creating SQL Functions with multiple SQL statements
101
-- you can also create functions that do more than just a SELECT.
103
-- 14MAR99 Clark Evans: Does not quite work, commented out for now.
105
-----------------------------
107
-- you may have noticed that Andy has a negative salary. We'll create a
108
-- function that removes employees with negative salaries.
110
-- SELECT * FROM EMP;
112
-- CREATE FUNCTION clean_EMP () RETURNS integer
113
-- AS 'DELETE FROM EMP WHERE EMP.salary <= 0;
114
-- SELECT 1 AS ignore_this'
117
-- SELECT clean_EMP();
119
-- SELECT * FROM EMP;
122
-----------------------------
123
-- Creating C Functions
124
-- in addition to SQL functions, you can also create C functions.
125
-- See funcs.c for the definition of the C functions.
126
-----------------------------
128
CREATE FUNCTION add_one(integer) RETURNS integer
129
AS '_OBJWD_/funcs' LANGUAGE C;
131
CREATE FUNCTION makepoint(point, point) RETURNS point
132
AS '_OBJWD_/funcs' LANGUAGE C;
134
CREATE FUNCTION copytext(text) RETURNS text
135
AS '_OBJWD_/funcs' LANGUAGE C;
137
CREATE FUNCTION c_overpaid(EMP, integer) RETURNS boolean
138
AS '_OBJWD_/funcs' LANGUAGE C;
140
SELECT add_one(3) AS four;
142
SELECT makepoint('(1,2)'::point, '(3,4)'::point ) AS newpoint;
144
SELECT copytext('hello world!');
146
SELECT name, c_overpaid(EMP, 1500) AS overpaid
148
WHERE name = 'Bill' or name = 'Sam';
150
-- remove functions that were created in this file
152
DROP FUNCTION c_overpaid(EMP, integer);
153
DROP FUNCTION copytext(text);
154
DROP FUNCTION makepoint(point, point);
155
DROP FUNCTION add_one(integer);
156
--DROP FUNCTION clean_EMP();
157
DROP FUNCTION high_pay();
158
DROP FUNCTION new_emp();
159
DROP FUNCTION add_em(integer, integer);
161
DROP FUNCTION double_salary(EMP);