/* PG Explorer Sample Database Please note : this sample is in no way functional for anything. Create the Database ------------------------------------------------------------------------------ Highlight the SQL and make sure that there is no transaction block by enabling the "Execute without transaction" on the toolbar. Highlighting SQL will execute the highlighted SQL Only. Thus this SQL being in a comment will have no influence ------------------------------------------------ Create Database pgexplorer; ------------------------------------------------ Create Language ------------------------------------------------------------------------------ NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB Make sure that the path to the file "/usr/lib/pgsql/plpgsql.so" is correct NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB PLEASE RUN THE FOLLOWING COMMAND ON YOUR SERVER ------------------------------------------------ createlang -L /usr/lib/pgsql plpgsql pgexplorer ------------------------------------------------ The Following Sample Functions were retrieved from www.brasileiro.net Thanks for www.brasileiro.net PLEASE EXECUTE EACH FUNCTION SEPERATELY BY HIGHLIGHTING IT WITH YOUR MOUSE */ /*----------------------------Stand Alown Functions----------------------------*/ CREATE FUNCTION add_one (integer) RETURNS integer AS ' BEGIN RETURN $1 + 1; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION concat_text (text, text) RETURNS text AS ' BEGIN RETURN $1 || $2; END; ' LANGUAGE 'plpgsql'; /* Simple functions to add/subtract dates. Prototypes: DATE_ADD(DATE,INT,PERIOD) DATE_SUB(DATE,INT,PERIOD) Where PERIOD is one of 'DAYS','MONTHS',or 'YEARS'. Examples: SELECT DATE_ADD('1999-12-31',1,'DAYS') will produce '2001-01-01' -- DATE_ADD, DATE_SUB date manipulation functions -- Copyright (C) 2001 Mark Steele (msteele@inet-interactif.com) -- -- This program is free software; you can redistribute it and/or -- modify it under the terms of the GNU General Public License -- as published by the Free Software Foundation; either version 2 -- of the License, or (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. */ CREATE FUNCTION date_add(date,int4,text) RETURNS DATE AS ' DECLARE var1 date; var2 text; BEGIN var2 = $2 || '' '' || $3; SELECT INTO var1 to_date($1 + var2::interval, ''YYYY-MM-DD''); RETURN var1; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION date_sub(date,int4,text) RETURNS DATE AS ' DECLARE var1 date; var2 text; BEGIN var2 = $2 || '' '' || $3; SELECT INTO var1 to_date($1 - var2::interval, ''YYYY-MM-DD''); RETURN var1; END; ' LANGUAGE 'plpgsql'; /*---------------------------- SEQUENCE ----------------------------*/ CREATE SEQUENCE "emp_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; /*---------------------------- TABLES ----------------------------*/ CREATE TABLE emp ( "emp_id" int4 DEFAULT nextval('emp_id_seq') NOT NULL, "empname" varchar(50), "salary" int4, "last_date" timestamp, "last_user" varchar(50), "date_entered" timestamp DEFAULT now() , PRIMARY KEY ("emp_id") ); CREATE SEQUENCE "employer_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; CREATE TABLE employer ( "empoyler_id" int4 DEFAULT nextval('employer_id_seq') NOT NULL, "empname" varchar(50), "date_entered" timestamp DEFAULT now() , PRIMARY KEY ("empoyler_id") ); /* New in Ver 1.01*/ CREATE RULE employer_name_check AS ON INSERT TO employer WHERE (new.empname ISNULL) DO INSTEAD NOTHING; CREATE TABLE emprelempl ( "empoyler_id" int4 NOT NULL, "emp_id" int4 NOT NULL, "date_set" timestamp DEFAULT now() , PRIMARY KEY("empoyler_id","emp_id") ); CREATE INDEX "empname_idx" ON "emp" ("empname"); /*---------------------------- FOREIGN KEY ----------------------------*/ ALTER TABLE "emprelempl" ADD CONSTRAINT "employer_fk" FOREIGN KEY (empoyler_id) REFERENCES employer(empoyler_id) MATCH FULL; ALTER TABLE "emprelempl" ADD CONSTRAINT "emplyee_fk" FOREIGN KEY (emp_id) REFERENCES emp(emp_id) MATCH FULL; COMMENT ON COLUMN "employer"."empoyler_id" IS 'Sequence Key'; COMMENT ON COLUMN "employer"."empname" IS 'Employee Name'; COMMENT ON TABLE "employer" IS 'Employer Table'; COMMENT ON COLUMN "emp"."emp_id" IS 'Sequence Key'; COMMENT ON COLUMN "emp"."salary" IS 'Employee Salary'; COMMENT ON TABLE "emp" IS 'Employee Table'; COMMENT ON TABLE "emprelempl" IS 'Employee Employer Relation'; /*---------------------------- TRIGGERS ----------------------------*/ /* This trigger ensures, that any time a row is inserted or updated in the table, the current user name and time are stamped into the row. And it ensures that an employees name is given and that the salary is a positive value. */ CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS ' BEGIN /* Check that empname and salary are given */ IF NEW.empname ISNULL THEN RAISE EXCEPTION ''empname cannot be NULL value''; END IF; IF NEW.salary ISNULL THEN RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; END IF; /* Who works for us when she must pay for? */ IF NEW.salary < 0 THEN RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; END IF; /* Remember who changed the payroll when */ NEW.last_date := now(); NEW.last_user := current_user; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); /* In this example, we take EMP (a table) and an integer as arguments to our function, which returns a boolean. If the "salary" field of the EMP table is NULL, we return "f". Otherwise we compare with that field with the integer passed to the function and return the boolean result of the comparison (t or f). */ CREATE FUNCTION c_overpaid (EMP, integer) RETURNS boolean AS ' DECLARE emprec ALIAS FOR $1; sallim ALIAS FOR $2; BEGIN IF emprec.salary ISNULL THEN RETURN ''f''; END IF; RETURN emprec.salary > sallim; END; ' LANGUAGE 'plpgsql'; /* This function returns the interval of time for which the two datetime ranges overlap. They are an expansion of the builtin overlaps(dt1,dt2,dt3,dt4) function. Contributed by Josh Berkus. -- This function returns the interval of time for which the two -- datetime ranges overlap. -- By Josh Berkus */ CREATE FUNCTION overlap_interval(DATETIME, DATETIME, DATETIME, DATETIME) RETURNS INTERVAL AS ' DECLARE begin1 ALIAS for $1; end1 ALIAS for $2; begin2 ALIAS for $3; end2 ALIAS for $4; overlap_amount INTERVAL; BEGIN /* test for overlap using the ovelap function. if not found, return 0 interval. */ IF NOT overlaps(begin1, end1, begin2, end2) THEN RETURN ''00:00:00''::INTERVAL; END IF; /* otherwise, test for the various forms of overlap */ IF begin1 < begin2 THEN IF end1 < end2 THEN overlap_amount := end1 - begin2; ELSE overlap_amount := end2 - begin2; END IF; ELSE IF end1 < end2 THEN overlap_amount := end1 - begin1; ELSE overlap_amount := end2 - begin1; END IF; END IF; RETURN overlap_amount; END; ' LANGUAGE 'plpgsql'; /* Given a book title (string starting with 'The' or 'A', case insensitive), then it returns the title with the prefix (A or The) move to the end of title. -- Function to massage book titles. -- Examples: Given 'The Book is Mine', 'Book is Mine, The' would be returned. -- Given 'A Super Guide', 'Super Guide, A' would be returned -- The function is case insensitive and will return the first letter of each word -- capitalised. -- By Patrick Giagnocavo, heavily modified by Roberto Mello (rmello@fslc.usu.edu) */ CREATE FUNCTION title_words (varchar) returns varchar AS ' DECLARE the_title alias for $1; temp_title varchar(255); cap_title varchar(255); trimmed_title varchar(255); BEGIN /* Capitalize each word in the title, then grab first 4 letters */ cap_title := initcap(the_title); temp_title := substring(cap_title from 1 for 4); if temp_title like ''The %'' then trimmed_title := trim (leading ''The '' from cap_title); trimmed_title := trimmed_title || '', The''; return trimmed_title; end if; if temp_title like ''A %'' then trimmed_title := trim (leading ''A '' from cap_title); trimmed_title := trimmed_title || '', A''; return trimmed_title; end if; return cap_title ; END; ' language 'plpgsql'; /* Requirment: To create a function in Postgres that will return nearest ZIP from a source ZIP. ZIP code table: */ CREATE TABLE "zips" ( "zip" varchar(10) NOT NULL, "state" char(2) DEFAULT 'NA' NOT NULL, "city" varchar(50) DEFAULT 'NA' NOT NULL, "location" point DEFAULT '(0,0)' NOT NULL, PRIMARY KEY ("zip") ); Insert into zips values('35005','AL','ADAMSVILLE','(86.959727,33.588437)'); Insert into zips values('35006','AL','ADGER','(87.167455,33.434277)'); Insert into zips values('35007','AL','KEYSTONE','(86.812861,33.236868)'); Insert into zips values('35010','AL','NEW SITE','(85.951086,32.941445)'); Insert into zips values('35014','AL','ALPINE','(86.208934,33.331165)'); Insert into zips values('35016','AL','ARAB','(86.489638,34.328339)'); /* ZIP code data provided from: http://ftp.census.gov/geo/www/gazetteer/places.html Install the Earthdistance from the contrib section of Postgresql. */ create function locate_providers (varchar(10),int2) returns text as ' declare source_zip alias for $1; offset alias for $2; source_point point; result record; begin /* Get the source point by zip */ select into source_point location from zips where zip = source_zip; if not found then return ''Error: ZIP not found''; end if; /* Get the providers one at a time by offset */ select into result trim(p.name)::text as name, trim(p.address1)::text as address, p.phone1::text as phone, trim(p.city)::text as city, round(source_point <@> location)::text as distance from zips z, providers p where source_point <@> location < 50 and z.zip = p.zip order by distance limit 1, offset; /* Return */ if found then return result.name || '', '' || result.address || '', '' || result.city || '', '' || result.phone || '', '' || result.distance || '' Miles''; end if; /* if nothing found */ return ''Error: Provider Not Found''; end; ' language 'plpgsql'; /* AUTHOR: Hano de la Rouviere LICENSE: Free DESCTIPTION: Convert a numeric amount into words. USAGE: select amount_to_words(12345.12); URL : http://www.pgexplorer.com GUI Tool for Postgres */ CREATE FUNCTION units_to_words(int4) RETURNS varchar(20) AS ' DECLARE result_str varchar(20); BEGIN SELECT INTO result_str CASE $1 WHEN 0 THEN '''' WHEN 1 THEN '' ONE'' WHEN 2 THEN '' TWO'' WHEN 3 THEN '' THREE'' WHEN 4 THEN '' FOUR'' WHEN 5 THEN '' FIVE'' WHEN 6 THEN '' SIX'' WHEN 7 THEN '' SEVEN'' WHEN 8 THEN '' EIGHT'' WHEN 9 THEN '' NINE'' WHEN 10 THEN '' TEN'' WHEN 11 THEN '' ELEVEN'' WHEN 12 THEN '' TWELVE'' WHEN 13 THEN '' THIRTEEN'' WHEN 14 THEN '' FOURTEEN'' WHEN 15 THEN '' FIFTEEN'' WHEN 16 THEN '' SIXTEEN'' WHEN 17 THEN '' SEVENTEEN'' WHEN 18 THEN '' EIGHTEEN'' WHEN 19 THEN '' NINETEEN'' END; RETURN result_str; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION amount_to_words(numeric(15,2)) RETURNS varchar(255) AS ' DECLARE amount ALIAS FOR $1; amount_str varchar(30); tri int4; bi int4; mi int4; th int4; hu int4; de int4; return_str varchar(255); BEGIN amount_str = to_char(amount, ''000000000000D00''); tri = substr(amount_str, 0, 3)::int4; bi = substr(amount_str, 2, 3)::int4; mi = substr(amount_str, 5, 3)::int4; th = substr(amount_str, 8, 3)::int4; hu = substr(amount_str, 11, 3)::int4; de = substr(amount_str, 15, 2)::int4; if tri > 0 then return_str = num_to_words(tri) || '' TRILLION ''; else return_str = ''''; END IF; if bi > 0 then return_str = return_str || num_to_words(bi) || '' BILLION ''; END IF; if mi > 0 then return_str = return_str || num_to_words(mi) || '' MILLION ''; END IF; if th > 0 then return_str = return_str || num_to_words(th) || '' THOUSAND ''; END IF; if hu > 0 then return_str = return_str || num_to_words(hu); END IF; IF return_str != '''' then return_str = return_str ||'' DOLLARS AND ''; else return_str = ''ZERO DOLLARS AND ''; END IF; if de > 0 then return_str = return_str || num_to_words(de); END IF; return_str = return_str || '' CENTS''; RETURN return_str; END; ' LANGUAGE 'plpgsql';