error when creating a view with CTE

When running the CTE below, it fetchs me the data I want.

WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
          a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

However when I'm trying to create a view on it like below

CREATE VIEW toto AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

It gives me the following error


ERROR: The column « Site » has been specified more than once


Error


ERROR: The column « Site » has been specified more than once
WSQL State :42701

I've tried to put the view name before Site, namely a or d or "Axis_Reference_All" but none of it works.

Questions are the following:

-
Is CTE allowed with view creation on postgres? I guess no but it is unclear.

-
What would be the best way to create a view with my CTE? That CTE has been built that way because it is improving performance.

There are two possible solutions

Do a USING join like below

CREATE VIEW report_view
AS 
WITH d as(
SELECT *
FROM report.get_sa001('2013-01-01'::date", TO_CHAR(NOW()", 'YYYY-MM-DD')::date", 32)
    )",
     a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site"", "Reference_internal"", "Customer_code") IN
       ( SELECT "Site"",
                    "Internal_reference"", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a USING ("Site","Internal_reference","Customer_code")
-- here is the USING. Courtesy of Jack Douglas for the tip

However as I'm retrieving datas from 5 years ago, it takes more than 5 minutes if I'm using a USING.

The best way is to call each columns separately like below and be sure to name the origin of the column, here a for Site and Customer_code.

CREATE VIEW report_view AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2015-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site", "Reference_internal", "Customer_code") IN
       ( SELECT "Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT "Period_date",
       a."Site",
       a."Customer_code",
       "Internal_reference",
       "InvoiceNumber",
       "Value_in_currency",
       "Value_in_EUR",
       "Value_Budget_in_EUR",
       "Selling_price_CUR",
       "Selling_price_EUR",
       "Currency_code",
       "Selling_quantity",
       "Variance_price_CUR",
       "Variance_price_EUR",
       "Variance_value_CUR",
       "Variance_value_EUR",
       "Selling_date",
       "Reference",
       "Reference_internal",
       "Reference_customer",
       "Reference_supplier",
       "Reference_description",
       "Reference_workshop",
       "Reference_line",
       "Reference_segment",
       "Reference_purchasingfamily",
       "Reference_motorapplication",
       "Reference_interco",
       "Reference_trading",
       "Cogs_rm",
       "Cogs_dl",
       "Cogs_voh",
       "Supplier_global",
       "Supplier_code",
       "Supplier_name",
       "Supplier_account_manager",
       "Supplier_interco",
       "Supplier_incoterm",
       "Supplier_incoterm_location",
       "Supplier_incoterm_via",
       "Supplier_continent",
       "Supplier_country",
       "Supplier_city",
       "Supplier_zipcode",
       "Purchasing_unit",
       "Purchasing_price",
       "Purchasing_currency",
       "Purchasing_payment_term_days",
       "Purchasing_payment_term_type",
       "Purchasing_consigned",
       "Purchasing_grossweight",
       "Purchasing_grosscube",
       "Purchasing_eco_order_qty",
       "Purchasing_pack_order_qty",
       "Purchasing_moq",
       "Purchasing_mov",
       "Purchasing_leadtime_days",
       "Customer_global",
       "Customer_name",
       "Customer_account_manager",
       "Customer_interco",
       "Customer_incoterm",
       "Customer_incoterm_location",
       "Customer_incoterm_via",
       "Customer_continent",
       "Customer_country",
       "Customer_city",
       "Customer_zipcode",
       "Selling_unit",
       "Selling_price",
       "Selling_currency",
       "Selling_payment_term_days",
       "Selling_payment_term_type",
       "Selling_consigned",
       "Selling_grossweight",
       "Selling_grosscube",
       "Selling_eco_order_qty",
       "Selling_pack_order_qty",
       "Selling_moq",
       "Selling_mov",
       "Selling_leadtime_days",
       "Reference_netweight",
       "Sc_storage_unit",
       "Sc_production_unit",
       "Sc_inventory_status",
       "Sc_inventory_price",
       "Sc_inventory_type"
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code"

This will give the required speed in term of data retrieval.

Cheers

StackExchange Database Administrators Q#215387, answer score: 2