Pg_carbonara

pg_carbonara.sql

--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 mix all togheter.';
		INSERT INTO boiling_pot 
			(
				content,
				quantity
			)  
		SELECT 
			ing_name,
			quantity 
		FROM 
			ingredients 
		WHERE 
			ing_name ='grated pecorino romano cheese'
		ON CONFLICT DO NOTHING;
		PERFORM pg_sleep(5);
		RAISE NOTICE 'BUON APPETITO!';
		PERFORM pg_sleep(5);
		RETURN 'SPAGHETTI CARBONARA'::text;
	END;
$BODY$
language plpgsql;