---------------------------------------------------------------------------
--
-- funcs.sql-
--    Tutorial on using functions in POSTGRES.
--
--
-- Copyright (c) 1994-5, Regents of the University of California
--
-- $Id: funcs.source,v 1.2 1998/02/11 03:51:38 thomas Exp $
--
---------------------------------------------------------------------------

-- before we create more sophisticated functions, let's populate an EMP
-- table

CREATE TABLE EMP (
	name		text,
	salary		int4,
	age		int4,
	dept		char16
);

INSERT INTO EMP VALUES ('Sam', 1200, 16, 'toy');
INSERT INTO EMP VALUES ('Claire', 5000, 32, 'shoe');
INSERT INTO EMP VALUES ('Andy', -1000, 2, 'candy');
INSERT INTO EMP VALUES ('Bill', 4200, 36, 'shoe');
INSERT INTO EMP VALUES ('Ginger', 4800, 30, 'candy');

-- the argument of a function can also be a tuple. For instance, 
-- double_salary takes a tuple of the EMP table

-----------------------------
-- Creating C Functions
--	in addition to SQL functions, you can also create C functions. 
--	See C-code/funcs.c for the definition of the C functions.
-----------------------------

CREATE FUNCTION add_one(int4) RETURNS int4
   AS '/user1/grad/whtak/postgres/src/funcs.so' LANGUAGE 'c';

CREATE FUNCTION concat16(char16, char16) RETURNS char16
   AS '/user1/grad/whtak/postgres/src/funcs.so' LANGUAGE 'c';

CREATE FUNCTION copytext(text) RETURNS text
   AS '/user1/grad/whtak/postgres/src/funcs.so' LANGUAGE 'c';

CREATE FUNCTION c_overpaid(EMP, int4) RETURNS bool
   AS '/user1/grad/whtak/postgres/src/funcs.so' LANGUAGE 'c';

SELECT add_one(3) AS four;

SELECT concat16('abc', 'xyz') AS newchar16;

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 concat16(char16,char16);
DROP FUNCTION add_one(int4);

DROP TABLE EMP;