If you are color blind try the Color chooser Color chooser Color chooser
PL PGSQL Function List.
This page is just a list of plpgsql functions that have gathered up over the last few months. There are no real corkers in here and to be honest I am only using a minimal feature set in most of the functions. I have not reformatted them or tidied them up in any way. Some of them are only half finished and some are running away merrily as we speak. I dragged them from a file and just stuffed them in here. Use them at your peril.
DROP TRIGGER insert_job_trigger ON rss_jobs;
DROP FUNCTION insert_job_trigger();
CREATE FUNCTION insert_job_trigger() RETURNS OPAQUE AS '
DECLARE
counter int4;
var_user_id int4;
rec_keyword RECORD;
rec_job_id RECORD;
var_job_id int4;
var_counter int4;
var_counter2 int4;
var_temp int4;
BEGIN
select into counter count(*)
from jobs_to_be_indexed
where job_id = NEW.rss_job_id;
if counter = 0 then
insert into jobs_to_be_indexed(job_id) values(NEW.rss_job_id);
else
delete from job_search_index where job_id = NEW.rss_job_id;
update jobs_to_be_indexed set indexed = 0 where job_id = NEW.rss_job_id;
end if;
for rec_keyword IN select keyword from key_word_search where indexed = 1 loop
var_counter := 1;
select into var_job_id jtbi.job_id as job_id
from jobs_to_be_indexed as jtbi
where jtbi.job_id = NEW.rss_job_id
and jtbi.indexed = 0
and (
-- lower(NEW.description) like ''% '' || rec_keyword.keyword || '' %''
lower(NEW.title) like ''% '' || rec_keyword.keyword || '' %''
or lower(NEW.link) like ''% '' || rec_keyword.keyword || '' %''
or lower(NEW.feed_id) = rec_keyword.keyword
);
var_counter := var_counter + 1;
IF var_job_id IS NOT NULL THEN
select into var_temp insert_index(rec_keyword.keyword, var_job_id) as output;
END IF;
END loop;
-- We need to keep this update statement until the last because a job may need
-- to be indexed on more than one keyword.
update jobs_to_be_indexed
set indexed = 1
where job_id = NEW.rss_job_id
and indexed = 0;
return new;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER insert_job_trigger BEforE insert OR UPDATE ON rss_jobs
for EACH ROW EXECUTE PROCEDURE insert_job_trigger();
-----------------------------------------------------------------------------
DROP FUNCTION rss_full_indexer();
CREATE FUNCTION rss_full_indexer() RETURNS INTEGER AS '
DECLARE
var_counter INT4;
BEGIN
/*
-- This is a function that resets everything for the next full indexer run
-- We use this to carry out a full index of all the jobs with all the keywords.
--
*/
truncate table job_search_index;
update jobs_to_be_indexed set indexed = 0;
update key_word_search set indexed = 1 where indexed = 0;
select into var_counter rss_indexer() as output;
RETURN var_counter;
END;
' LANGUAGE 'plpgsql';
----------------------------------------------------------------------------------
DROP FUNCTION clean_keywords();
CREATE FUNCTION clean_keywords() RETURNS INTEGER AS '
DECLARE
rec_common RECORD;
BEGIN
-- This is a cleaner to remove any common words that may have excaped the filter etc.
for rec_common IN select word_name from common_words
loop
delete from key_word_search where keyword = rec_common.word_name;
delete from job_search_index where keyword = rec_common.word_name;
end loop;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
----------------------------------------------------------------------------------
DROP FUNCTION cleanup();
CREATE FUNCTION cleanup() RETURNS INTEGER AS '
DECLARE
rec_inactive RECORD;
var_counter int4;
var_time int4;
BEGIN
-- This is a cleaner to stop old jobs being indexed etc.
var_time := (30*24*3600);
var_counter := 0;
for rec_inactive IN
select rss_job_id
from rss_jobs
where extract(epoch from date_time) < ( extract( epoch from now() ) - var_time )
loop
var_counter := var_counter + 1;
update rss_jobs
set active = 0
where rss_job_id = rec_inactive.rss_job_id;
delete from job_search_index
where job_id = rec_inactive.rss_job_id;
end loop;
RETURN var_counter;
END;
' LANGUAGE 'plpgsql';
----------------------------------------------------------------------------------
DROP FUNCTION rss_part_indexer();
CREATE FUNCTION rss_part_indexer() RETURNS INTEGER AS '
DECLARE
var_user_id int4;
rec_keyword RECORD;
rec_job_id RECORD;
var_job_id int4;
var_counter int4;
var_counter2 int4;
var_temp int4;
BEGIN
/*
*/
var_counter := 0;
--lock table jobs_to_be_indexed in EXCLUSIVE mode;
--lock table key_word_search in EXCLUSIVE mode;
--lock table rss_jobs in EXCLUSIVE mode;
--lock table job_search_index in EXCLUSIVE mode;
update key_word_search set indexed = 1 where indexed = 0;
for rec_keyword IN select keyword
from key_word_search
where indexed = 1
and length(keyword) > 5
loop
-- Find all the jobs that have the keyword and are not already indexed on that
-- keyword.
-- Create a record of these then loop
-- Using the EXCEPT keyword in this sql statement improves efficiency.
for rec_job_id IN
select j.rss_job_id as job_id
from rss_jobs as j , jobs_to_be_indexed as jtbi
where j.rss_job_id = jtbi.job_id
and jtbi.indexed = 1
and j.active = 1
and (
lower(j.description) like ''% '' || rec_keyword.keyword || '' %''
or lower(j.title) like ''% '' || rec_keyword.keyword || '' %''
or lower(j.link) like ''% '' || rec_keyword.keyword || '' %''
or lower(feed_id) = rec_keyword.keyword
)
EXCEPT
select rj.rss_job_id as job_id
from rss_jobs as rj, job_search_index as jsi
where rj.rss_job_id = jsi.job_id
and jsi.keyword = rec_keyword.keyword
loop
var_counter := var_counter + 1;
IF rec_job_id.job_id IS NOT NULL THEN
select into var_temp insert_index(rec_keyword.keyword, rec_job_id.job_id) as output;
--insert into job_search_index(job_id , keyword )
-- values (rec_job_id.job_id , rec_keyword.keyword );
-- j.description ~* ''(^| )rec_keyword.keyword( |$)''
--or j.title ~* ''(^| )rec_keyword.keyword( |$)''
--or lower(j.feed_id) = rec_keyword.keyword
END IF;
END loop;
END loop;
update jobs_to_be_indexed
set indexed = 1
where indexed = 0;
RETURN var_counter;
END;
' LANGUAGE 'plpgsql';
------------------------------------------------------------------------------------
DROP FUNCTION rss_indexer();
CREATE FUNCTION rss_indexer() RETURNS INTEGER AS '
DECLARE
var_user_id int4;
rec_keyword RECORD;
rec_job_id RECORD;
var_job_id int4;
var_counter int4;
var_counter2 int4;
var_temp int4;
BEGIN
/*
-- This is the indexer. Only when a job is added will this function be called,
*/
var_counter := 0;
--lock table jobs_to_be_indexed in EXCLUSIVE mode;
--lock table key_word_search in EXCLUSIVE mode;
--lock table rss_jobs in EXCLUSIVE mode;
lock table job_search_index in EXCLUSIVE mode;
-- We are selecting all keywords with a value of 1
-- because we are indexing each job as it comes in.
-- The reindexer function carries out a complete
-- reindex of everything
/* for rec_keyword IN select keyword from key_word_search where indexed = 1 loop
-- Find all the jobs that have the keyword and are not already indexed on that
-- keyword.
-- Create a record of these then loop
-- Using the EXCEPT keyword in this sql statement improves efficiency.
var_counter := 1;
for rec_job_id IN select j.rss_job_id as job_id
from rss_jobs as j , jobs_to_be_indexed as jtbi
where j.rss_job_id = jtbi.job_id
and jtbi.indexed = 0
and (
lower(j.description) like ''% '' || rec_keyword.keyword || ''%''
or lower(j.description) like ''%'' || rec_keyword.keyword || '' %''
or lower(j.title) like ''% '' || rec_keyword.keyword || ''%''
or lower(j.title) like ''%'' || rec_keyword.keyword || '' %''
or lower(j.link) like ''% '' || rec_keyword.keyword || ''%''
or lower(j.link) like ''%'' || rec_keyword.keyword || '' %''
or lower(j.feed_id) = rec_keyword.keyword
)
EXCEPT
select rj.rss_job_id as job_id
from rss_jobs as rj, job_search_index as jsi
where rj.rss_job_id = jsi.job_id
and jsi.keyword = rec_keyword.keyword loop
var_counter := var_counter + 1;
IF rec_job_id.job_id IS NOT NULL THEN
select into var_temp insert_index(rec_keyword.keyword, rec_job_id.job_id) as output;
--insert_index(rec_keyword.keyword, rec_job_id.job_id);
-- insert into job_search_index(job_id , keyword )
-- values (rec_job_id.job_id , rec_keyword.keyword );
END IF;
END loop;
END loop;
-- We need to keep this update statement until the last because a job mayneed
-- to be indexed on more than one keyword.
update jobs_to_be_indexed
set indexed = 1
where indexed = 0;
*/
RETURN var_counter;
END;
' LANGUAGE 'plpgsql';
-------------------------------------------------------------------------------------------
DROP FUNCTION insert_rss_job( varchar , varchar, text, varchar, integer );
CREATE FUNCTION insert_rss_job( varchar , varchar, text, varchar, integer ) RETURNS INTEGER AS '
DECLARE
var_title alias for $1;
var_link alias for $2;
var_description alias for $3;
var_feed_id alias for $4;
var_category alias for $5;
var_count int4;
var_count2 int4;
BEGIN
select into var_count count(*)
from rss_jobs
where link = var_link;
if var_count = 0 then
insert into rss_jobs( title, link, description, feed_id, category)
values ( var_title, var_link, var_description, var_feed_id, var_category);
end if;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
------------------------------------------------------------------------------------------
DROP TRIGGER add_agency ON agency;
DROP FUNCTION add_agency();
CREATE FUNCTION add_agency() RETURNS OPAQUE AS '
DECLARE
p1_counter int4;
BEGIN
SELECT into p1_counter count(*) FROM agency_details WHERE agency_id = NEW.agency_id;
IF p1_counter = 0 THEN
-- I give every agency a starting score of 10. The ranking table must be populated for
-- the details to be displayed.
insert into ranking( user_id , agency_id ) values ( 1 , NEW.agency_id);
insert into agency_details( agency_id , branch_name ) values (NEW.agency_id , NEW.agency_name);
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER add_agency BEforE insert OR UPDATE ON agency
for EACH ROW EXECUTE PROCEDURE add_agency();
---------------------------------------------------------------------
--
--
--
--
--
--
DROP TRIGGER add_user ON users;
DROP FUNCTION add_user();
CREATE FUNCTION add_user() RETURNS OPAQUE AS '
DECLARE
parent_counter int4;
BEGIN
SELECT into parent_counter count(*) FROM user_options WHERE user_id = NEW.user_id;
IF parent_counter = 0 THEN
insert into user_options(user_id) values ( NEW.user_id );
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER add_user BEforE insert OR UPDATE ON users
for EACH ROW EXECUTE PROCEDURE add_user();
---------------------------------------------------------------------
----
----
----
----
----
DROP TRIGGER add_new_contract ON contracts;
DROP FUNCTION add_new_contract();
CREATE FUNCTION add_new_contract() RETURNS OPAQUE AS '
DECLARE
parent_counter int4;
BEGIN
update users set confirmed = \'contract\' where confirmed = \'true\';
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER add_new_contract BEforE insert OR UPDATE ON contracts
for EACH ROW EXECUTE PROCEDURE add_new_contract();
---------------------------------------------------------------------
--
DROP TRIGGER session_check ON sessions;
DROP FUNCTION session_check();
CREATE FUNCTION session_check() RETURNS OPAQUE AS '
DECLARE
my_date_time timestamp DEFAULT now();
parent_counter int4;
BEGIN
SELECT into parent_counter count(*) FROM sessions WHERE id = NEW.id;
IF parent_counter = 0 THEN
DELETE FROM sessions
where id != NEW.id
and EXTRACT(EPOCH from date_time) < EXTRACT(EPOCH from my_date_time) - 360;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER session_check BEforE insert OR UPDATE ON sessions
for EACH ROW EXECUTE PROCEDURE session_check();
---------------------------------------------------------------------
--
--
------------------------------------------------------------------------
--
DROP FUNCTION insert_thread_message(integer , integer , varchar , integer );
CREATE FUNCTION insert_thread_message(integer , integer , varchar , integer ) RETURNS INTEGER AS '
DECLARE
var_user_id alias for $1;
var_agency_id alias for $2;
var_body alias for $3;
var_parent_message_id alias for $4;
message_subject varchar;
dummy_var int4;
BEGIN
SELECT into message_subject subject
FROM parent_messages
WHERE parent_message_id = var_parent_message_id
AND agency_id = var_agency_id;
IF message_subject IS NOT NULL THEN
insert into messages(user_id , body ) values (var_user_id , var_body );
insert into join_messages (parent_message_id , message_id ) values ( var_parent_message_id , currval(\'message_pk\') );
RETURN 1;
END IF;
RETURN -1;
END;
' LANGUAGE 'plpgsql';
--------------------------------------------------------------
DROP FUNCTION insert_parent_message(integer , integer , varchar , varchar );
CREATE FUNCTION insert_parent_message(integer, integer , varchar , varchar ) RETURNS INTEGER AS '
DECLARE
var_user_id alias for $1;
var_agency_id alias for $2;
var_subject alias for $3;
var_body alias for $4;
parent_counter int4;
message_counter int4;
dummy_var int4;
BEGIN
insert into parent_messages (subject , agency_id ) values ( var_subject , var_agency_id );
insert into messages(user_id , body ) values (var_user_id , var_body );
insert into join_messages (parent_message_id , message_id ) values ( currval(\'parent_message_pk\') , currval(\'message_pk\') );
RETURN 1 ;
END;
' LANGUAGE 'plpgsql';
-------------------------------------------------------------
--DROP FUNCTION insert_user_job(text , integer , varchar , varchar, varchar , varchar, varchar , varchar , integer );
DROP FUNCTION
insert_user_job(text , integer , varchar , varchar, varchar , varchar, varchar , varchar, integer , integer );
CREATE FUNCTION
insert_user_job(text , integer , varchar , varchar, varchar , varchar, varchar , varchar, integer , integer )
RETURNS INTEGER AS '
DECLARE
var_description alias for $1;
var_salary alias for $2;
var_role alias for $3;
var_location alias for $4;
var_contract alias for $5;
var_contact alias for $6;
var_telephone alias for $7;
var_mail alias for $8;
var_country alias for $9;
var_user_id alias for $10;
var_counter int4;
var_rss_jobs_pk int4;
BEGIN
insert into jobs( description , salary ,role,
location, country_id, contract,
contact, telephone, mail)
values ( var_description, var_salary,
var_role, var_location, var_country,
var_contract, var_contact, var_telephone,
var_mail);
select into var_rss_jobs_pk currval(\'rss_jobs_pk\') as output;
insert into user_jobs(job_id , user_id)
values ( var_rss_jobs_pk, var_user_id);
insert into rss_jobs ( rss_job_id, title, link, description, feed_id, category )
values ( var_rss_jobs_pk,
var_role,
''http://www.uklug.co.uk/cgi-bin/display_job?job_id='' || var_rss_jobs_pk,
var_description,
''www.uklug.co.uk'',
0);
RETURN 1;
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------
DROP FUNCTION accepted_contract( integer );
CREATE FUNCTION accepted_contract( integer ) RETURNS INTEGER AS '
DECLARE
var_user_id alias for $1;
var_exists int4;
var_max_contract_id int4;
BEGIN
-- -- We need to see if the user has already read the
-- -- latest contract.
-- --
select into var_exists max(co.contract_id)
from user_contracts as uc , contracts as co
where uc.user_id = var_user_id
and uc.contract_id = co.contract_id
group by co.contract_id;
-- select max(co.contract_id)
-- from user_contracts as uc , contracts as co
-- where uc.user_id = 1
-- and uc.contract_id = co.contract_id
-- group by co.contract_id;
--
-- select max(contract_id) from contracts group by contract_id;
-- If they have read the latest contract we
IF var_exists IS NOT NULL THEN
update users
set confirmed = ''true''
where user_id = var_user_id;
END IF;
IF var_exists IS NULL THEN
select into var_max_contract_id max(contract_id)
from contracts
group by contract_id;
insert into user_contracts( contract_id , user_id )
values ( var_max_contract_id , var_user_id);
update users
set confirmed = ''true''
where user_id = var_user_id;
END IF;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------
--
DROP FUNCTION insert_index(varchar , integer );
CREATE FUNCTION insert_index( varchar , integer ) RETURNS INTEGER AS '
DECLARE
var_keyword alias for $1;
var_job_id alias for $2;
var_exists int4;
BEGIN
SELECT into var_exists job_id
FROM job_search_index
WHERE keyword = var_keyword
AND job_id = var_job_id;
IF var_exists is null THEN
insert into job_search_index ( keyword , job_id )
values ( var_keyword, var_job_id);
END IF;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
-------------------------------------------------------------
CREATE OR REPLACE FUNCTION
insert_agency(varchar, varchar, varchar, varchar, varchar, varchar,
varchar, varchar, varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
DECLARE
var_agency_name alias for $1;
var_branch_name alias for $2;
var_building_number alias for $3;
var_building_name alias for $4;
var_street alias for $5;
var_city alias for $6;
var_postcode alias for $7;
var_country alias for $8;
var_mail alias for $9;
var_telephone alias for $10;
var_fax alias for $11;
var_website alias for $12;
var_agency_id int4;
BEGIN
select into var_agency_id agency_id
from agency
where agency_name = var_agency_name;
IF var_agency_id IS NULL THEN
insert into agency( agency_name ) values (var_agency_name);
select into var_agency_id agency_id
from agency
where agency_name = var_agency_name;
update agency_details
set branch_name = var_branch_name,
building_number = var_building_number,
building_name = var_building_name,
street = var_street,
city = var_city,
postcode = var_postcode,
country = var_country,
telephone_number = var_telephone,
fax = var_fax,
email = var_mail,
agency_url = var_website
where agency_id = var_agency_id;
END IF;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
-------------------------------------------------------------
DROP FUNCTION indexer( );
CREATE FUNCTION indexer( ) RETURNS INTEGER AS '
DECLARE
var_user_id int4;
rec_keyword RECORD;
rec_job_id RECORD;
var_job_id int4;
var_counter int4;
var_counter2 int4;
BEGIN
/*
-- This is the indexer. Only when a job is added will this function be called,
--
*/
var_counter := 0;
lock table jobs_to_be_indexed in EXCLUSIVE mode;
lock table key_word_search in EXCLUSIVE mode;
lock table jobs in EXCLUSIVE mode;
lock table job_search_index in EXCLUSIVE mode;
-- We are selecting all keywords with a value of 1
-- because we are indexing each job as it comes in.
-- The reindexer function carries out a complete
-- reindex of everything
for rec_keyword IN select keyword from key_word_search where indexed = 1 loop
-- Find all the jobs that have the keyword and are not already indexed on that
-- keyword.
-- Create a record of these then loop
-- Using the EXCEPT keyword in this sql statement improves efficiency.
for rec_job_id IN select j.job_id as job_id
from jobs as j , jobs_to_be_indexed as jtbi
where j.job_id = jtbi.job_id
and jtbi.indexed = 0
and ( lower(j.description) like ''%'' || rec_keyword.keyword ||''%''
or lower(j.role) like ''%'' || rec_keyword.keyword ||''%''
or lower(j.location) like ''%'' || rec_keyword.keyword ||''%'' )
EXCEPT
select j.job_id as job_id
from jobs as j , job_search_index as jsi
where j.job_id = jsi.job_id
and jsi.keyword = rec_keyword.keyword
loop
var_counter := var_counter + 1;
IF rec_job_id.job_id IS NOT NULL THEN
insert into job_search_index(job_id , keyword )
values (rec_job_id.job_id , rec_keyword.keyword );
END IF;
END loop;
END loop;
/* We need to keep this update statement until the last because a job mayneed
to be indexed on more than one keyword.
*/
update jobs_to_be_indexed
set indexed = 1
where indexed = 0;
RETURN var_counter;
END;
' LANGUAGE 'plpgsql';
-------------------------------------------------------------
DROP FUNCTION my_time();
CREATE FUNCTION my_time() RETURNS TIMESTAMP AS '
DECLARE
BEGIN
RETURN NOW();
END;
' LANGUAGE 'plpgsql';
------------------------------------------------------
DROP FUNCTION reset_indexer();
CREATE FUNCTION reset_indexer() RETURNS INTEGER AS '
DECLARE
var_counter INT4;
BEGIN
/*
-- This is a function that resets everything for the next full indexer run
-- We use this to carry out a full index of all the jobs with all the keywords.
--
*/
delete from job_search_index;
update jobs_to_be_indexed set indexed = 0;
update key_word_search set indexed = 1 where indexed = 0;
select into var_counter indexer() as output;
RETURN var_counter;
END;
' LANGUAGE 'plpgsql';
----------------------------------------------------------------------------------