X-Original-To: pgsql-committers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CB25A5283A; Thu, 16 Jun 2005 14:16:48 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 98934-09; Thu, 16 Jun 2005 17:16:38 +0000 (GMT) Received: from ms0.math.ucalgary.ca (ms0.math.ucalgary.ca [136.159.61.8]) by svr1.postgresql.org (Postfix) with ESMTP id 420CB52818; Thu, 16 Jun 2005 14:16:36 -0300 (ADT) Received: from linuxtest.math.ucalgary.ca (linuxtest.math.ucalgary.ca [136.159.61.64]) by ms0.math.ucalgary.ca (8.12.10+Sun/8.12.10) with ESMTP id j5GHGQXb027060; Thu, 16 Jun 2005 11:16:27 -0600 (MDT) Subject: Re: [SQL] Returning a Cross Tab record set from a function From: Marc Wrubleski To: =?ISO-8859-1?Q?K=D6PFERL?= Robert Cc: pgsql-sql@postgresql.org, pgsql-committers@postgresql.org In-Reply-To: References: Content-Type: multipart/alternative; boundary="=-xwuCq1U61vGC53sNROy1" Date: Thu, 16 Jun 2005 11:16:26 -0600 Message-Id: <1118942186.30032.44.camel@linuxtest.math.ucalgary.ca> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-4) X-Math-Stat-MailScanner-Information: Please contact the ISP at 220 3944 for more information X-Math-Stat-MailScanner: Found to be clean X-Math-Stat-MailScanner-From: mlwruble@math.ucalgary.ca X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.029 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200506/243 X-Sequence-Number: 18051 --=-xwuCq1U61vGC53sNROy1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit 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 > | > | > |---------------------------(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 --=-xwuCq1U61vGC53sNROy1 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit 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>
--=-xwuCq1U61vGC53sNROy1--