Обсуждение: question on setof record returning plpgsql function

Julie May wrote:
> What I would like to do is also return the date that is assigned to
> d_date for the current iteration of the first loop. The following
> code does not work. I either get one of three error messages
> depending on how many quote marks I use (unterminated string, error
> with $1, or unexpected return type). And suggestions would be greatly
> appreciated.
>

I think you had it working when you got the unexpected return type
message, you were just calling it wrong. The data types must match
*exactly* including WITH/WITHOUT TIME ZONE. I often find it simpler when
working with date/time data types to use an explicit cast, e.g.:

create table load_info_table(delivery_date date, clean_weight float8,
dirty_weight float8);
insert into load_info_table values('01/01/2003',8,10);
insert into load_info_table values('01/01/2003',9,11);
insert into load_info_table values('01/01/2003',10,12);
insert into load_info_table values('01/01/2003',7,8);
insert into load_info_table values('01/02/2003',18,20);
insert into load_info_table values('01/02/2003',29,36);
insert into load_info_table values('01/02/2003',9,15);

-- Function: public.get_factory_ytd()
CREATE or replace FUNCTION public.get_factory_ytd() RETURNS setof record
AS '
declare
        ytd_record record;
        d_date record;
begin
   for d_date in select distinct delivery_date from load_info_table
order by delivery_date asc loop
       for ytd_record in execute
       ''select sum(clean_weight)/sum(dirty_weight) as tare,
''''''||d_date.delivery_date|| ''''''::date from load_info_table where
delivery_date <= ''''''||d_date.delivery_date|| '''''''' loop
       return next ytd_record ;
     end loop;
   end loop;
  return;
end'  LANGUAGE 'plpgsql' VOLATILE;

test=# select * from get_factory_ytd() as (tare float8, delivery_date date);
        tare        | delivery_date
-------------------+---------------
  0.829268292682927 | 2003-01-01
  0.803571428571429 | 2003-01-02
(2 rows)

Note the ::date that I stuck in the function and how I specified
delivery_date as type "date" in the query.

HTH,

Joe

 Joe,

 Thank you very much. I didn't even think of casting the result of the first
 loop. I will test out the function tomorrow with the cast included and let
 you know how it works. From the looks of it (your results) it should work
 fine.

 Joe Conway Wrote:
I think you had it working when you got the unexpected return type
message, you were just calling it wrong. The data types must match
*exactly* including WITH/WITHOUT TIME ZONE. I often find it simpler when
working with date/time data types to use an explicit cast, e.g.:

-- Function: public.get_factory_ytd()
CREATE or replace FUNCTION public.get_factory_ytd() RETURNS setof record
AS '
declare
        ytd_record record;
        d_date record;
begin
   for d_date in select distinct delivery_date from load_info_table
order by delivery_date asc loop
       for ytd_record in execute
       ''select sum(clean_weight)/sum(dirty_weight) as tare,
''''''||d_date.delivery_date|| ''''''::date from load_info_table where
delivery_date <= ''''''||d_date.delivery_date|| '''''''' loop
       return next ytd_record ;
     end loop;
   end loop;
  return;
end'  LANGUAGE 'plpgsql' VOLATILE;

test=# select * from get_factory_ytd() as (tare float8, delivery_date date);
        tare        | delivery_date
-------------------+---------------
  0.829268292682927 | 2003-01-01
  0.803571428571429 | 2003-01-02
(2 rows)

Note the ::date that I stuck in the function and how I specified
delivery_date as type "date" in the query.

HTH,

Joe