mrrmpc
asked on
PostgreSQL Syntax
postgres version 9.5
level of my knowledge - novice to both postgresql and sql
The below code below that is commented out works.
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.
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.
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.
Can you elaborate on "does not work"?
Take a look at the doc page describing how to declare variables.
I think the declaration of the variable and setting its value needs to be done in separate steps.
declare patient_lname varchar(20);
set patient_lname = 'THOMAS';
declare patient_lname varchar(20);
set patient_lname = 'THOMAS';
ASKER
I appreciate the assistance.
I still get the below error. And I get it if I also use patient_mrn := '30006'; or set patient_mrn := '30006'; or set patient_mrn = '30006'; :
And I have also tried the following declare patient_mrn varchar(50);
Errors out on line1.
ERROR: syntax error at or near "varchar"
LINE 1: declare patient_mrn varchar;
^
********** Error **********
ERROR: syntax error at or near "varchar"
SQL state: 42601
Character: 21
I still get the below error. And I get it if I also use patient_mrn := '30006'; or set patient_mrn := '30006'; or set patient_mrn = '30006'; :
And I have also tried the following declare patient_mrn varchar(50);
Errors out on line1.
ERROR: syntax error at or near "varchar"
LINE 1: declare patient_mrn varchar;
^
********** Error **********
ERROR: syntax error at or near "varchar"
SQL state: 42601
Character: 21
declare patient_mrn varchar;
declare patient_lname varchar;
declare patient_fname varchar;
declare patient_dob varchar;
declare patient_acct_number varchar;
declare msg_control_id varchar;
patient_mrn := '30006';
patient_lname := 'THOMAS'
patient_fname := 'PAMELA'
patient_dob := '19551006'
patient_acct_number := 'P0085465b'
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);
Remove the "DECLARE" keyword from those lines.
Here are some examples of variable declarations:
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;
ASKER
Thanks so much Steve
So I am really lost on this as I have looked at the man pages. I have looked at example code from websites such as http://www.techonthenet.com/postgresql/declare_vars.php
And when I take the example and simply cut and paste it into the SQL query tool in Postgres it still errors!! This is postgres 9.5 that I am working with.
DECLARE vSite varchar;
This example would declare a variable called vSite as a varchar data type.
Below is an example of how to declare a variable in PostgreSQL and give it an initial value. This is different from a constant in that the variable's value can be changed later.
DECLARE vSite varchar DEFAULT 'TechOnTheNet.com';
These fail for me. And doesn't matter if I change to character or char still fails.
So I am really lost on this as I have looked at the man pages. I have looked at example code from websites such as http://www.techonthenet.com/postgresql/declare_vars.php
And when I take the example and simply cut and paste it into the SQL query tool in Postgres it still errors!! This is postgres 9.5 that I am working with.
DECLARE vSite varchar;
This example would declare a variable called vSite as a varchar data type.
Below is an example of how to declare a variable in PostgreSQL and give it an initial value. This is different from a constant in that the variable's value can be changed later.
DECLARE vSite varchar DEFAULT 'TechOnTheNet.com';
These fail for me. And doesn't matter if I change to character or char still fails.
The site you are looking at looks well-groomed and all, but they are obviously giving bad advice if their instructions contradict the actual manual. I would stick to a more authoritative source for your education. Per the official docs, the DECLARE keyword is used to define a cursor, which is a very specific kind of data structure.
The page I sent you to previously covered how to create a variable in pgSQL. The actual method you need will vary depending on the language you are using, but pgSQL is probably the place to start. Have you tried using the templates I suggested, found at https://www.postgresql.org/docs/9.5/static/plpgsql-declarations.html ?
The page I sent you to previously covered how to create a variable in pgSQL. The actual method you need will vary depending on the language you are using, but pgSQL is probably the place to start. Have you tried using the templates I suggested, found at https://www.postgresql.org/docs/9.5/static/plpgsql-declarations.html ?
ASKER
Yes Sir I tried your example first and did copy directly out of the postgres manpage help files. And it errors. Perhaps I have to turn on some allowance in Postgres I am really unsure at this point.
Capture.JPG
Capture.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in psql the syntax is
\set VARIABLE_NAME VARIABLE_VALUE
\set var1 201
\set var2 'Hello World'
create table test2 ( id int, col1 text);
insert into test2(id,col1) values ( :var1, :'var2' );
select * from test2;
id | col1
-----+-------------
201 | Hello World
(1 row)
\set
VERSION = 'PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit'
var1 = '201'
var2 = 'Hello World'
\set VARIABLE_NAME VARIABLE_VALUE
\set var1 201
\set var2 'Hello World'
create table test2 ( id int, col1 text);
insert into test2(id,col1) values ( :var1, :'var2' );
select * from test2;
id | col1
-----+-------------
201 | Hello World
(1 row)
\set
VERSION = 'PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit'
var1 = '201'
var2 = 'Hello World'
In the version that you have (9.5) the select where not exists can be short cut by using the "UPSERT" syntax like
INSERT .... ON CONFLICT DO NOTHING;
INSERT .... ON CONFLICT DO NOTHING;