Pg_carbonara
--load the schema in a postres database | |
-- to make a carbonara run | |
-- SELECT make_carbonara_please(); | |
DROP TABLE IF EXISTS ingredients; | |
DROP TABLE IF EXISTS boiling_pot; | |
DROP TABLE IF EXISTS frying_pan; | |
DROP TABLE IF EXISTS mixing_bowl; | |
CREATE TABLE boiling_pot | |
( | |
content character varying(255) NOT NULL, | |
temperature integer NOT NULL DEFAULT 35, | |
quantity character varying(255) NOT NULL DEFAULT '', | |
CONSTRAINT pk_pot PRIMARY KEY (content) | |
) | |
; | |
CREATE TABLE mixing_bowl | |
( | |
content character varying(255) NOT NULL, | |
quantity character varying(255) NOT NULL DEFAULT '', | |
CONSTRAINT pk_bowl PRIMARY KEY (content) | |
) | |
; | |
CREATE TABLE frying_pan | |
( | |
content character varying(255) NOT NULL, | |
quantity character varying(255) NOT NULL DEFAULT '', | |
CONSTRAINT pk_frying PRIMARY KEY (content) | |
) | |
; | |
CREATE TABLE ingredients | |
( | |
ing_name character varying(255) NOT NULL, | |
quantity character varying(255) NOT NULL, | |
alternative character varying(255) NULL, | |
notes text NULL, | |
status character varying(50) NOT NULL DEFAULT 'ready', | |
ordering serial, | |
CONSTRAINT pk_ingredients PRIMARY KEY (ing_name) | |
) | |
; | |
ALTER TABLE ingredients | |
ADD CONSTRAINT chk_blasphemous_ingredients | |
CHECK ( | |
ing_name NOT IN | |
( | |
'ham', | |
'cream', | |
'belly slices', | |
'parmesan', | |
'wild rocket', | |
'mushrooms', | |
'tomato', | |
'pineapple', | |
'onions', | |
'garlic', | |
'spam', | |
'marmite' | |
) | |
); | |
INSERT INTO ingredients | |
( | |
ing_name, | |
quantity, | |
alternative, | |
notes | |
) | |
VALUES | |
( | |
'spaghetti', | |
'350 gr', | |
'spaghetti', | |
'to get the pasta al dente, subtract 1 minute from the cooking time stated on the box' | |
), | |
( | |
'pork cheek (guanciale)', | |
'150 gr', | |
'bacon (pancetta)', | |
'bacon is a decent alternative (Sorry, Roman fellows!!!).' | |
), | |
( | |
'grated pecorino romano cheese', | |
'100 gr', | |
'', | |
'' | |
), | |
( | |
'medium size eggs', | |
'3', | |
'', | |
'' | |
), | |
( | |
'coarse salt', | |
'4 gr', | |
'', | |
'' | |
), | |
( | |
'fine salt', | |
'2 pinches', | |
'', | |
'' | |
), | |
( | |
'ground black pepper', | |
'2 gr', | |
'', | |
'' | |
), | |
( | |
'olive oil', | |
'3 gr', | |
'', | |
'' | |
) | |
ON CONFLICT DO NOTHING; | |
CREATE OR REPLACE FUNCTION make_carbonara_please() | |
RETURNS text AS | |
$BODY$ | |
DECLARE | |
v_temperature integer; | |
BEGIN | |
RAISE NOTICE 'Let''s add eggs,pepper and salt to the mixing bowl'; | |
INSERT INTO mixing_bowl (content,quantity) | |
SELECT | |
ing_name, | |
quantity | |
FROM | |
ingredients | |
WHERE | |
ing_name IN ( | |
'medium size eggs', | |
'ground black pepper', | |
'fine salt' | |
) | |
ON CONFLICT DO NOTHING; | |
RAISE NOTICE 'Now we mix everything with a fork. hang on.'; | |
PERFORM pg_sleep(10); | |
RAISE NOTICE 'PERFETTO!'; | |
PERFORM pg_sleep(1); | |
RAISE NOTICE 'Let''s add olive oil to the frying pan and turn on the fire.'; | |
INSERT INTO frying_pan (content,quantity) | |
SELECT | |
ing_name, | |
quantity | |
FROM | |
ingredients | |
WHERE | |
ing_name IN ( | |
'olive oil', | |
'pork cheek (guanciale)' | |
) | |
ON CONFLICT DO NOTHING; | |
RAISE NOTICE 'Turn on the fire and wait until the guanciale is cooked. Dont make it crispy though.'; | |
PERFORM pg_sleep(10); | |
RAISE NOTICE 'MOLTO BENE!'; | |
PERFORM pg_sleep(1); | |
RAISE NOTICE 'Now we adding water to the boiling pot and wait until boils.'; | |
INSERT INTO boiling_pot (content,temperature) VALUES ('water',25) | |
ON CONFLICT DO NOTHING; | |
FOR v_temperature in select generate_series(10,100,20) | |
LOOP | |
RAISE NOTICE 'be patient, water is warming up'; | |
UPDATE boiling_pot | |
SET temperature=v_temperature | |
WHERE | |
content='water' | |
; | |
PERFORM pg_sleep(1); | |
END LOOP; | |
UPDATE boiling_pot | |
SET temperature=100 | |
WHERE | |
content='water' | |
; | |
RAISE NOTICE 'Water is boiling, time to add salt and PASTA!'; | |
INSERT INTO boiling_pot (content,temperature,quantity) | |
SELECT | |
ing_name, | |
100, | |
quantity | |
FROM | |
ingredients | |
WHERE | |
ing_name IN ( | |
'spaghetti', | |
'coarse salt' | |
) | |
ON CONFLICT DO NOTHING | |
; | |
PERFORM pg_sleep(10); | |
RAISE NOTICE 'Drain the pasta then put it back into the boiling pot (without the water, of course).'; | |
DELETE FROM boiling_pot | |
WHERE | |
content IN ('water','coarse salt'); | |
RAISE NOTICE 'We first add the cooked guanciale.'; | |
INSERT INTO boiling_pot | |
( | |
content, | |
quantity | |
) | |
SELECT | |
content, | |
quantity | |
FROM | |
frying_pan | |
ON CONFLICT DO NOTHING | |
; | |
PERFORM pg_sleep(5); | |
RAISE NOTICE 'Then it''s the eggs turn. Don''t forget to give them another mix before adding it.'; | |
INSERT INTO boiling_pot | |
( | |
content, | |
quantity | |
) | |
SELECT | |
content, | |
quantity | |
FROM | |
mixing_bowl | |
ON CONFLICT DO NOTHING | |
; | |
PERFORM pg_sleep(5); | |
RAISE NOTICE 'Add the grated cheese and the pepper, then mix all together.'; | |
INSERT INTO boiling_pot | |
( | |
content, | |
quantity | |
) | |
SELECT | |
ing_name, | |
quantity | |
FROM | |
ingredients | |
WHERE | |
ing_name IN ('grated pecorino romano cheese','ground black pepper') | |
ON CONFLICT DO NOTHING; | |
PERFORM pg_sleep(5); | |
RAISE NOTICE 'BUON APPETITO!'; | |
PERFORM pg_sleep(5); | |
RETURN 'SPAGHETTI CARBONARA'::text; | |
END; | |
$BODY$ | |
language plpgsql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment