4thdoctor_scarf

PostgreSQL Carbonara

Sep 20th, 2025 (edited)
128
0
Never
1
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 5.13 KB | Source Code | 0 0
  1. --load the schema in a postres database
  2. -- to make a carbonara run
  3. -- SELECT make_carbonara_please();
  4. DROP TABLE IF EXISTS ingredients;
  5. DROP TABLE IF EXISTS boiling_pot;
  6. DROP TABLE IF EXISTS frying_pan;
  7. DROP TABLE IF EXISTS mixing_bowl;
  8.  
  9. CREATE TABLE boiling_pot
  10.     (
  11.         content character varying(255) NOT NULL,
  12.         temperature integer NOT NULL DEFAULT 35,
  13.         quantity character varying(255) NOT NULL DEFAULT '',
  14.         CONSTRAINT pk_pot PRIMARY KEY (content)
  15.     )
  16. ;
  17.  
  18. CREATE TABLE mixing_bowl
  19.     (
  20.         content     character varying(255) NOT NULL,
  21.         quantity    character varying(255) NOT NULL DEFAULT '',
  22.         CONSTRAINT pk_bowl PRIMARY KEY (content)
  23.     )
  24. ;
  25.  
  26. CREATE TABLE frying_pan
  27.     (
  28.         content character varying(255) NOT NULL,
  29.         quantity    character varying(255) NOT NULL DEFAULT '',
  30.         CONSTRAINT pk_frying PRIMARY KEY (content)
  31.     )
  32. ;
  33.  
  34.  
  35.  
  36. CREATE TABLE ingredients
  37.     (
  38.         ing_name    character varying(255) NOT NULL,
  39.         quantity    character varying(255) NOT NULL,
  40.         alternative character varying(255) NULL,
  41.         notes       text NULL,
  42.         status      character varying(50) NOT NULL DEFAULT 'ready',
  43.         ordering serial,
  44.         CONSTRAINT pk_ingredients PRIMARY KEY (ing_name)
  45.     )
  46. ;
  47.  
  48. ALTER TABLE ingredients
  49.     ADD CONSTRAINT chk_blasphemous_ingredients
  50.         CHECK (
  51.             ing_name NOT IN
  52.             (
  53.                 'ham',
  54.                 'cream',
  55.                 'belly slices',
  56.                 'parmesan',
  57.                 'wild rocket',
  58.                 'mushrooms',
  59.                 'tomato',
  60.                 'pineapple',
  61.                 'onions',
  62.                 'garlic',
  63.                 'spam',
  64.                 'marmite'
  65.             )
  66.     );
  67.  
  68.    
  69.  
  70. INSERT INTO ingredients
  71.     (
  72.         ing_name,
  73.         quantity,
  74.         alternative,
  75.         notes
  76.     )
  77. VALUES
  78.     (
  79.         'spaghetti',
  80.         '350 gr',
  81.         'spaghetti',
  82.         'to get the pasta al dente, subtract 1 minute from the cooking time stated on the box'
  83.     ),
  84.     (
  85.         'pork cheek (guanciale)',
  86.         '150 gr',
  87.         'bacon (pancetta)',
  88.         'bacon is a decent alternative (Sorry, Roman fellows!!!).'
  89.     ),
  90.     (
  91.         'grated pecorino romano cheese',
  92.         '100 gr',
  93.         '',
  94.         ''
  95.     ),
  96.     (
  97.         'medium size eggs',
  98.         '3',
  99.         '',
  100.         ''
  101.     ),
  102.     (
  103.         'coarse salt',
  104.         '4 gr',
  105.         '',
  106.         ''
  107.     ),
  108.     (
  109.         'fine salt',
  110.         '2 pinches',
  111.         '',
  112.         ''
  113.     ),
  114.     (
  115.         'ground black pepper',
  116.         '2 gr',
  117.         '',
  118.         ''
  119.     )
  120. ON CONFLICT DO NOTHING;
  121.  
  122.  
  123. CREATE OR REPLACE FUNCTION make_carbonara_please()
  124. RETURNS text AS
  125. $BODY$
  126.     DECLARE
  127.         v_temperature integer;
  128.  
  129.     BEGIN
  130.  
  131.         RAISE NOTICE 'Let''s add eggs,pepper and salt to the mixing bowl';
  132.         INSERT INTO mixing_bowl (content,quantity)
  133.         SELECT
  134.             ing_name,
  135.             quantity
  136.         FROM
  137.             ingredients
  138.         WHERE
  139.             ing_name IN (
  140.                     'medium size eggs',
  141.                     'ground black pepper',
  142.                     'fine salt'
  143.                 )
  144.         ON CONFLICT DO NOTHING;
  145.         RAISE NOTICE 'Now we mix everything with a fork. hang on.';
  146.         PERFORM pg_sleep(10);
  147.         RAISE NOTICE 'PERFETTO!';
  148.         PERFORM pg_sleep(1);
  149.  
  150.         RAISE NOTICE 'Let''s add the guanciale to the frying pan and turn on the fire.';
  151.         INSERT INTO frying_pan (content,quantity)
  152.         SELECT
  153.             ing_name,
  154.             quantity
  155.         FROM
  156.             ingredients
  157.         WHERE
  158.             ing_name IN (
  159.                     'pork cheek (guanciale)'
  160.                 )
  161.         ON CONFLICT DO NOTHING;
  162.  
  163.         RAISE NOTICE 'Wait until the guanciale release its grease and start sizzling. Dont make it crispy though.';
  164.         PERFORM pg_sleep(10);
  165.         RAISE NOTICE 'MOLTO BENE!';
  166.         PERFORM pg_sleep(1);
  167.         RAISE NOTICE 'Now we are adding water to the boiling pot and wait until it boils.';
  168.         INSERT INTO boiling_pot (content,temperature) VALUES ('water',25)
  169.         ON CONFLICT DO NOTHING;
  170.  
  171.         FOR v_temperature in select generate_series(10,100,20)
  172.         LOOP
  173.             RAISE NOTICE 'be patient, water is warming up';
  174.             UPDATE boiling_pot
  175.                 SET temperature=v_temperature
  176.             WHERE
  177.                 content='water'
  178.             ;
  179.             PERFORM pg_sleep(1);
  180.         END LOOP;
  181.         UPDATE boiling_pot
  182.                 SET temperature=100
  183.             WHERE
  184.                 content='water'
  185.             ;
  186.         RAISE NOTICE 'Water is boiling, time to add salt and PASTA!';
  187.         INSERT INTO boiling_pot (content,temperature,quantity)
  188.         SELECT
  189.             ing_name,
  190.             100,
  191.             quantity
  192.         FROM
  193.             ingredients
  194.         WHERE
  195.             ing_name IN (
  196.                     'spaghetti',
  197.                     'coarse salt'
  198.                 )
  199.         ON CONFLICT DO NOTHING
  200.         ;
  201.         PERFORM pg_sleep(10);
  202.         RAISE NOTICE 'Drain the pasta then put it back into the boiling pot (without the water, of course).';
  203.         DELETE FROM boiling_pot
  204.         WHERE
  205.             content IN ('water','coarse salt');
  206.         RAISE NOTICE 'We first add the cooked guanciale.';
  207.         INSERT INTO boiling_pot
  208.             (
  209.                 content,
  210.                 quantity
  211.             )
  212.         SELECT
  213.             content,
  214.             quantity
  215.         FROM
  216.             frying_pan
  217.         ON CONFLICT DO NOTHING
  218.         ;
  219.  
  220.         PERFORM pg_sleep(5);
  221.  
  222.         RAISE NOTICE 'Then it''s the eggs turn. Don''t forget to give them another mix before adding it.';
  223.         INSERT INTO boiling_pot
  224.             (
  225.                 content,
  226.                 quantity
  227.             )
  228.         SELECT
  229.             content,
  230.             quantity
  231.         FROM
  232.             mixing_bowl
  233.         ON CONFLICT DO NOTHING
  234.         ;
  235.         PERFORM pg_sleep(5);
  236.         RAISE NOTICE 'Add the grated cheese and the pepper, then mix all together.';
  237.         INSERT INTO boiling_pot
  238.             (
  239.                 content,
  240.                 quantity
  241.             )
  242.         SELECT
  243.             ing_name,
  244.             quantity
  245.         FROM
  246.             ingredients
  247.         WHERE
  248.             ing_name IN ('grated pecorino romano cheese','ground black pepper')
  249.         ON CONFLICT DO NOTHING;
  250.         PERFORM pg_sleep(5);
  251.         RAISE NOTICE 'BUON APPETITO!';
  252.         PERFORM pg_sleep(5);
  253.         RETURN 'SPAGHETTI CARBONARA'::text;
  254.     END;
  255. $BODY$
  256. language plpgsql;
Comments
  • User was banned
Add Comment
Please, Sign In to add comment