I'm building on a recipe book application. I'm only doing it to practice working in Postgresql:
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
I have several tables for storing recipe based information; one pertains to the list of ingredients:
CREATE TABLE ingredient (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
quantity NUMERIC NOT NULL,
unit TEXT,
display_order INT NOT NULL
);
I'm writing a function called save_recipe, and this is what it looks like so far
CREATE OR REPLACE PROCEDURE save_recipe
(
first_name COOK.FIRST_NAME%TYPE,
last_name COOK.LAST_NAME%TYPE,
email COOK.EMAIL%TYPE,
recipe_name RECIPE.NAME%TYPE,
recipe_cook_time RECIPE.COOK_TIME%TYPE,
recipe_preface RECIPE.PREFACE%TYPE,
instructions RECIPE.INSTRUCTIONS%TYPE,
ingred INGREDIENT
)
AS $$
DECLARE
cook_id COOK.ID%TYPE;
recipe_id RECIPE.ID%TYPE;
BEGIN
INSERT INTO cook (first_name, last_name, email)
VALUES (first_name, last_name, email)
RETURNING id INTO cook_id;
INSERT INTO recipe (name, cook_id, created_at, cook_time, instructions, preface)
VALUES (recipe_name, cook_id, now(), recipe_cook_time, instructions, recipe_preface)
RETURNING id INTO recipe_id;
INSERT INTO ingredient (name, quantity, unit, display_order)
VALUES (ingred.name, ingred.quantity, ingred.unit, ingred.display_order);
COMMIT;
RAISE NOTICE 'Cook ID : %', cook_id;
RAISE NOTICE 'Recipe ID : %', recipe_id;
END;
$$
LANGUAGE plpgsql;
But I'm having issues creating an ingredient literal (if that's the right word). This is the best I've been able to do at this point:
CALL save_recipe(
first_name => 'Joe',
last_name => 'Fresh',
email => '[email protected]',
recipe_name => 'Cherry Pie',
recipe_cook_time => '1 hour',
recipe_preface =>'I love cherry pie.',
instructions => ARRAY['Make.', 'Bake.', 'Eat.'],
ingred => (0, 'Cherry', 20, 'small, pitted', 1)
);
I'd like that ingred to be an array but my bigger concern is that I need to populate the ingredient.id even though I'm ignoring it during insert (because I want to use the generated IDs provided by Postgres). Is there a structure/type I can use where I don't need to specify a dummy ID like this (and that can eventually be an ARRAY type).
Thanks in advance.
Declare the parameter as a variadic array of the type. Use unnest() to get elements of the array as rows (the function simplified for readability):
CREATE OR REPLACE PROCEDURE save_recipe
(
first_name text,
last_name text,
email text,
VARIADIC ingred INGREDIENT[]
)
AS $$
DECLARE
cook_id COOK.ID%TYPE;
BEGIN
INSERT INTO cook (first_name, last_name, email)
VALUES (first_name, last_name, email)
RETURNING id INTO cook_id;
INSERT INTO ingredient (name, quantity, unit, display_order)
SELECT i.name, i.quantity, i.unit, i.display_order
FROM unnest(ingred) i;
RAISE NOTICE 'Cook ID : %', cook_id;
END;
$$
LANGUAGE plpgsql;
Example use:
CALL save_recipe(
first_name => 'Joe',
last_name => 'Fresh',
email => '[email protected]',
variadic ingred => array[
'(0, Apple, 2, small, 1)',
'(0, Pear, 5, large, 2)',
'(0, Cherry, 20, "small, pitted", 1)'
]::ingredient[]
);
select *
from ingredient;
id | name | quantity | unit | display_order
----+---------+----------+----------------+---------------
1 | Apple | 2 | small | 1
2 | Pear | 5 | large | 2
3 | Cherry | 20 | small, pitted | 1
(3 rows)
Note that the procedure call may be much simpler without named parameters:
CALL save_recipe(
'Joe', 'Fresh', '[email protected]',
'(0, Apple, 2, small, 1)',
'(0, Pear, 5, large, 2)',
'(0, Cherry, 20, "small, pitted", 1)'
);