CREATE OR REPLACE FUNCTION fn_test(param_arg1 integer, param_arg2 text) RETURNS text AS $$ DECLARE var_a integer := 0; var_b text := 'test test test'; BEGIN RAISE NOTICE 'Pointless example to demonstrate a point'; RETURN var_b || ' - ' || CAST(param_arg1 As text) || ' - ' || param_arg2; END $$ LANGUAGE 'plpgsql' STABLE; SELECT fn_test(10, 'test'); CREATE OR REPLACE FUNCTION fnpgsql_get_peoplebylname_key(param_lname text) RETURNS SETOF int AS $$ BEGIN RETURN QUERY SELECT name_key FROM people WHERE last_name LIKE param_lname; END $$ LANGUAGE 'plpgsql' STABLE; CREATE OR REPLACE FUNCTION cp_addtextfield(param_schema_name text, param_table_name text, param_column_name text) RETURNS text AS $$ BEGIN EXECUTE 'ALTER TABLE ' || quote_ident(param_schema_name) || '.' || quote_ident(param_table_name) || ' ADD COLUMN ' || quote_ident(param_column_name) || ' text '; RETURN 'done'; END; $$ LANGUAGE 'plpgsql' VOLATILE; SELECT cp_addtextfield('public', 'employees', 'resume');

CREATE OR REPLACE FUNCTION cp_updatesometable(param_id bigint, param_lname varchar(50), param_fname varchar(50)) RETURNS void AS $$ BEGIN UPDATE people SET first_name = param_fname, last_name = param_lname WHERE name_key = param_id; END; $$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; CREATE TABLE emp_audit( operation char(1) NOT NULL, stamp timestamp NOT NULL, userid text NOT NULL, empname text NOT NULL, salary integer ); CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $$ BEGIN -- Create a row in emp_audit to reflect the operation performed on emp, -- make use of the special variable TG_OP to work out the operation. IF (TG_OP = 'DELETE') THEN INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*; RETURN NEW; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $$ LANGUAGE plpgsql; CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();