From: Pepe TD Vo <pepevo@yahoo.com>
To: Pgsql-admin <pgsql-admin@postgresql.org>
Subject: query can't merge into table of the other schema
Date: Wed, 7 Nov 2018 15:10:25 +0000 (UTC)
Message-ID: <1425722754.312953.1541603425493@mail.yahoo.com> (raw)
References: <1425722754.312953.1541603425493.ref@mail.yahoo.com>
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 )
WHENMATCHED 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
WHENNOT 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
whenothers then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SQLERRM;
v_Ret := v_ErrorCode;
----
-- Commit the record into theErrorLog
----
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 integeras $$
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 variableLINE 13: MERGE INTO cidrdba.sc_date_in prod ^SQL state: 42601Character: 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pepevo@yahoo.com
Subject: Re: query can't merge into table of the other schema
In-Reply-To: <1425722754.312953.1541603425493@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox