Plpgsql function syntax error at first coalesce statement - Mailing list pgsql-general

Hi all,

I'm trying to write my first plpgsql function and I'm running into a
problem that may or may not have to do with a coalesce statement.

I wrote a very similar sql function that does basically the same thing
for just one trainer where I pass in an id number and that one works fine.

I re-used  much of the code from that one to write this plpgsql function
that is supposed to retrieve all trainers. I'm using the example in 38.6.4

http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html#PLPGSQL-RECORDS-ITERATING

as my basic template.

CREATE or replace FUNCTION view_all_trainers()
   returns table (
     pp_id integer,
     tr_id integer,
     pp_first_name text,
     pp_last_name text,
     pp_address text,
     pp_city text,
     pp_state text,
     pp_zip text,
     pp_county text,
     email text,
     phone text,
     status text,
     availability text,
     west_ed boolean,
     cda boolean,
     blood_borne boolean,
     fire_safety boolean,
     med_admin boolean,
     first_aid_cpr boolean,
     child_abuse boolean,
     staff_orientation boolean,
     cacfp boolean,
     other boolean,
     "HNS" boolean,
     "ALE" boolean,
     "CGD" boolean,
     "G&D" boolean,
     "FR" boolean,
     "PM" boolean,
     "P" boolean,
     "UCA" boolean) AS $$
   DECLARE
     trainer RECORD;
   BEGIN
     FOR trainer IN SELECT tr_pp_id FROM trainers where tr_pp_id is not null
       LOOP
         SELECT
           pp_id,
           tr_id,
           pp_first_name,
           pp_last_name,
           pp_address,
           pp_city,
           pp_state,
           pp_zip,
           pp_county,
           coalesce(pp_email,'No E-Mail Address') as email,
           coalesce(to_char(pp_work_phone::bigint,'FM(999)
999-9999'),'No Work Phone')
             || coalesce(' Ext. ' || pp_work_phone_extension,'') as phone,
           tr_date_name as status,
           case
             when (select trs_tr_will_train from trainers_trainer_will_train
                where trs_tr_will_train_pp_id = trainer.tr_pp_id)  > 1
then 'Any Location'
             else 'In House Only'
           end as availability,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 1 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as west_ed,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 2 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as cda,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 3 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as blood_borne,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 4 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as fire_safety,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 5 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as med_admin,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 6 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as first_aid_cpr,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 7 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as child_abuse,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 8 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as staff_orientation,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 9 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as cacfp,
           case
             when (select trs_tr_cat_id from trainers_trainer_categories
               where trs_tr_cat_id = 10 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as other,
           case
             when (select sum(trs_tr_level) from trainers_trainer_levels
               where trs_tr_level_core_area_id = 1 and
               trs_tr_level_pp_id = trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as "HNS",
           case
             when (select sum(trs_tr_level) from trainers_trainer_levels
               where trs_tr_level_core_area_id = 2 and
               trs_tr_level_pp_id = trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as "ALE",
           case
             when (select sum(trs_tr_level) from trainers_trainer_levels
               where trs_tr_level_core_area_id = 3 and
               trs_tr_level_pp_id = trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as "CGD",
           case
             when (select sum(trs_tr_level) from trainers_trainer_levels
               where trs_tr_level_core_area_id = 4 and
               trs_tr_level_pp_id = trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as "G&D",
           case
             when (select sum(trs_tr_level) from trainers_trainer_levels
               where trs_tr_level_core_area_id = 5 and
               trs_tr_level_pp_id = trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as "FR",
           case
             when (select sum(trs_tr_level) from trainers_trainer_levels
               where trs_tr_level_core_area_id = 6 and
               trs_tr_level_pp_id = trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as "PM",
           case
             when (select sum(trs_tr_level) from trainers_trainer_levels
               where trs_tr_level_core_area_id = 7 and
               trs_tr_level_pp_id = trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as "P",
           case
             when (select sum(trs_tr_level) from trainers_trainer_levels
               where trs_tr_level_core_area_id = 8 and
               trs_tr_level_pp_id = trainer.tr_pp_id)
               is not null then 't'::boolean
             else 'f'::boolean
           end as "UCA"
           from people
           join trainers on pp_id = tr_pp_id
           join trainers_trainer_dates as ttd1 on pp_id = trs_tr_date_pp_id
           join trainer_dates on tr_date_id = trs_tr_date_id
           WHERE NOT EXISTS (
             SELECT * FROM trainers_trainer_dates as ttd2 where
             ttd1.trs_tr_date_pp_id = ttd2.trs_tr_date_pp_id and
             ttd1.trs_tr_date_recorded < ttd2.trs_tr_date_recorded
           )
           and  tr_date_name not in ('Application Date','Last Updated Date')
           and pp_id = trainer.tr_pp_id;
         END LOOP;
         RETURN;
     END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION view_all_trainers() TO GROUP wykids_users;
GRANT EXECUTE ON FUNCTION view_all_trainers() TO GROUP www;

When I try to run this I get the following error:

jross@acer:/var/www/stars/sql $ psql -f view_all_trainers.sql wykids

psql:view_all_trainers.sql:189: ERROR:  syntax error at or near "$10"
LINE 1: ...  $9 , coalesce(pp_email,'No E-Mail Address') as  $10 , coal...
                                                              ^
QUERY:  SELECT  $1 ,  $2 ,  $3 ,  $4 ,  $5 ,  $6 ,  $7 ,  $8 ,  $9 ,
coalesce(pp_email,'No E-Mail Address') as  $10 ,
coalesce(to_char(pp_work_phone::bigint,'FM(999) 999-9999'),'No Work
Phone') || coalesce(' Ext. ' || pp_work_phone_extension,'') as  $11 ,
tr_date_name as  $12 , case when (select trs_tr_will_train from
trainers_trainer_will_train where trs_tr_will_train_pp_id =  $13 ) > 1
then 'Any Location' else 'In House Only' end as  $14 , case when (select
trs_tr_cat_id from trainers_trainer_categories where trs_tr_cat_id = 1
and trs_tr_cat_pp_id =  $15 ) is not null then 't'::boolean else
'f'::boolean end as  $16 , case when (select trs_tr_cat_id from
trainers_trainer_categories where trs_tr_cat_id = 2 and trs_tr_cat_pp_id
=  $17 ) is not null then 't'::boolean else 'f'::boolean end as  $18 ,
case when (select trs_tr_cat_id from trainers_trainer_categories where
trs_tr_cat_id = 3 and trs_tr_cat_pp_id =  $19 ) is not null then
't'::boolean else 'f'::boolean end as  $20 , case when (select
trs_tr_cat_id from trainers_trainer_categories where trs_tr_cat_id = 4
and trs_tr_cat_pp_id =  $21 ) is not null then 't'::boolean else
'f'::boolean end as  $22 , case when (select trs_tr_cat_id from
trainers_trainer_categories where trs_tr_cat_id = 5 and trs_tr_cat_pp_id
=  $23 ) is not null then 't'::boolean else 'f'::boolean end as  $24 ,
case when (select trs_tr_cat_id from trainers_trainer_categories where
trs_tr_cat_id = 6 and trs_tr_cat_pp_id =  $25 ) is not null then
't'::boolean else 'f'::boolean end as  $26 , case when (select
trs_tr_cat_id from trainers_trainer_categories where trs_tr_cat_id = 7
and trs_tr_cat_pp_id =  $27 ) is not null then 't'::boolean else
'f'::boolean end as  $28 , case when (select trs_tr_cat_id from
trainers_trainer_categories where trs_tr_cat_id = 8 and trs_tr_cat_pp_id
=  $29 ) is not null then 't'::boolean else 'f'::boolean end as  $30 ,
case when (select trs_tr_cat_id from trainers_trainer_categories where
trs_tr_cat_id = 9 and trs_tr_cat_pp_id =  $31 ) is not null then
't'::boolean else 'f'::boolean end as  $32 , case when (select
trs_tr_cat_id from trainers_trainer_categories where trs_tr_cat_id = 10
and trs_tr_cat_pp_id =  $33 ) is not null then 't'::boolean else
'f'::boolean end as  $34 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 1 and
trs_tr_level_pp_id =  $35 ) is not null then 't'::boolean else
'f'::boolean end as  $36 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 2 and
trs_tr_level_pp_id =  $37 ) is not null then 't'::boolean else
'f'::boolean end as  $38 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 3 and
trs_tr_level_pp_id =  $39 ) is not null then 't'::boolean else
'f'::boolean end as  $40 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 4 and
trs_tr_level_pp_id =  $41 ) is not null then 't'::boolean else
'f'::boolean end as  $42 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 5 and
trs_tr_level_pp_id =  $43 ) is not null then 't'::boolean else
'f'::boolean end as  $44 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 6 and
trs_tr_level_pp_id =  $45 ) is not null then 't'::boolean else
'f'::boolean end as  $46 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 7 and
trs_tr_level_pp_id =  $47 ) is not null then 't'::boolean else
'f'::boolean end as  $48 , case when (select sum(trs_tr_level) from
trainers_trainer_levels where trs_tr_level_core_area_id = 8 and
trs_tr_level_pp_id =  $49 ) is not null then 't'::boolean else
'f'::boolean end as  $50  from people join trainers on  $1  = tr_pp_id
join trainers_trainer_dates as ttd1 on  $1  = trs_tr_date_pp_id join
trainer_dates on tr_date_id = trs_tr_date_id WHERE NOT EXISTS ( SELECT *
FROM trainers_trainer_dates as ttd2 where ttd1.trs_tr_date_pp_id =
ttd2.trs_tr_date_pp_id and ttd1.trs_tr_date_recorded <
ttd2.trs_tr_date_recorded ) and tr_date_name not in ('Application
Date','Last Updated Date') and  $1  =  $51
CONTEXT:  SQL statement in PL/PgSQL function "view_all_trainers" near
line 151

I'm sure this is something simple but I've been staring at it so long
now I can't see the forest for the trees.

Thanks!

Jeff Ross