User Defined Functions
CREATE FUNCTION add_nums(x int, y int) RETURNS int AS
$$
SELECT x + y;
$$
LANGUAGE SQL;
-- ORACLE
CREATE FUNCTION add_nums(x INTEGER, y INTEGER) RETURNS INTEGER
AS
BEGIN
SELECT x + y FROM dual;
END;
-- SQL Server
CREATE FUNCTION add_nums(x INTEGER, y INTEGER) RETURNS INTEGER
AS
BEGIN
SELECT x + y;
END;
DROP FUNCTION IF EXISTS prepare_product_tables;
CREATE FUNCTION prepare_product_tables() RETURNS int AS
$$
DELETE FROM products;
DELETE FROM discontinued_products;
DELETE FROM price_history;
DROP TABLE IF EXISTS price_history;
CREATE TABLE price_history (name varchar(12), price numeric, as_of timestamp);
INSERT INTO products VALUES
('monitor',400),
('printer',200),
('notebook',800),
('camera',300),
('router',30),
('microwave',80);
SELECT COUNT(*)::int FROM products;
$$
LANGUAGE SQL;
SELECT prepare_product_tables() AS products_inserted;
DROP FUNCTION IF EXISTS increase_price;
CREATE FUNCTION increase_price(product text, markup numeric) RETURNS int AS
$$
UPDATE products
SET price = price * (1 + $2)
WHERE name LIKE $1;
SELECT COUNT(*)::int FROM products
WHERE name LIKE $1;
$$
LANGUAGE SQL;
SELECT increase_price('router', .2);
DROP FUNCTION IF EXISTS increase_price;
CREATE FUNCTION increase_price(product text, markup numeric) RETURNS products AS
$$
UPDATE products
SET price = price * (1 + $2)
WHERE name LIKE $1;
INSERT INTO price_history
SELECT name, price, now()
FROM products
WHERE name LIKE $1;
SELECT * FROM products WHERE name like $1;
$$
LANGUAGE SQL;
SELECT increase_price('router', .2);
CREATE FUNCTION increase_price2(product text, markup numeric) RETURNS TABLE (name text, new_price numeric) AS
$$
UPDATE products
SET price = price * (1 + $2)
WHERE name LIKE $1
RETURNING name, price AS new_price;
$$
LANGUAGE SQL;
SELECT * FROM increase_price2('printer', .1)
DROP FUNCTION IF EXISTS discontinue_product;
CREATE FUNCTION discontinue_product( name varchar) RETURNS products AS
$$
DELETE
FROM products
WHERE name LIKE $1
RETURNING *
$$
LANGUAGE SQL;
SELECT *
FROM discontinue_product('router');
SELECT *
FROM products;
DROP FUNCTION IF EXISTS second_level_reports;
CREATE FUNCTION second_level_reports(employee_id int) RETURNS SETOF int AS
$$
SELECT employee_id
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE manager_id = $1
);
$$
LANGUAGE SQL;
SELECT *
FROM second_level_reports(3);
SELECT *
FROM employees
INNER JOIN second_level_reports(3) AS second_level
ON second_level = employees.employee_id;
console.log( 'Code is Poetry' );
console.log( 'Code is Poetry' );
SELECT *
FROM employees
INNER JOIN second_level_reports(3) AS second_level
ON second_level = employees.employee_id;
SELECT *
FROM employees
INNER JOIN second_level_reports(3) AS second_level
ON second_level = employees.employee_id;
SELECT
CASE
WHEN make_date(
EXTRACT(YEAR FROM NOW())::int,
EXTRACT(MONTH FROM dt)::int,
EXTRACT(DAY FROM dt)::int) < DATE(NOW())
THEN make_date(
EXTRACT(YEAR FROM NOW())::int + 1,
EXTRACT(MONTH FROM dt)::int,
EXTRACT(DAY FROM dt)::int)
ELSE make_date(
EXTRACT(YEAR FROM NOW())::int,
EXTRACT(MONTH FROM dt)::int,
EXTRACT(DAY FROM dt)::int)
END as nxt_date
SELECT
CASE
WHEN EXTRACT(DOW FROM nxt_date) = 0 THEN
DATE(nxt_date) + INTERVAL '1 day'
WHEN EXTRACT(DOW FROM nxt_date) = 6 THEN
DATE(nxt_date) - INTERVAL '1 day'
ELSE nxt_date
END AS dt
FROM (
SELECT
CASE
WHEN make_date(
EXTRACT(YEAR FROM NOW())::int,
EXTRACT(MONT.......
SELECT
CASE
WHEN EXTRACT(DOW FROM nxt_date) = 0 THEN
DATE(nxt_date) + INTERVAL '1 day'
WHEN EXTRACT(DOW FROM nxt_date) = 6 THEN
DATE(nxt_date) - INTERVAL '1 day'
ELSE nxt_date
END AS dt
FROM (
SELECT
CASE
WHEN make_date(
EXTRACT(YEAR FROM NOW())::int,
EXTRACT(MONTH FROM dt)::int,
EXTRACT(DAY FROM dt)::int) < DATE(NOW())
THEN make_date(
EXTRACT(YEAR FROM NOW())::int + 1,
EXTRACT(MONTH FROM dt)::int,
EXTRACT(DAY FROM dt)::int)
ELSE make_date(
EXTRACT(YEAR FROM NOW())::int,
EXTRACT(MONTH FROM dt)::int,
EXTRACT(DAY FROM dt)::int)
END as nxt_date
CREATE FUNCTION next_review_date(dt date) RETURNS DATE AS
$$
SELECT dt::DATE
FROM (
SELECT
CASE
WHEN EXTRACT(DOW FROM nxt_date) = 0 THEN
DATE(nxt_date) + INTERVAL '1 day'
WHEN EXTRACT(DOW FROM nxt_date) = 6 THEN
DATE(nxt_date) - INTERVAL '1 day'
ELSE nxt_date
END AS dt
FROM (
SELECT
CASE
WHEN make_date(
EXTRACT(YEAR FROM NOW())::int,
EXTRACT(MONTH FROM dt)::int,
EXTRACT(DAY FROM dt)::int) < DATE(NOW())
THEN make_date(
EXTRACT(YEAR FROM NOW())::int + 1,
EXTRACT(MONTH FROM dt)::int,
EXTRACT(DAY FROM dt)::int)
ELSE make_date(
EXTRACT(YEAR FROM NOW())::int,
EXTRACT(MONTH FROM dt)::int,
EXTRACT(DAY FROM dt)::int)
END as nxt_date
) d
) dd
$$
LANGUAGE SQL;
SELECT *,
next_review_date(hire_date)
FROM hire_dates;
DROP FUNCTION IF EXISTS marsaglia_normal;
CREATE FUNCTION marsaglia_normal(n int) RETURNS TABLE (n double precision) AS
$$
CREATE TEMP TABLE polar_points (
x double precision,
y double precision,
s double precision
);
INSERT INTO polar_points
SELECT
polar_points.x AS x,
polar_points.y AS y,
polar_points.s AS s
FROM (
SELECT
x,
y,
x * x + y * y AS s
FROM (
SELECT
2 * random() - 1 AS x,
2 * random() - 1 AS y
FROM generate_series(0, $1)
) numbers
WHERE x * x + y * y < 1
) polar_points;
SELECT
x * sqrt((-2 * ln(s))/s) AS n
FROM polar_points
UNION
SELECT
y * sqrt((-2 * ln(s))/s) AS n
FROM polar_points
$$
LANGUAGE SQL;
DROP FUNCTION IF EXISTS marsaglia_normal;
CREATE FUNCTION marsaglia_normal(n int) RETURNS TABLE (n double precision) AS
$$
CREATE TEMP TABLE polar_points (
x double precision,
y double precision,
s double precision
);
INSERT INTO polar_points
SELECT
polar_points.x AS x,
polar_points.y AS y,
polar_points.s AS s
FROM (
SELECT
x,
y,
x * x + y * y AS s
FROM (
SELECT
2 * random() - 1 AS x,
2 * random() - 1 AS y
FROM generate_series(0, $1)
) numbers
WHERE x * x + y * y < 1
) polar_points;
SELECT
x * sqrt((-2 * ln(s))/s) AS n
FROM polar_points
UNION
SELECT
y * sqrt((-2 * ln(s))/s) AS n
FROM polar_points
$$
LANGUAGE SQL;
SELECT count(*),
avg(marsaglia_normal) AS mean,
stddev(marsaglia_normal)
FROM (
SELECT marsaglia_normal(100000)
ORDER BY marsaglia_normal
) mars;
--OR
SELECT count(*) as n,
AVG(n) AS mean,
stddev(n) AS standard_dev
FROM polar_points;
CREATE AGGREGATE fname(rtype) (
sfunc = accum_name,
stype = float8[],
finalfunc = finalize_name,
initcond = '{0.0, 0.0}'
);
CREATE OR REPLACE FUNCTION geomean_accum(float8[], float8) RETURNS float8[] AS
$$
SELECT array[$1[1] + LN($2), $1[2]+1.0];
$$
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION geomean_finalize(float8[]) RETURNS float8 AS
$$
SELECT EXP($1[1] / $1[2]);
$$
LANGUAGE SQL;
SELECT
COUNT(column1) AS row_count,
AVG(column1) AS arithmetic_mean,
GEOMEAN(column1) AS geometric_mean
FROM (
VALUES
(5),
(7),
(55),
(6),
(3)
) sample;