Thread: Re: [SQL] Returning a Cross Tab record set from a function
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:
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> |