question on setof record returning plpgsql function

From: "Julie May" <julie(at)ccorb(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: question on setof record returning plpgsql function
Date: 2003-10-08 17:41:46
Message-ID: 013901c38dc3$7206c010$a1d2a8c0@impsu.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;
</not working code>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Grant Rutherford 2003-10-08 17:48:09 Displaying a float8 as a full-length number
Previous Message Diogo Biazus 2003-10-08 17:31:03 Re: Humor me: Postgresql vs. MySql (esp. licensing)