select the values from the postgresql database and insert/update based on that i need complete query

Question!

I need to design this in query level is this possible to do for version,deleted,flag columns in the table.

src_emp----> source table

empno,ename,sal
1    ,aaa  ,1000
2    ,bbb  ,2000
3    ,ccc  ,3000

First load in target table trg_emp output look like below

empkey,empno,ename,sal  ,version,flag,deleted
1     ,1    ,aaa  ,1000 ,1      ,Y   ,N
2     ,2    ,bbb  ,2000 ,1      ,Y   ,N
3     ,3    ,ccc  ,3000 ,1      ,Y   ,N

Now after doing this i will change the salary for empno=3 to sal=4000 Then after updating my target table look like

empkey,empno,ename,sal  ,version,flag,deleted
1     ,1    ,aaa  ,1000 ,1      ,Y   ,N
2     ,2    ,bbb  ,2000 ,1      ,Y   ,N
3     ,3    ,ccc  ,3000 ,1      ,N   ,N
4     ,3    ,ccc  ,4000 ,2      ,Y   ,N

Above you can observe version is increased to 2 for empno=3 and flag has changed for last row to "Y" and previous row as "N" for the empno=3 Ok once version and flag works then third is deleted if any record is deleted in source then that flag has to become 'Y' Suppose if i delete empno=2 in source table then my final target table should look like below format

empkey,empno,ename,sal,version,flag,deleted
1,1,aaa,1000,1,Y,N
2,2,bbb,2000,1,Y,Y
3,3,ccc,3000,1,N,N
4,3,ccc,4000,2,Y,N

In target table second row flag has to update deleted flag value as "Y".



Answers

If I understand your question, you can force that kind of behavior by using TRIGGERS using postgresql. You have to create a trigger in the table referencing to the trigger function.

More info on creating triggers here: http://www.postgresql.org/docs/current/static/sql-createtrigger.html

and info on creating triggers functions here: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

By : NewK


This video can help you solving your question :)
By: admin