Re: query can't merge into table of the other schema

I tried INSERT ... ON CONFLICT and still not work
CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_G28" (v_Ret OUT int) RETURNS integeras $$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 prodUSING cidr_staging.STG_G28 stgON ( 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: 42601Character: 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: 42601Character: 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.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(at)lelarge(dot)info> wrote:

Hi,

Le mer. 7 nov. 2018 à 23:52, Pepe TD Vo <pepevo(at)yahoo(dot)com> 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 success

On Wednesday, November 7, 2018 5:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

Ron <ronljohnsonjr(at)gmail(dot)com> 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.