Thread: question on setof record returning plpgsql function
I have 90% of my function working and I know to get the next 10% it is just a matter of getting the quotations and the escaping quotations correct. Here is the portion that does work:
<working code>
-- 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 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;
</working code>
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.
Thanks,
Julie
<not working code>
-- 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|| '''''' 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;
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|| '''''' 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;
</not working code>
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