Postgres Pro
Downloads
Home   >   mailing lists

query can't merge into table of the other schema - Mailing list pgsql-admin

From Pepe TD Vo
Subject query can't merge into table of the other schema
Date
Msg-id 1425722754.312953.1541603425493@mail.yahoo.com
Whole thread Raw
Responses Re: query can't merge into table of the other schema
List pgsql-admin
Tree view
Hello,

Would you please tell me why I can't merge table from another schema? I have granted all the privilege from one to another.

this is procedure from Oracle:\
CREATE OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_MIG_STG_DATE_IN" (
v_Ret OUT number )
as
        v_ErrorCode             number;
        v_ErrorMsg              varchar2(512);
        v_Module                varchar2(32) := 'PR_MIG_STG_DATE_IN';
begin
 
   ----
   -- MERGING: STG_DATE_IN into SC_DATE_IN
   ----
   MERGE INTO cidrdba.sc_date_in prod
   USING (
           SELECT Receipt_Number,date_in, mig_filename,mig_insert_dt,mig_modified_dt
           FROM cidr_staging.STG_Date_In
           ORDER by mig_seq
           ) stg
   ON ( prod.receipt_number = stg.receipt_number )
   WHEN MATCHED THEN UPDATE SET
   --   prod.Receipt_Number     = stg.Receipt_Number,
      prod.Date_In                      = stg.Date_In,
      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              = sysdate
   WHEN NOT MATCHED THEN INSERT
           (
                   prod.Receipt_Number,
                   prod.Date_In,
                   prod.mig_filename,
                   prod.mig_insert_dt,
                   prod.mig_modified_dt
           ) VALUES (
                   stg.Receipt_Number,
                   stg.Date_In,
                   stg.mig_filename,
                   sysdate,
                   --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
                ----
                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;
/

I converted to Postgres:

CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_DATE_IN" (v_Ret OUT integer ) RETURNS integer
as $$

declare
        v_ErrorCode             integer;
        v_ErrorMsg              varchar(512);
        v_Module                varchar(32) := 'PR_MIG_STG_DATE_IN';
begin

   ----
   -- MERGING: STG_DATE_IN into SC_DATE_IN
   ----
   MERGE INTO cidrdba.sc_date_in prod
   USING (
           SELECT Receipt_Number,date_in, mig_filename,mig_insert_dt,mig_modified_dt
           FROM cidr_staging.STG_Date_In
           ORDER by mig_seq
           ) stg
   ON ( prod.receipt_number = stg.receipt_number )
   WHEN MATCHED THEN UPDATE SET
   --   prod.Receipt_Number     = stg.Receipt_Number,
      prod.Date_In                      = stg.Date_In,
      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              = sysdate
   WHEN NOT MATCHED THEN INSERT
           (
                   prod.Receipt_Number,
                   prod.Date_In,
                   prod.mig_filename,
                   prod.mig_insert_dt,
                   prod.mig_modified_dt
           ) VALUES (
                   stg.Receipt_Number,
                   stg.Date_In,
                   stg.mig_filename,
                   sysdate,
                   --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
      ----

--      RAISE NOTICE 'calling "pr_write_error_log"(%)',( sys_context('userenv','session_user'),
PERFORM pr_write_error_log ( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module,
                                v_ErrorCode, v_ErrorMsg );
end;
$$ LANGUAGE plpgsql;

ERROR:  "cidrdba.sc_date_in" is not a known variable
LINE 13:    MERGE INTO cidrdba.sc_date_in prod
                       ^
SQL state: 42601
Character: 352
 
thank you.
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

pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: PostgreSQL 10.5 : Strange pg_wal fill-up, solved with the shutdown checkpoint
Next
From: Pepe TD Vo
Date:
Subject: for loop