Solved

Need Help With Writing postreSQL function

Posted on 2010-11-07
14
1,079 Views
Last Modified: 2012-05-10
Hi,

I'm coming from a MSSQL server background and am very new to postreSQL.  I'm trying to write a relatively simple function but keep getting error after error.  It seems that my declared strings are causing issues even though I've tried escaping special characters with \.  If someone could review the below function and point out where my issues are.  The most recent error I have is:

ERROR:  unterminated string
CONTEXT:  compile of PL/pgSQL function "inserttenant" near line 11

********** Error **********

ERROR: unterminated string
SQL state: 42804
Context: compile of PL/pgSQL function "inserttenant" near line 11

Also, is there a way to get the last inserted record into a table similar to t-sql @@identity? In the below code, I've been relegated to getting the max(id).

Thanks so much!
CREATE FUNCTION insertTena(nae character varying(50),con_nm character varying(50),ten_typ character varying(50),

			     pass_x character varying(100),spec character varying(50)) 

RETURNS character varying(50) AS $$

DECLARE



	description character varying(50) := "OK";

	--temporarily hard-code these values

	temp_type_code varchar(10) := "HO";

	temp_spec_code varchar(10);

	temp_last_ten_id int;

	temp_pass_msg varchar(255) := "Must be at least 6 characters. Must contain at least one lower case letter, one upper case letter, one digit and one special character. Valid special characters are -   !@#$%^&+=\"";

	temp_cas_rep_con varchar(1000) := "<?xml version=\"1.0\" encoding=\"UTF-8\"?><PROCEDURE_CASE_REPORT><PATIENT_INFO>true</PATIENT_INFO><SERVICE_INFO>true</SERVICE_INFO><PROCEDURE_STAFF>true</PROCEDURE_STAFF><ALLERGY>true</ALLERGY><DIAGNOSIS>true</DIAGNOSIS><VASCULAR_ACCESS>true</VASCULAR_ACCESS><COAG_STUDY>true</COAG_STUDY><MEDEICATION>true</MEDEICATION><VITALS>true</VITALS><RNNOTE>true</RNNOTE><NURSE_SIGN>true</NURSE_SIGN></PROCEDURE_CASE_REPORT>";

	temp_log_msg text := "Medical snooping into patient medical records is illegal and disrespectful.Employees are at personal risk for financial penalties, state licensing board action and may face later civil lawsuits. YOU could be fined up to $250,000! Privacy Reminders for All Workforce Members:!"	

BEGIN



	--get code

	select 	code into temp_type_code

	from 	zu_ten_type 	

	where	code = temp_tenant_type_code;



	--get scode

	select 	code into temp_spec_code

	from 	zu_spec 	

	where	description = spec;



	insert into tena(ids,"vs",cr_by,cr_dt,mod_by,mod,ten_ty,nam,pass_x,pass_msg, 

	  cs_rept_cfg,log_msg,obs,can_edit,dis_met_meas,time_cd)

	values (1,-1,now(),-1,now(),temp_type_code,nae,con_nm,null,null,null,null,null,null,null,null,null,null,null,null,pass_x,

		pass_msg,temp_cas_rep_con,temp_log_msg,"f","t","f","US/Pacific",null,null);



	--get last id of insert for below

	select	max(ids) into temp_last_ten_id

	from	tena;

	

	insert into ten_spec(ids,"vs",cr_by,cr_dt,mod_by,mod,spec_cd,ten_id,obs)

	values(1,-1,now(),-1,now(),temp_spec_code,temp_last_ten_id,"f");



	return description;

END;

$$ LANGUAGE plpgsql;

Open in new window

0
Comment
Question by:blue44
  • 8
  • 6
14 Comments
 
LVL 7

Expert Comment

by:Hatrix76
ID: 34082949
Hi, first, change all " for string constants into ' (no double, single). (see also: http://www.postgresql.org/docs/8.4/interactive/plpgsql-declarations.html)


then try again.

ID's in postgresql are sequences, a sequence, if you do not know yet, is like a internal counter type. You can define boundaries and how to increment, etc.

If you create a serial type in postgresql, a sequence is automatically created behind the scenes.

To get the last value of an insert, use:
currval();

if you want the last inserted value (in your session) from another table (sequence) use:
currval('sequence-name')

you can look at the sequences e.g. in pgadmin3,
see also: http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html

hope this helps,

best
0
 

Author Comment

by:blue44
ID: 34086822
Thanks for responding.

I tried the single quotes and it didn't make a difference so I decided to just assign those variables with select into.  This seemed to work. :-)  

I am, however, getting a new error which I don't understand.  I thought it was referencing passing the arguments into the insert statement so I changed them to $1, $2...but that didn't seem to work.  Any ideas?

ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "inserttenant" near line 48

Also, how do I get the sequence-name for curval?

Thanks, again!
CREATE FUNCTION insertTena(nae character varying(50),con_nm character varying(50),ten_typ character varying(50),

			     pass_x character varying(100),spec character varying(50)) 

RETURNS character varying(50) AS $$

DECLARE



	description character varying(50) := "OK";

	--temporarily hard-code these values

	temp_type_code varchar(10) := "HO";

	temp_spec_code varchar(10);

	temp_last_ten_id int;

	temp_pass_msg varchar(255); 

	temp_cas_rep_con varchar(1000);

	temp_log_msg text;	

BEGIN



	--get code

	select 	code into temp_type_code

	from 	zu_ten_type 	

	where	code = temp_tenant_type_code;



	--get scode

	select 	code into temp_spec_code

	from 	zu_spec 	

	where	description = spec;



	--get password message

	select 	pass_msg into temp_pass_msg

	from 	tena limit 1;



	--get case_report_config

	select 	cas_rep_con into temp_cas_rep_con

	from 	tena limit 1;



	--get temp_login_message text

	select 	log_msg into temp_log_msg

	from 	tena limit 1;



	insert into tena(ids,"vs",cr_by,cr_dt,mod_by,mod,ten_ty,nam,pass_x,pass_msg, 

	  cs_rept_cfg,log_msg,obs,can_edit,dis_met_meas,time_cd)

	values (1,-1,now(),-1,now(),temp_type_code,$1,$2,null,null,null,null,null,null,null,null,null,null,null,null,$4,

		pass_msg,temp_cas_rep_con,temp_log_msg,"f","t","f","US/Pacific",null,null);



	--get last id of insert for below

	select	max(ids) into temp_last_ten_id

	from	tena;

	

	insert into ten_spec(ids,"vs",cr_by,cr_dt,mod_by,mod,spec_cd,ten_id,obs)

	values(1,-1,now(),-1,now(),temp_spec_code,temp_last_ten_id,"f");



	return description;

END;

$$ LANGUAGE plpgsql;

Open in new window

0
 
LVL 7

Expert Comment

by:Hatrix76
ID: 34092133
Ok, let's first try the basic stuff:

* Change all occurances of ".." which represent string data, to '..'. the double quotes are normally used in postgres to quote fieldnames which are not standard conform (like, fieldnames with uppercase Letters, or which are defined with qutoes, etc.)

* curval, if you do not use a parameter with curval, it delivers the last inserted id, regardless of the table.

Are you using PGAdmin III to administrate the database? If not, download it, it's free. There you can see all sequences defined in your database.

Normally they are generated like <tablename>_<idfieldname>_seq

you can see them in pgadmin on the same level as functions and tables, etc.


please report back what postgreSQL does after you have changed all the quotes, from there on we will check further. And if possible go back to your first script.

best
Ray
0
 

Author Comment

by:blue44
ID: 34097599
Sorry for the delays in getting back to you but I think we are on very different time zones.

I was able to get the function to compile eventually after a lot of trial and error.  The issue ended up being that I was passing function parameters that were the same as the column insert names (i.e., pass_x).

As for currval(), I tried 'select currval(tena.id) from tena' where tena is the table name and id is the pk.

and received the following error message:

ERROR:  could not open relation with OID 1

********** Error **********

ERROR: could not open relation with OID 1
SQL state: XX000

Any ideas? Thanks!
0
 
LVL 7

Expert Comment

by:Hatrix76
ID: 34098147
try currval('tena_id_seq'); or just simple currval();

best
0
 

Author Comment

by:blue44
ID: 34098548
Thanks...it looks like I don't have that function installed.  

error message:
function currval() does not exist

Is there a package this function is part of similar to dblink?

Thanks!
0
 
LVL 7

Expert Comment

by:Hatrix76
ID: 34100000
No, this is a standard core function of postgresql ... that is very strange, which version of postgresql are you running?

It is possible that depending on the version, you have to use lastval() to get the last inserted value and that you can only use currval() with an actual sequence as a parameter.

try lastval() or currval('tena_id_seq') if the table is tena and the id field (primary key field) is id.

Just to be sure, you set this field as primary key in postgresql, haven't you?

You said that you are new to postgresql, how did you declare the this id field? In postgresql you need either to use the serial (or bigserial) type which will autocreate the sequence and everything needed. If you do not have used serial (or bigserial) you have to create this by yourself, look at: http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html, 8.1.4, serial types.

It should explain the postgresql behavior.

The reason for doing it that way and not with integer and autoincrement like in mysql is that for a lot of purposes one might only have one autoincremented value throughout the database, e.g. for replication it is way more efficient to use only one sequence and use this sequence for every table as id field. therefore you only have to replicate the sequence once and not as many times as you have tables.

best
Ray
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:blue44
ID: 34108374
The version is: "PostgreSQL 8.3.11 on x86_64-pc-linux-gnu"

I confirmed that the primary key of tena is id.  The remaining parameters of the id column are:
Name: id
Position: 1
Data Type: bigint
Default: nextval(('ten_id_seq'::text)::regclass)
Sequence:
Not Null? Yes
Primary Key: Yes
Foreign Key: No
Storage: PLAIN
Inherited: No
Statistics: -1
System Column: No
Comment:

I tried both lastval() and currval() again and got the following error:

select currval('tena_id_seq') from tenant

********** Error **********

ERROR: currval of sequence "tenant_id_seq" is not yet defined in this session
SQL state: 55000

Hopefully this tells you something :-)

Thanks!!
0
 
LVL 7

Expert Comment

by:Hatrix76
ID: 34109544
ah, ok, just try:

select currval('ten_id_seq'::regclass);

possible this is working also:
select currval('ten_id_seq');

if both have troubles you may have to specify:
select currval(('ten_id_seq'::text)::regclass);

8.3 is really quite old, is there a way you can upgrade to latest 8.4 or latest 9.0?

the sequence is not part of the table, therefore you do not do a "from <table>" thereafter.

Why the sequence is named ten_id_seq when the tablename is tena I do not know, is it possible that the table was created with ten and then renamed to tena? If so, it's ok, because sequences don't get renamed if table-names change.

best regards
Ray


0
 
LVL 7

Expert Comment

by:Hatrix76
ID: 34109548
ah, replace "you do not do" with "you do not need to do" regarding the "from <table>".

0
 

Author Comment

by:blue44
ID: 34118495
I tried all the variations and still get this error.  The table name is tena btw:-(

********** Error **********

ERROR: currval of sequence "tena_id_seq" is not yet defined in this session
SQL state: 55000

I spoke with the client and they're not open to upgrading at this point, unfortunately.

Thanks!
0
 
LVL 7

Accepted Solution

by:
Hatrix76 earned 500 total points
ID: 34118636
Ah, ok, sure, now it's clear whats going on ...

the sequence is tena_id_seq, that's fine, but you can't use currval('<seq>') or lastval() until you actually inserted a value in table for <seq> or in any table if you use lastval().

This is what "currval of sequence not yet defined" means.

You can play around with it by adding a new value like this:

select nextval('tena_id_seq');

this will retrieve the next id for this sequence.

Then you can retrieve it via:

select currval('tena_id_seq');
or
select lastval();

don't worry about the number you retrieve, it's normal that sequence numbers have wholes in them, it's because they have to be unique, so if you have a transaction which get's rollbacked because of an error or per design, the number will be lost as well!

best
Ray
0
 

Author Comment

by:blue44
ID: 34128516
Bingo...that worked!! Thanks for all your help.  I'm sure I'll be back soon with other brain-scratching postgres questions.

Have a good day!
0
 
LVL 7

Expert Comment

by:Hatrix76
ID: 34129044
glad I could help,

best
Ray
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
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.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now