query can't merge into table of the other schema

Поиск
Список
Период
Сортировка
От Pepe TD Vo
Тема query can't merge into table of the other schema
Дата
Msg-id 1425722754.312953.1541603425493@mail.yahoo.com
обсуждение исходный текст
Ответы Re: query can't merge into table of the other schema  (Ron <ronljohnsonjr@gmail.com>)
Список pgsql-admin
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: PostgreSQL 10.5 : Strange pg_wal fill-up, solved with the shutdown checkpoint
Следующее
От: Pepe TD Vo
Дата:
Сообщение: for loop