Обсуждение: 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