Problems using pgScript

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 = @people[@people_row][0];  set @par_refno = @people[@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::textas 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[0][@column] = @updates[@updates_row][@column];      set @column
=@column + 1;    end
 
    if @updates[@updates_row]['parties_data_type_of_contact'] = 
'TELEPHONE' or @updates[@updates_row]['parties_data_type_of_contact'] = 
'HOMETEL'       or @updates[@updates_row]['parties_data_type_of_contact'] = 
'TEL' or @updates[@updates_row]['parties_data_type_of_contact'] = 
'CONTACTTEL'    begin      set @updates[0]['home_phone'] = 
@updates[@updates_row]['parties_data_telephone_number'];    end    if
@updates[@updates_row]['parties_data_type_of_contact']= 
 
'WORKTEL' or @updates[@updates_row]['parties_data_type_of_contact'] = 
'DAYTEL'    begin      set @updates[0]['work_phone'] = 
@updates[@updates_row]['parties_data_telephone_number'];    end    if
@updates[@updates_row]['parties_data_type_of_contact']= 'MOBILE'    begin      set @updates[0]['mobile_phone'] = 
 
@updates[@updates_row]['parties_data_telephone_number'];    end    if
@updates[@updates_row]['parties_data_type_of_contact']= 'EMAIL'    begin      set @updates[0]['email'] = 
 
@updates[@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","","",""