Some great resources and notes for working with PostgeSQL functions (stored procedures)

https://www.youtube.com/watch?v=bLi4zhMey3Q
http://www.postgresqltutorial.com/postgresql-insert/

Data Types:

http://www.postgresql.org/docs/9.2/static/functions-datetime.html

CREATE TABLE new_book (
id serial,
title text,
author text
);

CREATE OR REPLACE FUNCTION ins_book(p_title varchar, p_author text)
RETURNS integer AS
$$
INSERT INTO new_book(title, author) VALUES($1, $2) -- Could use $1, $2 here too
RETURNING id;
$$
LANGUAGE 'sql' VOLATILE;

SELECT ins_book('Twilight', 'Stephanie Meyer') AS book_id;

CREATE OR REPLACE FUNCTION upd_book(log_id integer, p_title text, p_author text)
RETURNS void AS
$$
UPDATE new_book
SET title = $2,
author = $3
WHERE id = $1;
$$
LANGUAGE 'sql' VOLATILE;

SELECT upd_book(1, 'Twilight 2', 'Stephanie Meyer');

-- Three common approaches of returning sets
-- RETURNS TABLE
-- OUT parameters
-- Composite data type

CREATE OR REPLACE FUNCTION ret_books(p_title varchar)
RETURNS TABLE (id int, title text, author text) AS
$$
SELECT id, title, author
FROM new_book
WHERE title like p_title; -- Could use $1 here too
$$
LANGUAGE 'sql' STABLE;

SELECT * FROM ret_books('%Twilight%');

CREATE OR REPLACE FUNCTION ret_books_out(p_title varchar, OUT p_id int, OUT title text, OUT p_author text)
RETURNS SETOF record AS
$$
SELECT *
FROM new_book
WHERE title LIKE $1;
$$
LANGUAGE 'sql' STABLE;

SELECT * FROM ret_books_out('%Twilight%');

CREATE OR REPLACE FUNCTION ret_books_com(p_title text)
RETURNS SETOF new_book AS
$$
SELECT *
FROM new_book
WHERE title LIKE $1;
$$
LANGUAGE 'sql' STABLE;

SELECT * FROM ret_books_com('%Twilight%');

-- Using PL/pgSQL allows functions to use local variables 
-- with DECLARE, must use BEGIN..END block

CREATE OR REPLACE FUNCTION ret_books_plsql()
RETURNS TABLE (id int, title text, author text) AS
$$
BEGIN
RETURN QUERY
SELECT *
FROM new_book;
END;
$$
LANGUAGE 'plpgsql' STABLE;

SELECT * FGROM ret_books_plsql();


-- Trigger Functions
-- Define the trigger function first. This function can be used on any table 
-- that has a title column. Trigger functions that change values of a row 
-- should only be called in the BEFORE event, because in the AFTER event, 
-- all updates to the NEW record will be ignored

CREATE OR REPLACE FUNCTION f_trig_books() RETURNS trigger AS
$$
BEGIN
NEW.title := upper(NEW.title);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

-- Next attach the trigger function to the appropriate trigger
-- The trigger will fire before the record is committed. 
-- PostgreSQL 9.0+ allows us to limit the firing of the trigger 
-- only if specified columns have changed

CREATE TRIGGER trig_books
BEFORE INSERT OR UPDATE OF title, author
ON new_book
FOR EACH ROW
EXECUTE PROCEDURE f_trig_books();

SELECT ins_book('Inferno 2', 'Dan Brown') AS new_id;

--Now see the new title in row that is all caps
SELECT * FROM new_book