Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PostgreSQL Syntax

Posted on 2016-07-18
12
Medium Priority
?
240 Views
Last Modified: 2016-07-23
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');

Open in new window


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);

Open in new window


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.
0
Comment
Question by:mrrmpc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41719404
Can you elaborate on "does not work"?
0
 

Author Comment

by:mrrmpc
ID: 41719425
Syntax Error Screen Shot
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41719434
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 32

Expert Comment

by:awking00
ID: 41719482
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';
0
 

Author Comment

by:mrrmpc
ID: 41719513
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

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);

Open in new window

0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41719613
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;
0
 

Author Comment

by:mrrmpc
ID: 41721866
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.
Postgres 9.5 error
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41721923
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 ?
0
 

Author Comment

by:mrrmpc
ID: 41721982
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
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 2000 total points
ID: 41722059
So, I installed Postgresql 9.5 on my local VM and did some playing around.  I found that the DECLARE is actually necessary, but the missing part is that it appears variable declarations are part of procedural code.  That means you can't just declare a variable on the command line - you need a defined block of code.  That block of code can be anything from a FUNCTION or PROCEDURE declaration, to something called an anonymous code block.  

There is a further restriction, depending on the client you are using.  For example, when I used this function declaration in HeidiSQL, this is what I got:Postgres example from HeidiSQLWhen I tried the same thing in pgsql:
postgres=# create function test_fn() returns int as $$
postgres$#     declare val int := 2;
postgres$#     begin
postgres$#         return (SELECT val);
postgres$#     end;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select test_fn();
 test_fn
---------
       2
(1 row)

Open in new window

This is because many clients will parse your SQL by the ';' character, sending each token to the server as its own query.  This doesn't work well for Postgres, which maintains a sort of "state" in the commands you submit.  

So, to sum up:
1) Must be in a code block
2) Use DECLARE
3) Verify validity in command line
0
 
LVL 22

Expert Comment

by:earth man2
ID: 41725582
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'
0
 
LVL 22

Expert Comment

by:earth man2
ID: 41725828
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;
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question