Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Returning a Cross Tab record set from a function

From: Marc Wrubleski <mlwruble(at)math(dot)ucalgary(dot)ca>
To: KÖPFERL Robert <robert(dot)koepferl(at)sonorys(dot)at>
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-committers(at)postgresql(dot)org
Subject: Re: [SQL] Returning a Cross Tab record set from a function
Date: 2005-06-16 17:16:26
Message-ID: 1118942186.30032.44.camel@linuxtest.math.ucalgary.ca (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-sql
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(at)math(dot)ucalgary(dot)ca]
> |Sent: Mittwoch, 01. Juni 2005 16:15
> |To: pgsql-sql(at)postgresql(dot)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(at)math(dot)ucalgary(dot)ca>

In response to

pgsql-committers by date

Next:From: Bruce MomjianDate: 2005-06-16 17:53:55
Subject: pgsql: Add fsync() define for Win32 to cover cases other than
Previous:From: Bruce MomjianDate: 2005-06-16 01:43:49
Subject: pgsql: Add \x hex support to ecpg strings.

pgsql-sql by date

Next:From: Greg Sabino MullaneDate: 2005-06-16 19:01:19
Subject: Re: SELECT with sum on groups ORDERING by the subtotals
Previous:From: Din AdrianDate: 2005-06-16 17:11:17
Subject: Re: [SQL] PostgreSQL and Delphi 6

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group