음 어느정도는 만들고 올리셔야..

너무 막연합니다.

테이블 정보나

예시정도는 올리셔야 도움을 드릴듯합니다.

 이런식으로 만들어 봤는데요..

insert into b

잘 안되네요..

집합 개념으로 생각하셔야죠.

A집합에서 B집합을 빼서 (except) 그놈을 B 집합에 넣는다.

insert into b select from a except select from b

 insert into a select * from except select * from a 에러 나는데여..

create table b (aco1 int ,bco1 varchar);

with a as

(

select 3 as aco1,'test'::text as bco1

)

insert into b (aco1,bco1)

select aco1,bco1

from a

where (aco1,bco1) not in (select aco1, bco1 from b);

모 이런식으로 만들수는 있겠죠

CREATE OR REPLACE FUNCTION public.usp_set_emp_udt

(

p_employee_composite public.employee_udt []

)

RETURNS void AS $$

/*

지현명

*/

DECLARE

_sqlstate text;

_message text;

_context text;

BEGIN

--데이터 처리는 delete > update > insert

with upsert as (SELECT A.emp_id, A.emp_nm FROM unnest(p_employee_composite) A),

del as

(

delete from employee

where not exists (select 1 from upsert a where a.emp_id = employee.emp_id)

),

upd as

(

update employee

set emp_nm = upsert.emp_nm

, last_chg_time = clock_timestamp()

from upsert

where upsert.emp_id = employee.emp_id

AND upsert.emp_nm <> employee.emp_nm

)

insert into employee

select upsert.emp_id, upsert.emp_nm, clock_timestamp(), clock_timestamp()

from upsert

where not exists(select 1 from employee where employee.emp_id = upsert.emp_id);

EXCEPTION

WHEN OTHERS THEN

GET STACKED DIAGNOSTICS

_sqlstate = RETURNED_SQLSTATE,

_message = MESSAGE_TEXT,

_context = PG_EXCEPTION_CONTEXT;

RAISE EXCEPTION E'sqlstate: %, message: %, context: [%]',

_sqlstate, _message, replace(_context, E'n', ' <- ');

END;

$$ LANGUAGE 'plpgsql';