pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning a Cross Tab record set from a function
3+ messages / 2 participants
[nested] [flat]
* Returning a Cross Tab record set from a function
@ 2005-06-01 14:14 Marc Wrubleski <mlwruble@math.ucalgary.ca>
0 siblings, 0 replies; 3+ messages in thread
From: Marc Wrubleski @ 2005-06-01 14:14 UTC (permalink / raw)
To: pgsql-sql
I have read the great work that all the list members have done working
with cross tabs (pivot tables) in postgresql. The issue I have not seen
a solution for, but would really like to see, is the ability to return
the results of a dynamic (variable # of columns) cross tab function as a
recordset.
The excellent code contributed by Christoph Haller in the "Generating a
cross tab II (pivot table)" thread was very useful, but it dumps the
results into a view. I need to query like "select * from
create_pivot_report('sales_report2','vendor','product','sales','sum','sales');"
and have the result back as a recordset.
The reason I want to do this is that I have a hierarchical structure of
itemtypes where each itemtype contains an arbitrary number of items, AND
each itemtype has an arbitrary number of attributes. I want to perform
the crosstab on the items with attributes for a given itemtype. The
static code works perfectly fine for a query of an itemtype, BUT the
itemtypes and attributes may change often enough that creating views for
each itemtype will be insufficient.
It seems I can do this from any higher level language, but it drives me
crazy that I can't perform this operation as a function inside of
Postgres...
Thanks for any thoughts you might have...
--
Marc Wrubleski <mlwruble_at_math.ucalgary.ca>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Returning a Cross Tab record set from a function
@ 2005-06-06 10:20 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-16 17:16 ` Re: [SQL] Returning a Cross Tab record set from a function Marc Wrubleski <mlwruble@math.ucalgary.ca>
0 siblings, 1 reply; 3+ messages in thread
From: KÖPFERL Robert @ 2005-06-06 10:20 UTC (permalink / raw)
To: 'Marc Wrubleski' <mlwruble@math.ucalgary.ca>; pgsql-sql
|-----Original Message-----
|From: Marc Wrubleski [mailto:mlwruble@math.ucalgary.ca]
|Sent: Mittwoch, 01. Juni 2005 16:15
|To: pgsql-sql@postgresql.org
|Subject: [SQL] Returning a Cross Tab record set from a function
|
[...]
|
|It seems I can do this from any higher level language, but it drives me
|crazy that I can't perform this operation as a function inside of
|Postgres...
Yes, semms very like this can't be done in just sql but needs a higher level
lng.
Actually Postgres provides such higher languages. Try including PL/Python
PL/perl or PL/tk. Fom there you are able to query all the metadata of the
wanted tables so that a adequate SQL-string can be generated.
In case of sourcecode, you find theses languages in the contrib dir
|
|Thanks for any thoughts you might have...
|
|--
|Marc Wrubleski <mlwruble_at_math.ucalgary.ca>
|
|
|---------------------------(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
|
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Returning a Cross Tab record set from a function
2005-06-06 10:20 Re: Returning a Cross Tab record set from a function KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-06-16 17:16 ` Marc Wrubleski <mlwruble@math.ucalgary.ca>
0 siblings, 0 replies; 3+ messages in thread
From: Marc Wrubleski @ 2005-06-16 17:16 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql; pgsql-committers@postgresql.org
Hi All, thanks for your responses.
I know higher level languages can perform the operation, but I think a
function written in a higher level language could still not return a
resulting (structure undefined) table back as a result set. I hope I am
wrong about this ;-) If not, read on...
My function caller cannot query like SELECT * FROM crosstab ('SELECT
x,y,z FROM foo ...') AS ct(a int, b text, c text) because he does not
know that x,y,or z are available to him, and there may also q,r, and s
too. That's part of what he is hoping to get from the query! (as well as
the data for these columns)
Instead I need to query like SELECT * FROM crosstab_undef ('SELECT *
FROM foo' ...)
I am no programmer, so I need someone to tell me if it is possible to
add this feature to Postgres, or does it go against every (type) rule
written? The difference is that if it IS possible, I may be able to get
someone to write some code to contribute. I would love to hear from a
developer on this one.
I have a system that has two interfaces to the database (More if you
include reporting tools) and I would like similar functionality for all
interfaces. This is why I would like to have a function defined at the
database level.
Many Thanks,
Marc Wrubleski
On Mon, 2005-06-06 at 12:20 +0200, KÖPFERL Robert wrote:
>
> |-----Original Message-----
> |From: Marc Wrubleski [mailto:mlwruble@math.ucalgary.ca]
> |Sent: Mittwoch, 01. Juni 2005 16:15
> |To: pgsql-sql@postgresql.org
> |Subject: [SQL] Returning a Cross Tab record set from a function
> |
> [...]
> |
> |It seems I can do this from any higher level language, but it drives me
> |crazy that I can't perform this operation as a function inside of
> |Postgres...
>
> Yes, semms very like this can't be done in just sql but needs a higher level
> lng.
> Actually Postgres provides such higher languages. Try including PL/Python
> PL/perl or PL/tk. Fom there you are able to query all the metadata of the
> wanted tables so that a adequate SQL-string can be generated.
>
> In case of sourcecode, you find theses languages in the contrib dir
> |
> |Thanks for any thoughts you might have...
> |
> |--
> |Marc Wrubleski <mlwruble_at_math.ucalgary.ca>
> |
> |
> |---------------------------(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
> |
--
Marc Wrubleski <mlwruble@math.ucalgary.ca>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-06-16 17:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-01 14:14 Returning a Cross Tab record set from a function Marc Wrubleski <mlwruble@math.ucalgary.ca>
2005-06-06 10:20 Re: Returning a Cross Tab record set from a function KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-16 17:16 ` Marc Wrubleski <mlwruble@math.ucalgary.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox