473,230 Members | 1,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

question on setof record returning plpgsql function

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
1 8171
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Christopher Murtagh | last post by:
Greetings, I've got a fairly simple function that I'm trying to return a set, but seem to have come across a stumbling block. When I execute the function below (the input params are not used at...
12
by: Alex | last post by:
Hi, I have a column with a sequence in a table and want to allocate the first 200 for special purpose thus starting the sequence from 200. For the first 200 I have entries that will be added and...
9
by: Ron St-Pierre | last post by:
On a daily basis I place a lot of data into the empty table dailyList, and from that data update certain fields in currentList. I thought that using a function would be a good way to do this(?)....
1
by: Pascal Polleunus | last post by:
Hi, I'm trying to return a RECORD from a function, but when I try to use the variable I have the following error: ERROR: record "r" has no field "id" Here's an example: CREATE OR...
16
by: Froggy / Froggy Corp. | last post by:
Hello everyone, I try to see if i can make a recursive function with a trigger set on INSERT and doing an insert under my trigger function. So i wrote a test function : CREATE OR REPLACE...
1
by: Chris Ochs | last post by:
I read the docs but I'm still a little lost as to how to do this. I have this function which works fine. CREATE OR REPLACE FUNCTION lookup_customer_byemail(varchar) RETURNS SETOF customer_rec...
2
by: Vladimir M | last post by:
Hi I am writing a function with PL/pgSQL, which returns result of some complex query with several tables. In manual i found such example: CREATE OR REPLACE FUNCTION Test() RETURNS SETOF...
6
by: twinklyblue | last post by:
Hi thescripts team, I would like to ask for your help once again regarding returning set of records in plpgsql. I followed examples presented in other sites regarding this function but my script...
2
by: ycjack | last post by:
Hi, all Following is a simple function which returns type of setof: CREATE OR REPLACE FUNCTION tesp_report_query() RETURNS SETOF rpt.tesp_report_query AS $BODY$ DECLARE rec record; BEGIN...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.