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:

>
> |-----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>