On Fri, Oct 21, 2016 at 2:45 AM, David G. Johnston < david.g.johns...@gmail.com> wrote:
> On Thu, Oct 20, 2016 at 3:35 PM, Арсений Нестюк <arseniy.nest...@gmail.com > > wrote: > >> >> I haven't thought about casting null before, it's interesting. It'll make >> the implementation a little easier, but won't answer my question >> completely. I still need to create a type in one query and use it in >> another, don't I? >> It just seems strange to me that I can't define a >> function/trigger/view/anything to show a matrix in one easy request. >> > >> > On Fri, Oct 21, 2016 at 12:37 AM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> >>> >>> A self-contained example probably would have been more effective >>> here...I can barely follow along but understand the two core issues: >>> >>> 1. A query must define all of its columns at execution time. >>> 2. An anyelement output function must have at least one anyelement input. >>> >>> Focusing on that alone one convention is to define a type and then pass >>> in a "null" of that type as the anyelement input. You then expect to get a >>> non-null value of that type upon function completion. >>> >>> <not tested> >>> CREATE TYPE pair (x int, y int); >>> CREATE FUNCTION random_item(anyelement) RETURNS anyelement; >>> SELECT random_item(null::pair); -- returns pair (2, 6) >>> >>> This is one more tool for the belt. I don't understand the details of >>> your requirements to suggest the best way to combine pseudo type functions >>> and dynamic SQL. >>> >>> At some point you have to detect the column count from an initial query >>> and dynamically incorporate it into a second query. Anything beyond that >>> is because you are trying to make things better (faster, more >>> understandable). >>> >>> David J. >>> >> >> > Please keep replies on-list and do not top-post. > > As I ended with - the columns of a query must be known at compile time. > Since your column count is determined by a query you cannot solve your > problem with a single query. > > This is fundamental limitation of SQL. You could make something that > LOOKS like a matrix but only contains a single column (either textual or > array) depending on your ultimate presentation goal. Look at the > "format(...)" function if you want to play with this approach. > > David J. > I'm sorry for this, I wasn't able to find any guide on using mailing lists, so I just pressed "reply" in sincere hope that it would work. Thank you for the reply. Something that looks like a matrix is completely enough for me. Regards, Arseniy Nestyuk.
