postgres version 9.5
level of my knowledge - novice to both postgresql and sql
The below code below that is commented out works.
--INSERT INTO public."tblADTforOrders" (pat_mrn, pat_acct_number, pat_lname, pat_fname, pat_dob, msg_id)
-- SELECT '30006', 'P0085465a','THOMAS', 'PAMELA', '19551006', '2016071815582862'
-- WHERE NOT EXISTS (SELECT 1 FROM public."tblADTforOrders" WHERE pat_acct_number='P0085465a');
And I would like to use variables to hold string information and use that in the INSERT/SELECT statement. However I am unable to get the correct syntax. I've tried set, declare and several other examples I have found on google search but have not been able to accomplish doing this type of mapping.
The information does NOT work.
set patient_mrn '30006'
set patient_lname 'THOMAS'
set patient_fname 'PAMELA'
set patient_dob '19551006'
set patient_acct_number 'P0085465b'
set msg_control_id '2016071815582862'
INSERT INTO public."tblADTforOrders" (pat_mrn, pat_acct_number, pat_lname, pat_fname, pat_dob, msg_id)
SELECT :patient_mrn, :patient_acct_number, :patient_lname, :patient_fname, :patient_dob, :msg_control_id
WHERE NOT EXISTS (SELECT 1 FROM public."tblADTforOrders" WHERE pat_acct_number= :patient_acct_number);
I've spent far to long on this and unfortunately it is a basic concept I need to know how to complete. Any and all assistance is appreciated. Thanks in advance.