I tried INSERT ... ON CONFLICT and still not work
CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_G28" (v_Ret OUT int) RETURNS integer
as $$
declare
v_ErrorCode int;
v_ErrorMsg varchar(512);
v_Module varchar(32) = 'PR_MIG_STG_G28';
begin
----------------------------------
--### MERGING: STG_G28 into SC_G28
----------------------------------
-- MERGE INTO cidrdba.sc_g28 prod
-- USING (
INSERT into cidrdba.sc_g28 prod
USING cidr_staging.STG_G28 stg
ON ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
prod.Service_Center = stg.Service_Center,
-- prod.Receipt_Number = stg.Receipt_Number,
prod.Rep_INS_Attny_ID = stg.Rep_INS_Attny_ID,
prod.Rep_State_Num = stg. Rep_State_Num,
prod.Rep_VOLAG = stg.Rep_VOLAG,
prod.Rep_Rep_Code = stg.Rep_Rep_Code,
prod.Rep_Last_Name = stg.Rep_Last_Name,
prod.Rep_First_Name = stg.Rep_First_Name,
prod.Rep_Middle_Name = stg.Rep_Middle_Name,
prod.Rep_Firm_Name = stg.Rep_Firm_Name,
prod.Rep_Street = stg.Rep_Street,
prod.Rep_Street_2 = stg.Rep_Street_2,
prod.Rep_City = stg.Rep_City,
prod.Rep_State = stg.Rep_State,
prod.Rep_ZIP = stg.Rep_ZIP,
prod.Rep_Province = stg.Rep_Province,
prod.Rep_Postal_code = stg.Rep_Postal_code,
prod.Rep_Country = stg.Rep_Country,
prod.mig_filename = stg.mig_filename,
--prod.mig_insert_dt = stg.mig_insert_dt,
--prod.mig_modified_dt = stg.mig_modified_dt
prod.mig_modified_dt = current_timestamp
WHEN NOT MATCHED THEN INSERT
(
Service_Center,
Receipt_Number,
Rep_INS_Attny_ID,
Rep_State_Num,
Rep_VOLAG,
Rep_Rep_Code,
Rep_Last_Name,
Rep_First_Name,
Rep_Middle_Name,
Rep_Firm_Name,
Rep_Street,
Rep_Street_2,
Rep_City,
Rep_State,
Rep_ZIP,
Rep_Province,
Rep_Postal_code,
Rep_Country,
mig_filename,
mig_insert_dt,
mig_modified_dt
) SELECT (
stg.Service_Center,
stg.Receipt_Number,
stg.Rep_INS_Attny_ID,
stg.Rep_State_Num,
stg.Rep_VOLAG,
stg.Rep_Rep_Code,
stg.Rep_Last_Name,
stg.Rep_First_Name,
stg.Rep_Middle_Name,
stg.Rep_Firm_Name,
stg.Rep_Street,
stg.Rep_Street_2,
stg.Rep_City,
stg.Rep_State,
stg.Rep_ZIP,
stg.Rep_Province,
stg.Rep_Postal_code,
stg.Rep_Country,
stg.mig_filename,
current_timestamp,
--stg.mig_insert_dt,
null
--stg.mig_modified_dt
)
;
----
-- Set the return code to 0
----
v_Ret := SQLCODE;
----
-- Exception error handler
----
exception
when others then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SQLERRM;
v_Ret := v_ErrorCode;
----
-- Commit the record into the ErrorLog
----
PERFORM pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
----
-- Intentionally leaving the "commit" to application
----
end;
$$ LANGUAGE plpgsql;
--
ERROR: syntax error at or near "prod"
LINE 15: INSERT into cidrdba.sc_g28 prod
^
SQL state: 42601
Character: 452
even with and/or without alias of cidrdba.gc_g28 table
If I do:
INSERT into cidrdba.sc_g28 (prod.service_Center,prod.receipt_Number,prod.rep_INS_Attny_ID,prod.rep_State_Num,prod.rep_VOLAG,
prod.Rep_Rep_Code, prod.Rep_Last_Name,prod.Rep_First_Name,prod.Rep_Middle_Name, prod.rep_Firm_Name,
prod.rep_Street,prod.Rep_Street_2, prod.Rep_City,prod.rep_State,prod.rep_ZIP,prod.rep_Province, prod.Rep_Postal_code,
prod.Rep_Country, prod.mig_filename, prod.mig_modified_dt)
(
SELECT stg.Service_Center, stg.Receipt_Number, stg.Rep_INS_Attny_ID, stg.Rep_State_Num, stg.Rep_VOLAG,
stg.Rep_Rep_Code, stg.Rep_Last_Name, stg.Rep_First_Name, stg.Rep_Middle_Name, stg.Rep_Firm_Name,
stg.Rep_Street, stg.Rep_Street_2, stg.Rep_City, stg.Rep_State, stg.rep_ZIP, stg.Rep_Province, stg.Rep_Postal_code,
stg.ep_Country, stg.mig_filename, stg.mig_modified_dt
FROM cidr_staging.STG_G28 stg
ORDER by stg.mig_seq
-- ) stg
ON CONFLICT ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
prod.Service_Center = stg.Service_Center,
-- prod.Receipt_Number = stg.Receipt_Number,
prod.Rep_INS_Attny_ID = stg.Rep_INS_Attny_ID,
prod.Rep_State_Num = stg. Rep_State_Num,
prod.Rep_VOLAG = stg.Rep_VOLAG, ..... blah ...blah
I get:
ERROR: syntax error at or near "ON"
LINE 13: ON CONFLICT ( prod.receipt_number = stg.receipt_numbe...
^
SQL state: 42601
Character: 985
v/r,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
On Thursday, November 8, 2018 4:16 AM, Guillaume Lelarge <guillaume@xxxxxxxxxxxx> wrote:
Hi,
--
Le mer. 7 nov. 2018 à 23:52, Pepe TD Vo <pepevo@xxxxxxxxx> a écrit :
thank you for replying, if there's no Merge stating in Postgres, what statement in Postgres should I use? Update?
INSERT ... ON CONFLICT ... (see https://www.postgresql.org/docs/11/sql-insert.html for details)
I found merge examples on https://wiki.postgresql.org/wiki/MergeTestExamples and the syntax is about the same as Oracle but a function is not working.
Yeah, but at the top of this page, there is this text: "This was never integrated into PostgreSQL, and requires significant work to be production quality".
If I used update syntax it would long implement for this query. I'm not a developer and I'm new to Postgres. Is there a good document you suggest me to use?
The manual would be a good place to start: https://www.postgresql.org/docs/
thank you again.Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing successRon <ronljohnsonjr@xxxxxxxxx> writes:
> On 11/07/2018 09:10 AM, Pepe TD Vo wrote:
>> *ERROR: "cidrdba.sc_date_in" is not a known variable*
> That doesn't look like a "can't merge table from another schema" error.
>> *LINE 13: MERGE INTO cidrdba.sc_date_in prod*
>> * ^*
>> *SQL state: 42601*
>> *Character: 352*
> What happens when you run the statement through psql?
It will fail, of course, since there's no MERGE statement in Postgres.
I think the reason for the weird error is that the plpgsql scanner is
seeing the "INTO cidrdba.sc_date_in" part and trying to process that
as an "INTO plpgsql-variable" clause, before it's fed the rest of the
statement to the core parser, which is what would notice that MERGE
isn't a known command. Maybe we could improve matters by reordering
that processing, but it might be a lot of work for a small benefit.
regards, tom lane
--
Guillaume.
