Skip to content

Instantly share code, notes, and snippets.

@the4thdoctor

the4thdoctor/pg_carbonara.sql

Last active December 2, 2022 13:20
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save the4thdoctor/b347c18f341b1c35de8e4895c3e29496 to your computer and use it in GitHub Desktop.
Save the4thdoctor/b347c18f341b1c35de8e4895c3e29496 to your computer and use it in GitHub Desktop.
Download ZIP
Pg_carbonara
Raw
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 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