intadd_one(intarg);

char16*concat16(char16*arg1,char16*arg2);

Point*makepoint(Point*pointx,Point*pointy);

text*copytext(text*t);

boolc_overpaid(TUPLEt,/* the current instance of EMP */

int4limit);

boolc_overpaid(TupleTableSlot*t,/* the current instance of EMP */

int4limit);

returnarg+1;

}

concat16(char16*arg1,char16*arg2)

makepoint(Point*pointx,Point*pointy)

{

char16*new_c16= (char16*)palloc(sizeof(char16));

Point*new_point= (Point*)palloc(sizeof(Point));

MemSet(new_c16,0,sizeof(char16));

strncpy((char*)new_c16, (char*)arg1,16);

return (char16*) (strncat((char*)new_c16, (char*)arg2,16));

new_point->x=pointx->x;

new_point->y=pointy->y;

returnnew_point;

}

}

c_overpaid(TUPLEt,/* the current instance of EMP */

c_overpaid(TupleTableSlot*t,/* the current instance of EMP */

int4limit)

{

boolisnull= false;

---------------------------------------------------------------------------

--

-- funcs.sql-

-- Tutorial on using functions in POSTGRES.

-- 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 $

-- $Id: funcs.source,v 1.3 1999/03/14 15:22:15 momjian Exp $

--

---------------------------------------------------------------------------

nametext,

salaryint4,

ageint4,

);

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');

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

SELECT name, double_salary(EMP) AS dream

FROM EMP

WHERE EMP.dept= 'toy';

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,

\'none\'::char16 ASdept'

1000 AS salary,

25 AS age,

\'(2,2)\'::point AScubicle'

LANGUAGE 'sql';

-- you can then project a column out of resulting the tuple by using the

-----------------------------

-- 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;

--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.

--SeeC-code/funcs.c for the definition of the C functions.

--See funcs.c for the definition of the C functions.

-----------------------------

CREATE FUNCTION add_one(int4) RETURNS int4

AS '_OBJWD_/funcs.so' LANGUAGE 'c';

CREATE FUNCTIONconcat16(char16, char16) RETURNSchar16

CREATE FUNCTIONmakepoint(point, point) RETURNSpoint

AS '_OBJWD_/funcs.so' LANGUAGE 'c';

CREATE FUNCTION copytext(text) RETURNS text

SELECT add_one(3) AS four;

SELECTconcat16('abc', 'xyz') ASnewchar16;

SELECTmakepoint('(1,2)'::point, '(3,4)'::point) ASnewpoint;

SELECT copytext('hello world!');

DROP FUNCTION c_overpaid(EMP, int4);

DROP FUNCTION copytext(text);

DROP FUNCTIONconcat16(char16,char16);

DROP FUNCTIONmakepoint(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();