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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.
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 success


On Wednesday, November 7, 2018 5:36 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:


Ron <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.




[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux