how to join 2 related tables using postgres functions?

Question

I have a recipes table with columns recipe_id, product_name, instructions and I have instructions table with columns recipe_id, instructions.

columns instructions and recipe_id in recipes table is the same number that points to instructions table.

I was using supabase client and tried to join them like this:

  let { data, error, count } = await supabase
    .from('recipes')
    .select(
      `*,
    instructions(instructions)
    `,
      { count: 'exact' }
    )
    .eq('id', id);

But the returned data had instructions as an object:

Recipes table:

| recipe_id | instructions | product_name |
|-----------|--------------|--------------|
| 24        | 24           | omelette     |

Instructions table

| recipe_id | instructions                                     |
|-----------|--------------------------------------------------|
| 24        | '{"root":{"children":[{"children":[{"detail":0,"for…format":"","indent":0,"type":"root","version":1}}' |

I want combine it so it looks like this:

product_name: "Omelette",
recipe_id: 24,
instructions: '{"root":{"children":[{"children":[{"detail":0,"for…format":"","indent":0,"type":"root","version":1}}'

How can I write a function that joins recipe with instructions and returns it as a key instead of another object?

Something that I could call it with supabase.rpc()

user_2188186 · Answer

This is possible if you create a function that returns a table.

I've recreated tables here as an example:

CREATE TABLE recipes (
    id int8 PRIMARY KEY, 
    product_name text,
    instructions text,
);
CREATE TABLE instructions (
    instructions_id serial, 
    recipe_id int8 REFERENCES recipes ON DELETE CASCADE,
    instructions int,
    PRIMARY KEY (recipe_id, instructions_id)
);

Once you have the table set, then you can create the function that returns a table. Like this:

CREATE OR REPLACE FUNCTION get_instructions_for_recipe(recipe_id int8) RETURNS 
TABLE (id int8, product_name text, instructions text, instructions_instructions text) AS 
$$
  SELECT recipes.id, recipes.product_name, recipes.instructions , instructions.instructions
  FROM recipes INNER JOIN instructions
  ON recipes.id = instructions.recipe_id
  WHERE recipe_id = recipes.id   
$$ language SQL;

Then, you can call the using supabase-js client:

const supabase = createClient(SUPABASE_URL, SUPABASE_KEY)
const { data: receipe_data, error } = await supabase
  .rpc('get_instructions_for_recipe', {recipe_id: 1})

how to join 2 related tables using postgres functions?

1 Answers