1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
---------------------------------------------------------------------------
--
-- funcs.sql-
-- Tutorial on using functions in POSTGRES.
--
--
-- Copyright (c) 1994-5, Regents of the University of California
--
-- $PostgreSQL: pgsql/src/tutorial/funcs.source,v 1.7 2003/11/29 22:41:33 pgsql Exp $
--
---------------------------------------------------------------------------
-----------------------------
-- Creating SQL Functions on Base Types
-- a CREATE FUNCTION statement lets you create a new function that
-- can be used in expressions (in SELECT, INSERT, etc.). We will start
-- with functions that return values of base types.
-----------------------------
--
-- let's create a simple SQL function that takes no arguments and
-- returns 1
CREATE FUNCTION one() RETURNS int4
AS 'SELECT 1 as ONE' LANGUAGE 'sql';
--
-- functions can be used in any expressions (eg. in the target list or
-- qualifications)
SELECT one() AS answer;
--
-- here's how you create a function that takes arguments. The following
-- function returns the sum of its two arguments:
CREATE FUNCTION add_em(int4, int4) RETURNS int4
AS 'SELECT $1 + $2' LANGUAGE 'sql';
SELECT add_em(1, 2) AS answer;
-----------------------------
-- Creating SQL Functions on Composite Types
-- it is also possible to create functions that return values of
-- composite types.
-----------------------------
-- before we create more sophisticated functions, let's populate an EMP
-- table
CREATE TABLE EMP (
name text,
salary int4,
age int4,
cubicle point
);
INSERT INTO EMP VALUES ('Sam', 1200, 16, '(1,1)');
INSERT INTO EMP VALUES ('Claire', 5000, 32, '(1,2)');
INSERT INTO EMP VALUES ('Andy', -1000, 2, '(1,3)');
INSERT INTO EMP VALUES ('Bill', 4200, 36, '(2,1)');
INSERT INTO EMP VALUES ('Ginger', 4800, 30, '(2,4)');
-- the argument of a function can also be a tuple. For instance,
-- double_salary takes a tuple of the EMP table
CREATE FUNCTION double_salary(EMP) RETURNS int4
AS 'SELECT $1.salary * 2 AS salary' LANGUAGE 'sql';
SELECT name, double_salary(EMP) AS dream
FROM EMP
WHERE EMP.cubicle ~= '(2,1)'::point;
-- the return value of a function can also be a tuple. However, make sure
-- that the expressions in the target list is in the same order as the
-- columns of EMP.
CREATE FUNCTION new_emp() RETURNS EMP
AS 'SELECT \'None\'::text AS name,
1000 AS salary,
25 AS age,
\'(2,2)\'::point AS cubicle'
LANGUAGE 'sql';
-- you can then project a column out of resulting the tuple by using the
-- "function notation" for projection columns. (ie. bar(foo) is equivalent
-- to foo.bar) Note that we don't support new_emp().name at this moment.
SELECT name(new_emp()) AS nobody;
-- let's try one more function that returns tuples
CREATE FUNCTION high_pay() RETURNS setof EMP
AS 'SELECT * FROM EMP where salary > 1500'
LANGUAGE 'sql';
SELECT name(high_pay()) AS overpaid;
-----------------------------
-- Creating SQL Functions with multiple SQL statements
-- you can also create functions that do more than just a SELECT.
--
-- 14MAR99 Clark Evans: Does not quite work, commented out for now.
--
-----------------------------
-- you may have noticed that Andy has a negative salary. We'll create a
-- function that removes employees with negative salaries.
--
-- SELECT * FROM EMP;
--
-- CREATE FUNCTION clean_EMP () RETURNS int4
-- AS 'DELETE FROM EMP WHERE EMP.salary <= 0\;
-- SELECT 1 AS ignore_this'
-- LANGUAGE 'sql';
--
-- SELECT clean_EMP();
--
-- SELECT * FROM EMP;
-----------------------------
-- Creating C Functions
-- in addition to SQL functions, you can also create C functions.
-- See funcs.c for the definition of the C functions.
-----------------------------
CREATE FUNCTION add_one(int4) RETURNS int4
AS '_OBJWD_/funcs' LANGUAGE 'c';
CREATE FUNCTION makepoint(point, point) RETURNS point
AS '_OBJWD_/funcs' LANGUAGE 'c';
CREATE FUNCTION copytext(text) RETURNS text
AS '_OBJWD_/funcs' LANGUAGE 'c';
CREATE FUNCTION c_overpaid(EMP, int4) RETURNS bool
AS '_OBJWD_/funcs' LANGUAGE 'c';
SELECT add_one(3) AS four;
SELECT makepoint('(1,2)'::point, '(3,4)'::point ) AS newpoint;
SELECT copytext('hello world!');
SELECT name, c_overpaid(EMP, 1500) AS overpaid
FROM EMP
WHERE name = 'Bill' or name = 'Sam';
-- remove functions that were created in this file
DROP FUNCTION c_overpaid(EMP, int4);
DROP FUNCTION copytext(text);
DROP FUNCTION makepoint(point,point);
DROP FUNCTION add_one(int4);
--DROP FUNCTION clean_EMP();
DROP FUNCTION high_pay();
DROP FUNCTION new_emp();
DROP FUNCTION add_em(int4, int4);
DROP FUNCTION one();
DROP FUNCTION double_salary(EMP);
DROP TABLE EMP;
|
