Hi
I have been trying to use pgScript to do something I would normally do
by writing a function. It very nearly worked perfectly except I got
stuck with a problem where the script would run indefinitely and could
not be interrupted.
If you find the line "UNCOMMENTING THE LINE BELOW CAUSES THE QUERY TO
NEVER FINISH AND IS NOT CANCELLABLE" and uncomment the line below then
the pgScript stops working.
I was also wondering whether this is a valid use at all, in particular :-
1. How are you meant to get a record from a table and insert a the
contents of a text column into another table? What would happen if the
column data contained quotes?
2. Is it true that you can't put a column from a record into a sql
command without first assigning it to a simple variable? This seems a
little restrictive.
Sorry about the length of the script by the way.
Many Thanks
Kieran
The script :-
drop table if exists t_allresidents cascade;
create temp table t_allresidents
(
id integer,
prop_ref text,
type_of_client text,
tenancy_refno text,
non_resident text,
d_address text,
prop_type text,
account_name text,
account_pay_ref text,
res_ind text,
org_ind text,
ownership text,
sold_date text,
repairs_data_home_telephone_number text,
repairs_data_contact_telephone_number text,
repairs_data_contact_telephone_number_extension text,
par_refno text,
title text,
initials text,
forename text,
other_name text,
surname text,
date_of_birth text,
lanugage text,
gender text,
ethnicity text,
disabled_ind text,
parties_data_telephone_number text,
parties_data_type_of_contact text,
parties_data_telephone_number_extension text,
parties_data_allow_texts text,
parties_data_comments text
);
-- Load the data into the temporary table
*** Need to insert an appropriate copy from here to load test.csv (the
data is at the bottom of this file ****
drop table if exists all_residents;
create table all_residents (like t_allresidents,home_phone
text,work_phone text,mobile_phone text,email text,primary key (id));
set @table = 't_allresidents';
SET @people = SELECT distinct prop_ref,par_refno FROM @table where
parties_data_type_of_contact is not null order by 1,2 limit 1;
set @people_row = 0;
while @people_row < lines(@people)
begin
set @prop_ref = @peop...@people_row][0];
set @par_refno = @peop...@people_row][1];
set @updates =
SELECT
id,
prop_ref,
type_of_client,
tenancy_refno,
non_resident,
d_address,
prop_type,
account_name,
account_pay_ref,
res_ind,
org_ind,
ownership,
sold_date,
repairs_data_home_telephone_number,
repairs_data_contact_telephone_number,
repairs_data_contact_telephone_number_extension,
par_refno,
title,
initials,
forename,
other_name,
surname,
date_of_birth,
lanugage,
gender,
ethnicity,
disabled_ind,
parties_data_telephone_number,
parties_data_type_of_contact,
parties_data_telephone_number_extension,
parties_data_allow_texts,
parties_data_comments,
null::text as home_phone,
null::text as work_phone,
null::text as mobile_phone,
null::text as email
FROM @table where prop_ref = '@prop_ref' and par_refno = '@par_refno'
order by id;
set @updates_row = 0;
while @updates_row < lines(@updates)
begin
set @column = 0;
while @column < columns(@updates) - 4 -- To leave the 4 extra
telephone columns untouched
begin
set @updates[...@column] = @updat...@updates_row][@column];
set @column = @column + 1;
end
if @updat...@updates_row]['parties_data_type_of_contact'] =
'TELEPHONE' or @updat...@updates_row]['parties_data_type_of_contact'] =
'HOMETEL'
or @updat...@updates_row]['parties_data_type_of_contact'] =
'TEL' or @updat...@updates_row]['parties_data_type_of_contact'] =
'CONTACTTEL'
begin
set @updates[0]['home_phone'] =
@updat...@updates_row]['parties_data_telephone_number'];
end
if @updat...@updates_row]['parties_data_type_of_contact'] =
'WORKTEL' or @updat...@updates_row]['parties_data_type_of_contact'] =
'DAYTEL'
begin
set @updates[0]['work_phone'] =
@updat...@updates_row]['parties_data_telephone_number'];
end
if @updat...@updates_row]['parties_data_type_of_contact'] = 'MOBILE'
begin
set @updates[0]['mobile_phone'] =
@updat...@updates_row]['parties_data_telephone_number'];
end
if @updat...@updates_row]['parties_data_type_of_contact'] = 'EMAIL'
begin
set @updates[0]['email'] =
@updat...@updates_row]['parties_data_telephone_number'];
end
set @updates_row = @updates_row + 1;
end
set @id = @updates[0]['id'];
set @prop_ref = @updates[0]['prop_ref'];
set @type_of_client = @updates[0]['type_of_client'];
set @tenancy_refno = @updates[0]['tenancy_refno'];
set @non_resident = @updates[0]['non_resident'];
set @d_address = @updates[0]['d_address'];
set @prop_type = @updates[0]['prop_type'];
set @account_name = @updates[0]['account_name'];
set @account_pay_ref = @updates[0]['account_pay_ref'];
set @res_ind = @updates[0]['res_ind'];
set @org_ind = @updates[0]['org_ind'];
set @ownership = @updates[0]['ownership'];
set @sold_date = @updates[0]['sold_date'];
set @repairs_data_home_telephone_number =
@updates[0]['repairs_data_home_telephone_number'];
set @repairs_data_contact_telephone_number =
@updates[0]['repairs_data_contact_telephone_number'];
set @repairs_data_contact_telephone_number_extension =
@updates[0]['repairs_data_contact_telephone_number_extension'];
set @par_refno = @updates[0]['par_refno'];
set @title = @updates[0]['title'];
set @initials = @updates[0]['initials'];
set @forename = @updates[0]['forename'];
set @other_name = @updates[0]['other_name'];
set @surname = @updates[0]['surname'];
set @date_of_birth = @updates[0]['date_of_birth'];
set @lanugage = @updates[0]['lanugage'];
set @gender = @updates[0]['gender'];
set @ethnicity = @updates[0]['ethnicity'];
set @disabled_ind = @updates[0]['disabled_ind'];
set @parties_data_telephone_number =
@updates[0]['parties_data_telephone_number'];
set @parties_data_type_of_contact =
@updates[0]['parties_data_type_of_contact'];
set @parties_data_telephone_number_extension =
@updates[0]['parties_data_telephone_number_extension'];
set @parties_data_allow_texts = @updates[0]['parties_data_allow_texts'];
set @parties_data_comments = @updates[0]['parties_data_comments'];
set @home_phone = @updates[0]['home_phone'];
set @work_phone = @updates[0]['work_phone'];
set @mobile_phone = @updates[0]['mobile_phone'];
set @email = @updates[0]['email'];
print @id;
print @prop_ref;
print @type_of_client;
print @tenancy_refno;
print @non_resident;
print @d_address;
insert into all_residents
(
id, prop_ref, type_of_client, tenancy_refno, non_resident, d_address
/* ,prop_type, account_name, account_pay_ref, res_ind, org_ind, ownership
,sold_date, repairs_data_home_telephone_number,
repairs_data_contact_telephone_number
repairs_data_contact_telephone_number_extension, par_refno, title,
initials, forename, other_name, surname, date_of_birth, lanugage,
gender, ethnicity, disabled_ind, parties_data_telephone_number,
parties_data_type_of_contact, parties_data_telephone_number_extension,
parties_data_allow_texts, parties_data_comments, home_phone,
work_phone, mobile_phone, email
*/ )
VALUES (
@id, '@prop_ref', '@type_of_client', '@tenancy_refno',
'@non_resident', '@d_address'
/* ,'@prop_type', '@account_name', '@account_pay_ref', '@res_ind',
'@org_ind', '@ownership'
,'@sold_date', '@repairs_data_home_telephone_number',
'@repairs_data_contact_telephone_number'
'@repairs_data_contact_telephone_number_extension', '@par_refno',
'@title',
'@initials', '@forename', '@other_name', '@surname',
'@date_of_birth', '@lanugage',
'@gender', '@ethnicity', '@disabled_ind',
'@parties_data_telephone_number',
'@parties_data_type_of_contact',
'@parties_data_telephone_number_extension',
'@parties_data_allow_texts', '@parties_data_comments', '@home_phone',
'@work_phone', '@mobile_phone', '@email'
*/ );
print @prop_type;
update all_residents set prop_type = '@prop_type';
print @account_name;
-- UNCOMMENTING THE LINE BELOW CAUSES THE QUERY TO NEVER FINISH AND
IS NOT CANCELLABLE
-- update all_residents set account_name = '@account_name';
print @account_pay_ref;
print @res_ind;
print @org_ind;
print @ownership;
print @sold_date;
print @repairs_data_home_telephone_number;
print @repairs_data_contact_telephone_number;
print @repairs_data_contact_telephone_number_extension;
print @par_refno;
print @title;
print @initials;
print @forename;
print @other_name;
print @surname;
print @date_of_birth;
print @lanugage;
print @gender;
print @ethnicity;
print @disabled_ind;
print @parties_data_telephone_number;
print @parties_data_type_of_contact;
print @parties_data_telephone_number_extension;
print @parties_data_allow_texts;
print @parties_data_comments;
print @home_phone;
print @work_phone;
print @mobile_phone;
print @email;
set @people_row = @people_row + 1;
end
**************************************** the data for test.csv
"id","prop_ref","type_of_client","tenancy_refno","non_resident","d_address","prop_type","account_name","account_pay_ref","res_ind","org_ind","ownership","sold_date","repairs_data_home_telephone_number","repairs_data_contact_telephone_number","repairs_data_contact_telephone_number_extension","par_refno","title","initials","forename","other_name","surname","date_of_birth","lanugage","gender","ethnicity","disabled_ind","parties_data_telephone_number","parties_data_type_of_contact","parties_data_telephone_number_extension","parties_data_allow_texts","parties_data_comments"
6,"1089182","TENANT","25921","NO","24 Adder Avenue,Chester CH3
5UP","FLAT","MR A PERSON & MRS B
PERSON","4345995910","Y","Y","COUNCIL","","01244
898190","7900290934","","114481","MR","A","PERSON","","PERSON","24/05/45","ENG","M","UNKWNUNKWN","N","01244
898190","HOMETEL","","",""
7,"1089182","TENANT","25921","NO","24 Adder Avenue,Chester CH3
5UP","FLAT","MR A PERSON & MRS B
PERSON","4345995910","Y","Y","COUNCIL","","01244
898190","7900290934","","114481","MR","A","PERSON","","PERSON","24/05/45","ENG","M","UNKWNUNKWN","N","01244
898191","TELEPHONE","","",""
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
