Re: column definition for setof record functions (like dblink and crosstabs)

2012/6/6 Willy-Bas Loos <willybas(at)gmail(dot)com>:
> Hi,
>
> Functions that return SETOF RECORD need to be called with a column
> definition. That is a bit limiting.
> For example, you might not know how many columns your crosstab will have in
> advance, you might not know the structure of a table that you access with
> dblink in advance.
> Also, it's always a more complicated query to write, which is not very user
> friendly.
>
> I searched a bit for the reason, and i found: "This allows the system to
> understand what * should expand to, and what proname in the WHERE clause
> refers to, in advance of trying to execute the function." in the dblink
> page.
> From this sentence i can derive that there are some internal mechanisms that
> need to have the the column definition before the data.
>
> I was just wondering if there were a way to generate this information
> automatically, so that the user need not supply it.
> It would have a cost of course, but in some cases, it might certainly be
> worth it.
>

there are no way, how to do it.

generating cross tables should be solved via cursors
http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html

I believe so dblink will be deprecated in near future, because has
mentioned limits - you can use
http://wiki.postgresql.org/wiki/Foreign_data_wrappers

> Cheers,
>
> WBL
> --
> "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
>