I have a function that creates records for different tables that are referenced with each other. The function returns the record from the "parent" table. So I am using that function in the FROM of my query so that I can then perform JOIN on the associated records so that I can piece the tables together. My problem is that the associated records aren’t getting returned and I know for certain that they are getting created.
From what I understand, FROM and JOIN are pretty much executed at the same time so it makes sense that the FROM‘s creation of the records that are used in the JOIN would not exist.
So how can I ensure that the call to the function is executed first before the JOIN is executed? I tried doing this with CTE but no avail.
SELECT i.invoice_id, i.invoice_date, i.invoice_due_date, i.created_by_id, i.currency_id, i.created_at, si.supplier_info, bi.billing_info FROM invoices.create_shift_invoice( CAST(NULLIF($ invoice_date, NULL) AS TIMESTAMP), CAST(NULLIF($ invoice_due_date, NULL) AS TIMESTAMP), CAST(NULLIF($ currency_id, '') AS INT8), CAST(NULLIF($ created_by_id, '') AS INT8), CAST(NULLIF($ supplier_info, NULL) AS JSONB), CAST(NULLIF($ billing_info, NULL) AS JSONB), CAST(NULLIF($ invoice_items_shifts, NULL) AS JSONB) ) i JOIN ( SELECT _si.invoice_id, json_build_object( 'supplier_info_id', CAST(_si.supplier_info_id AS VARCHAR), 'invoice_id', CAST(_si.invoice_id AS VARCHAR), 'suppler_name', _si.supplier_name ) AS supplier_info FROM invoices.supplier_infos _si ) si USING (invoice_id) JOIN ( SELECT _bi.invoice_id, json_build_object( 'billing_info_id', CAST(_bi.billing_info_id AS VARCHAR), 'invoice_id', CAST(_bi.invoice_id AS VARCHAR), 'customer_id', CAST(_bi.customer_id AS VARCHAR) ) AS billing_info FROM invoices.billing_infos _bi ) bi USING (invoice_id) And here’s my attempt at a CTE:
WITH create_shift_invoice AS ( SELECT * FROM invoices.create_shift_invoice( CAST(NULLIF($ invoice_date, NULL) AS TIMESTAMP), CAST(NULLIF($ invoice_due_date, NULL) AS TIMESTAMP), CAST(NULLIF($ currency_id, '') AS INT8), CAST(NULLIF($ created_by_id, '') AS INT8), CAST(NULLIF($ supplier_info, NULL) AS JSONB), CAST(NULLIF($ billing_info, NULL) AS JSONB), CAST(NULLIF($ invoice_items_shifts, NULL) AS JSONB) ) ) SELECT i.invoice_id, i.invoice_date, i.invoice_due_date, i.created_by_id, i.currency_id, i.created_at, si.supplier_info, bi.billing_info FROM create_shift_invoice i LEFT JOIN ( SELECT _si.invoice_id, json_build_object( 'supplier_info_id', CAST(_si.supplier_info_id AS VARCHAR), 'invoice_id', CAST(_si.invoice_id AS VARCHAR), 'suppler_name', _si.supplier_name ) AS supplier_info FROM invoices.supplier_infos _si ) si USING (invoice_id) LEFT JOIN ( SELECT _bi.invoice_id, json_build_object( 'billing_info_id', CAST(_bi.billing_info_id AS VARCHAR), 'invoice_id', CAST(_bi.invoice_id AS VARCHAR), 'customer_id', CAST(_bi.customer_id AS VARCHAR) ) AS billing_info FROM invoices.billing_infos _bi ) bi USING (invoice_id) 