funcs.source 4.53 KB
Newer Older
1 2 3
---------------------------------------------------------------------------
--
-- funcs.sql-
4
--	  Tutorial on using functions in POSTGRES.
5 6 7 8
--
--
-- Copyright (c) 1994-5, Regents of the University of California
--
9
-- $Id: funcs.source,v 1.6 2003/10/26 04:51:51 momjian Exp $
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
--
---------------------------------------------------------------------------

-----------------------------
-- 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,
55
	cubicle		point
56 57
);

58 59 60 61 62
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)');
63 64 65 66 67 68 69 70 71

-- 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
72
WHERE EMP.cubicle ~= '(2,1)'::point;
73 74 75 76 77 78 79

-- 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,
80 81 82
			  1000 AS salary,
			  25 AS age,
			  \'(2,2)\'::point AS cubicle'
83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
   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.
102 103 104
--
-- 14MAR99 Clark Evans: Does not quite work, commented out for now.
--
105 106 107 108
-----------------------------

-- you may have noticed that Andy has a negative salary. We'll create a
-- function that removes employees with negative salaries.
109 110 111 112 113 114 115 116 117 118 119
--
-- 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;
120 121 122 123 124


-----------------------------
-- Creating C Functions
--	in addition to SQL functions, you can also create C functions. 
125
--	See funcs.c for the definition of the C functions.
126 127 128
-----------------------------

CREATE FUNCTION add_one(int4) RETURNS int4
129
   AS '_OBJWD_/funcs' LANGUAGE 'c';
130

131
CREATE FUNCTION makepoint(point, point) RETURNS point
132
   AS '_OBJWD_/funcs' LANGUAGE 'c';
133 134

CREATE FUNCTION copytext(text) RETURNS text
135
   AS '_OBJWD_/funcs' LANGUAGE 'c';
136 137

CREATE FUNCTION c_overpaid(EMP, int4) RETURNS bool
138
   AS '_OBJWD_/funcs' LANGUAGE 'c';
139 140 141

SELECT add_one(3) AS four;

142
SELECT makepoint('(1,2)'::point, '(3,4)'::point ) AS newpoint;
143 144 145 146 147 148 149 150 151

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

152 153
DROP FUNCTION c_overpaid(EMP, int4);
DROP FUNCTION copytext(text);
154
DROP FUNCTION makepoint(point,point);
155
DROP FUNCTION add_one(int4);
156
--DROP FUNCTION clean_EMP();
157
DROP FUNCTION high_pay();
158 159
DROP FUNCTION new_emp();
DROP FUNCTION add_em(int4, int4);
160
DROP FUNCTION one();
161
DROP FUNCTION double_salary(EMP);
162 163

DROP TABLE EMP;