Plpgsql function syntax error at first coalesce statement
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
Jeff Ross <jross@wykids.org> writes:
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.
No, it's not the coalesce ...
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...
^
The problem here is that you've got a collision between a plpgsql
parameter name (email) and a name you are trying to use in the SELECT
statement for a different purpose ("as email" is trying to label a
result column of the SELECT). plpgsql isn't bright enough to figure
out that you didn't mean for it to substitute the parameter's value
into the SELECT at that point, so it tries to do so, via the "$10"
you can see there. (This will get improved in PG 9.0, but that
doesn't help you today.)
You need to avoid such naming conflicts. In this particular case
it might be practical to just drop the AS clauses. In general it's
a good plan to use a separate naming convention for parameters and
plpgsql variables, such as prepending "p_" or "v_" to their names.
regards, tom lane
On 04/25/10 12:32, Tom Lane wrote:
Jeff Ross<jross@wykids.org> writes:
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.No, it's not the coalesce ...
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...
^The problem here is that you've got a collision between a plpgsql
parameter name (email) and a name you are trying to use in the SELECT
statement for a different purpose ("as email" is trying to label a
result column of the SELECT). plpgsql isn't bright enough to figure
out that you didn't mean for it to substitute the parameter's value
into the SELECT at that point, so it tries to do so, via the "$10"
you can see there. (This will get improved in PG 9.0, but that
doesn't help you today.)You need to avoid such naming conflicts. In this particular case
it might be practical to just drop the AS clauses. In general it's
a good plan to use a separate naming convention for parameters and
plpgsql variables, such as prepending "p_" or "v_" to their names.regards, tom lane
Thanks as always, Tom.
I dropped the AS clauses and it runs and makes a function but now I have
a different error:
wykids=# select * from view_all_trainers();
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function "view_all_trainers" line 6 at SQL statement
Now I'm *really* confused. I thought the table structure I created at
the beginning of the function was where the results would be returned
to. I tried a variety of queries including select into and create table
but they didn't work either.
Jeff
On 25/04/2010 20:50, Jeff Ross wrote:
Now I'm *really* confused. I thought the table structure I created at
the beginning of the function was where the results would be returned
to. I tried a variety of queries including select into and create table
but they didn't work either.
I think you have to do RETURN NEXT inside the loop:
create function....
declare
return_row record;
...
begin
...
for..
loop
select ... into return_row;
return next return_row;
end loop;
...
return;
end;
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
Jeff Ross <jross@wykids.org> writes:
Now I'm *really* confused. I thought the table structure I created at
the beginning of the function was where the results would be returned
to.
Uh, you're using that as the destination for the FOR loop's SELECT.
What exactly is the purpose of having a second SELECT within the loop?
regards, tom lane
On 04/25/10 14:20, Tom Lane wrote:
Jeff Ross<jross@wykids.org> writes:
Now I'm *really* confused. I thought the table structure I created at
the beginning of the function was where the results would be returned
to.Uh, you're using that as the destination for the FOR loop's SELECT.
What exactly is the purpose of having a second SELECT within the loop?regards, tom lane
I am only an egg.
How else do I get the results I want--name, address, city, state, and so
on through the list out to whatever calls this function?
Jeff
Jeff Ross <jross@wykids.org> writes:
On 04/25/10 14:20, Tom Lane wrote:
Uh, you're using that as the destination for the FOR loop's SELECT.
What exactly is the purpose of having a second SELECT within the loop?
How else do I get the results I want--name, address, city, state, and so
on through the list out to whatever calls this function?
Well, you could do
SELECT all-that-stuff INTO some-record-variable FROM ...;
RETURN NEXT some-record-variable;
which is more or less what the error message is suggesting.
Or you could merge the computations you want into the first SELECT
(the one in the FOR) and just RETURN NEXT directly from the FOR's
loop variable, instead of having two record variables.
Or you could eliminate the explicit loop altogether and just RETURN
QUERY one-big-query (if you're using a PG version new enough to have
RETURN QUERY).
regards, tom lane