Hi,
I am trying to get my feet wet in SRF's
I had to define a type in order to get my first attempt
at a srf for an sql language function ie..
create type annual_report_t ype as
( category text,
jan numeric(9,2),
feb numeric(9,2),
mar numeric(9,2),
apr numeric(9,2),
may numeric(9,2),
jun numeric(9,2),
jul numeric(9,2),
aug numeric(9,2),
sep numeric(9,2),
oct numeric(9,2),
nov numeric(9,2),
dec numeric(9,2) ,
total numeric(9,2) )
and then use this type as
create or replace function annual_report(i nteger) returns setof
annual_report_t ype
as '
select a.category, (select sum(amount) from all_accounts where
category=a.cate gory and
extract (month from date) = 1 and extract (year from date) = $1) as
jan,
(select sum(amount) from all_accounts where category=a.cate gory and
extract (month from date) = 2 and extract (year from date) = $1) as
feb,
....
....
(select sum(amount) from all_accounts where category=a.cate gory and
extract (year from date) = $1) as total
from all_accounts a
group by category
order by category
' language sql
The above seems to be working fine...
I would feel a bit more comfortable if I could recover the definition
of the type at a later time, I cannot seem to find the definition of
the type in pg_type (there is an entry but the definition does not seem
to
be visible).
It does not seem possible to replace "annual_report_ type" in the
function
definition with just the type...All of the placements fail for me in
any case.
Any suggestions as to how I can remember the rowtype? or (embed the
definiton
of the type in the definition of the function without having to create
an explicit type?
Jerry
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org 4 1166
Jerry LeVan <je*********@ek u.edu> writes: I would feel a bit more comfortable if I could recover the definition of the type at a later time,
Try "\d annual_report_t ype" in psql.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Doh, I was using \dT and \dT+....
Thanks
Jerry
On Aug 31, 2004, at 3:14 PM, Tom Lane wrote: Jerry LeVan <je*********@ek u.edu> writes: I would feel a bit more comfortable if I could recover the definition of the type at a later time,
Try "\d annual_report_t ype" in psql.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Your query looks suspiciously complicated...
Why not process all 12 months in one shot with something like this :
- only one subquery
- no join
- date between can make an index scan on date
select category, sum(amount) as sum_amount, extract (month from date) as
month
from all_accounts where (date between beginning of the year and end of
the year)
group by category,month order by category,month )
Not what you wanted but probably massively faster.
Or you can do this (in approximate SQL):
create type annual_report_t ype as
( sums numeric(9,2)[12] );
create type my_type as ( month integer, amount numeric );
CREATE AGGREGATE my_sum
takes one input which is my_type and sums the amount into the month column
of annual_report_t ype
Then :
select category, my_sum( my_type(month,a mount) as report, extract (month
from date) as month
from all_accounts where (date between beginning of the year and end of
the year)
group by category,month order by category,month )
Dunno if this would work, it would be nice I think.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Thank you for the response Pierre,
select category, sum(amount) as sum_amount, extract (month from date)
as month
from all_accounts where (extract(year from date)=2003)
group by category,month order by category,month
is certainly much faster than what I am doing but as you pointed out,
I want the table to have a column for each month ( and a grand total
as the last column).
I have not used arrays and aggregates, I will take a look....
Jerry
On Sep 1, 2004, at 3:03 AM, Pierre-Frédéric Caillaud wrote: Your query looks suspiciously complicated... Why not process all 12 months in one shot with something like this : - only one subquery - no join - date between can make an index scan on date
select category, sum(amount) as sum_amount, extract (month from date) as month from all_accounts where (date between beginning of the year and end of the year) group by category,month order by category,month )
Not what you wanted but probably massively faster.
Or you can do this (in approximate SQL):
create type annual_report_t ype as ( sums numeric(9,2)[12] );
create type my_type as ( month integer, amount numeric );
CREATE AGGREGATE my_sum takes one input which is my_type and sums the amount into the month column of annual_report_t ype
Then : select category, my_sum( my_type(month,a mount) as report, extract (month from date) as month from all_accounts where (date between beginning of the year and end of the year) group by category,month order by category,month )
Dunno if this would work, it would be nice I think.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jeff Davis |
last post by:
in section _37.7.1.2. RETURN NEXT_ of the docs, it says that "PL/pgSQL
stores the entire result set before returning from the function".
Is the same true for C, and if so, should we document it in
_33.7.9. Returning Sets from C-Language Functions_ ?
It could be important if someone wanted to return a huge amount of data
from an SRF and it was larger than available RAM.
Regards,
|
by: infobahn |
last post by:
printf("%p\n", (void *)0); /* UB, or not? Please explain your answer. */
|
by: Zach |
last post by:
When it is being said that, "value types are created on the stack or inline as part of an
object". If a value type is created in an object, and that object is being
called, the value type in that object, is still created on the stack, I
would say, so I don't understand this inline business. Apart from the fact
that it is my understanding that "inline" as it exists in C++ doesn't exist in C#. Could someone please shed some light on this...
|
by: Leonid |
last post by:
Hello,
I develop Web Server using ATL in VC++ .NET and I can
access it from the same computer using IE and SRF
file: 'http://localhost/ATLServerApp/ATLServerApp.srf'.
How can I access this Server from another computer
connected to LAN? What particular address do I need to
provide for IE on that remote computer?
Thanks for the help,
|
by: Együd Csaba |
last post by:
Hi All,
I'd like to write an SRF but I'm block a strange error message
WARNING: plpgsql: ERROR during compile of check_close2 near line 11
ERROR: return type mismatch in function returning tuple at or near ";"
I get this message even if the return rowset contains 0 or more tuples. What
is done wrong?
The code:
-----------------------------------------------------------------
| |
by: jacob navia |
last post by:
Hi people
I have been working again in my tutorial, and I have finished the
"types" chapter. If you feel like "jacob bashing" this is the
occasion! I am asking for criticisms, or for things I may have said
wrong. Keep in mind that this is a tutorial, and I donot want to
overwhelm the reader with little details.
-------------------------------------------------------------------------*
Types
|
by: sophia.agnes |
last post by:
Dear all,
what are the major expression types in c?
i have seen the following types of expressions
1) constant expressions
2) integral expressions
3) float expressions
4) pointer expressions
|
by: tonytech08 |
last post by:
How valuable is it that class objects behave like built-in types? I
appears that the whole "constructor doesn't return a value because
they are called by the compiler" thing is to enable built-in-like
behavior for objects of class type. That leads to the "necessity" for
the exception machinery so that errors from constructors can be
handled. Is all that complexity worth it just to get built-in-like
behavior from class objects? Maybe a...
|
by: diegoM |
last post by:
Hello,
I try to use the srf API and I have a link error. When I did :
FuncCallContext *funcctx;
...
if (SRF_IS_FIRSTCALL()){
funcctx = SRF_FIRSTCALL_INIT(); // If I remove this line, no link error
...
}
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |