src/tutorial/funcs.source

   1 ---------------------------------------------------------------------------
   2 --
   3 -- funcs.sql-
   4 --        Tutorial on using functions in POSTGRES.
   5 --
   6 --
   7 -- Copyright (c) 1994-5, Regents of the University of California
   8 --
   9 -- src/tutorial/funcs.source
  10 --
  11 ---------------------------------------------------------------------------
  12
  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 -----------------------------
  19
  20 --
  21 -- let's create a simple SQL function that takes no arguments and
  22 -- returns 1
  23
  24 CREATE FUNCTION one() RETURNS integer
  25    AS 'SELECT 1 as ONE' LANGUAGE SQL;
  26
  27 --
  28 -- functions can be used in any expressions (eg. in the target list or
  29 -- qualifications)
  30
  31 SELECT one() AS answer;
  32
  33 --
  34 -- here's how you create a function that takes arguments. The following
  35 -- function returns the sum of its two arguments:
  36
  37 CREATE FUNCTION add_em(integer, integer) RETURNS integer
  38    AS 'SELECT $1 + $2' LANGUAGE SQL;
  39
  40 SELECT add_em(1, 2) AS answer;
  41
  42 -----------------------------
  43 -- Creating SQL Functions on Composite Types
  44 --      it is also possible to create functions that return values of
  45 --      composite types.
  46 -----------------------------
  47
  48 -- before we create more sophisticated functions, let's populate an EMP
  49 -- table
  50
  51 CREATE TABLE EMP (
  52         name            text,
  53         salary          integer,
  54         age             integer,
  55         cubicle         point
  56 );
  57
  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)');
  63
  64 -- the argument of a function can also be a tuple. For instance,
  65 -- double_salary takes a tuple of the EMP table
  66
  67 CREATE FUNCTION double_salary(EMP) RETURNS integer
  68    AS 'SELECT $1.salary * 2 AS salary' LANGUAGE SQL;
  69
  70 SELECT name, double_salary(EMP) AS dream
  71 FROM EMP
  72 WHERE EMP.cubicle ~= '(2,1)'::point;
  73
  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
  76 -- columns of EMP.
  77
  78 CREATE FUNCTION new_emp() RETURNS EMP
  79    AS 'SELECT ''None''::text AS name,
  80                           1000 AS salary,
  81                           25 AS age,
  82                           ''(2,2)''::point AS cubicle'
  83    LANGUAGE SQL;
  84
  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.
  88
  89 SELECT name(new_emp()) AS nobody;
  90
  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'
  94    LANGUAGE SQL;
  95
  96 SELECT name(high_pay()) AS overpaid;
  97
  98
  99 -----------------------------
 100 -- Creating SQL Functions with multiple SQL statements
 101 --      you can also create functions that do more than just a SELECT.
 102 -----------------------------
 103
 104 -- you may have noticed that Andy has a negative salary. We'll create a
 105 -- function that removes employees with negative salaries.
 106
 107 SELECT * FROM EMP;
 108
 109 CREATE FUNCTION clean_EMP () RETURNS integer
 110    AS 'DELETE FROM EMP WHERE EMP.salary <= 0;
 111        SELECT 1 AS ignore_this'
 112    LANGUAGE SQL;
 113
 114 SELECT clean_EMP();
 115
 116 SELECT * FROM EMP;
 117
 118
 119 -----------------------------
 120 -- Creating C Functions
 121 --      in addition to SQL functions, you can also create C functions.
 122 --      See funcs.c for the definition of the C functions.
 123 -----------------------------
 124
 125 CREATE FUNCTION add_one(integer) RETURNS integer
 126    AS '_OBJWD_/funcs' LANGUAGE C;
 127
 128 CREATE FUNCTION makepoint(point, point) RETURNS point
 129    AS '_OBJWD_/funcs' LANGUAGE C;
 130
 131 CREATE FUNCTION copytext(text) RETURNS text
 132    AS '_OBJWD_/funcs' LANGUAGE C;
 133
 134 CREATE FUNCTION c_overpaid(EMP, integer) RETURNS boolean
 135    AS '_OBJWD_/funcs' LANGUAGE C;
 136
 137 SELECT add_one(3) AS four;
 138
 139 SELECT makepoint('(1,2)'::point, '(3,4)'::point ) AS newpoint;
 140
 141 SELECT copytext('hello world!');
 142
 143 SELECT name, c_overpaid(EMP, 1500) AS overpaid
 144 FROM EMP
 145 WHERE name = 'Bill' or name = 'Sam';
 146
 147 -- remove functions that were created in this file
 148
 149 DROP FUNCTION c_overpaid(EMP, integer);
 150 DROP FUNCTION copytext(text);
 151 DROP FUNCTION makepoint(point, point);
 152 DROP FUNCTION add_one(integer);
 153 --DROP FUNCTION clean_EMP();
 154 DROP FUNCTION high_pay();
 155 DROP FUNCTION new_emp();
 156 DROP FUNCTION add_em(integer, integer);
 157 DROP FUNCTION one();
 158 DROP FUNCTION double_salary(EMP);
 159
 160 DROP TABLE EMP;