I am currently working on a stored procedure and am running my head into the wall not seeing where there could be ambiguity. I need to do the following: Update columns in a target table, if certain pertaining datapoints are found in another table. If however there is no fitting datapoint to update in the target table, then an insertion should happen. To do this I figured I'd use something of structure
WITH cte AS (UPDATE Table1 SET a=b FROM view1 where condition RETURNING *)
INSERT INTO Table1 (values)
(SELECT values FROM cte where condition);
which I got from this SO answers php postgres UPDATE: check for success . (As I am writing this it dawns on me that this will return the already updated rows by RETURNING. So i will need to work out the set of non-updated values from that. Ignore that oversight for now).
Pushing this through some syntax validator (https://onecompiler.com/postgresql) doesn't net any errors.
However when trying to actually run the SP , my system throws ERROR: column reference "persnr" is ambiguous Where: PL/pgSQL function cpm_sp_kst_umbuchung(character varying,character varying) line 20 at SQL statement.
After staring at the SP for quite some time and googling around I can't seem to figure out how the column name would be ambiguous. Can anyone point me to my error? I figured that it probably has something to do with how I am instanciating the CTE. Thanks in advance!
Anyhow, here is the full SP and view:
CREATE PROCEDURE CPM_SP_KST_UMBUCHUNG(
_scenario_plan_0 varchar(30),
session_usr varchar(30)
)
LANGUAGE plpgsql
AS
$$ -- SP
DECLARE
--Variable Declaration
_scenario_plan_1 character varying;
_scenario_plan_2 character varying;
BEGIN --SP
_scenario_plan_1 = (SELECT COD_SCENARIO_SUCC FROM SCENARIO WHERE COD_SCENARIO = _scenario_plan_0);
_scenario_plan_2 = (SELECT COD_SCENARIO_SUCC FROM SCENARIO WHERE COD_SCENARIO = (SELECT COD_SCENARIO_SUCC FROM SCENARIO WHERE COD_SCENARIO = _scenario_plan_0));
BEGIN -- STATUS N
WITH cte_update_rest AS
(
UPDATE AW_001_000001_000001 HR_SET
SET COD_DEST1 = V.COD_DEST1_NEU
,COD_DEST2 = V.COD_DEST2_NEU
,COD_DEST3 = V.COD_DEST3_NEU
,COD_AZIENDA = V.COD_AZIENDA_NEU
,IMPORTO = CAST(V.IMPORTO as NUMERIC)/CAST(V.HR_ANTEIL as NUMERIC) * CAST(V.KST_ANTEIL as NUMERIC)
,ANTEIL = 100 * CAST(V.HR_ANTEIL as NUMERIC) * CAST(V.KST_ANTEIL as NUMERIC)
,ZEIT = CAST(V.ZEIT as NUMERIC)/CAST(V.HR_ANTEIL as NUMERIC) * CAST(V.KST_ANTEIL as NUMERIC)
,PROVENIENZA = 'CPM_SP_KST_UMBUCHUNG'
,USERUPD = session_usr
,DATEUPD = NOW()
FROM V_KST_AW_BASIS V
WHERE V.HR_SCE in (_scenario_plan_0, _scenario_plan_1, _scenario_plan_2)
AND V.KST_SCE = _scenario_plan_0
AND V.STATUS = 'N'
AND HR_SET.COD_SCENARIO = V.HR_SCE
AND HR_SET.COD_AZIENDA = V.COD_AZIENDA
AND HR_SET.PERSNR = V.PERSNR
AND HR_SET.COD_PERIODO = V.COD_PERIODO
AND HR_SET.COD_SCENARIO = V.HR_SCE
AND HR_SET.FUNKTION = V.FUNKTION
AND HR_SET.LOHNART = V.LOHNART
AND HR_SET.COD_CONTO = V.COD_CONTO
AND HR_SET.COD_VALUTA = V.COD_VALUTA
AND HR_SET.COD_CATEGORIA = V.COD_CATEGORIA
AND HR_SET.BUCHUNG = V.BUCHUNG
AND HR_SET.TARIF = V.TARIF
AND HR_SET.EN_VERSION = V.EN_VERSION
AND HR_SET.COD_DEST1 = V.COD_DEST1
AND HR_SET.COD_DEST2 = V.COD_DEST2
AND HR_SET.COD_DEST3 = V.COD_DEST3
RETURNING *
)
INSERT INTO AW_001_000001_000001
(
OID
,PERSNR
,COD_AZIENDA
,COD_SCENARIO
,COD_PERIODO
,FUNKTION
,LOHNART
,COD_CONTO
,COD_DEST1
,COD_DEST2
,COD_DEST3
,COD_VALUTA
,IMPORTO
,ANTEIL
,ZEIT
,BUCHUNG
,COD_CATEGORIA
,TARIF
,EN_VERSION
,PROVENIENZA
,USERUPD
,DATEUPD
)
(SELECT
uuid_generate_v4() as OID
,cte.PERSNR
,cte.COD_AZIENDA_NEU as COD_AZIENDA
,cte.COD_SCENARIO
,cte.COD_PERIODO
,cte.FUNKTION
,cte.LOHNART
,cte.COD_CONTO
,cte.COD_DEST1_NEU
,cte.COD_DEST2_NEU
,cte.COD_DEST3_NEU
,cte.COD_VALUTA
,CAST(cte.IMPORTO as NUMERIC)/CAST(cte.cte.HR_ANTEIL as NUMERIC) * CAST(cte.KST_ANTEIL as NUMERIC) as IMPORTO
,100 * CAST(cte.HR_ANTEIL as NUMERIC) * CAST(cte.KST_ANTEIL as NUMERIC) as ANTEIL --das muss noch angepasst werden damit die Berechnung stimmt
,CAST(cte.ZEIT as NUMERIC)/CAST(cte.HR_ANTEIL as NUMERIC) * CAST(cte.KST_ANTEIL as NUMERIC) as ZEIT
,cte.BUCHUNG
,cte.COD_CATEGORIA
,cte.TARIF
,cte.EN_VERSION
,'CPM_SP_KST_UMBUCHUNG' as PROVENIENZA
,session_usr as USERUPD
,NOW() as DATEUPD
FROM cte_update_rest cte
WHERE
cte.HR_SCE in (_scenario_plan_0, _scenario_plan_1, _scenario_plan_2)
AND cte.KST_SCE = _scenario_plan_0
AND cte.STATUS = 'N'
)
;
END; -- STATUS N
BEGIN -- STATUS U
SELECT 'U' as PLATZHALTER;
END; -- STATUS U
BEGIN -- STATUS L
SELECT 'L' as PLATZHALTER;
END; -- STATUS L
END; --SP
$$ -- SP
CREATE VIEW V_KST_AW_BASIS
AS
SELECT
KST_SET.PERSNR
,HR_SET.COD_AZIENDA AS COD_AZIENDA
,KST_SET.COD_AZIENDA_NEU
,HR_SET.COD_SCENARIO AS HR_SCE
,KST_SET.COD_SCENARIO AS KST_SCE
,HR_SET.COD_PERIODO
,HR_SET.FUNKTION
,HR_SET.LOHNART
,HR_SET.COD_CONTO
,HR_SET.COD_DEST1 as COD_DEST1
,HR_SET.COD_DEST2 as COD_DEST2
,HR_SET.COD_DEST3 as COD_DEST3
,KST_SET.COD_DEST1_NEU as COD_DEST1_NEU
,KST_SET.COD_DEST2_NEU as COD_DEST2_NEU
,KST_SET.COD_DEST3_NEU as COD_DEST3_NEU
,HR_SET.COD_VALUTA
,HR_SET.IMPORTO
,CAST(HR_SET.ANTEIL AS NUMERIC)/100 as HR_ANTEIL
,KST_SET.ANTEIL_NEU AS KST_ANTEIL
,HR_SET.ZEIT
,HR_SET.BUCHUNG
,HR_SET.COD_CATEGORIA
,HR_SET.TARIF
,HR_SET.EN_VERSION
,KST_SET.STATUS
FROM AW_001_000001_000001 HR_SET
RIGHT JOIN AW_001_000004_000001 KST_SET
ON HR_SET.PERSNR = KST_SET.PERSNR
AND HR_SET.COD_AZIENDA = KST_SET.COD_AZIENDA
AND HR_SET.COD_DEST1 = KST_SET.COD_DEST1
AND HR_SET.COD_DEST2 = KST_SET.COD_DEST2
AND HR_SET.COD_DEST3 = KST_SET.COD_DEST3
WHERE KST_SET.STATUS is not null
This is on plpgSQL version: PostgreSQL 13.10 (Ubuntu 13.10-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
I tried to reference all columns by using table.-prefix and googled the error (also on SO). The problem is that most solutions are only expanding on how you need to reference the columns with the table-prefix if the columns are part of both joined tables/views. I am aware of that, but can't see how I am not adhering to this in my SP.
The question is obsolete. I figured that the Updated-table which I am returning with RETURNING * holds all columns from BOTH tables used in the update statement. This leads to duplicated column codes for the columns I am joining on. This is fixed by directly naming the columns I need.
Anyhow, I pivoted towards another approach as this approach didn't actually gave me the information I needed.