Re: Showing matrix with single query

On Fri, Oct 21, 2016 at 2:45 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thu, Oct 20, 2016 at 3:35 PM, Арсений Нестюк <arseniy(dot)nestyuk(at)gmail(dot)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(dot)g(dot)johnston(at)gmail(dot)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.