By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

question on setof record returning plpgsql function

P: n/a
I have 90% of my function working and I know to get the next 10% it is justa 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 bydelivery_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 bydelivery_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>
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.