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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
