Thread: Showing matrix with single query
Hello everyone.
I'm trying to solve the following problem:Currently, I'm generating the required table with the crosstab function:
cols = 'row_n int, ' || (SELECT string_agg(col_name, ', ') FROM (SELECT 'c' || generate_series(0, col_max) || ' int' AS col_name) AS Subq);
query = 'CREATE TEMPORARY TABLE Temp_show AS SELECT * FROM CROSSTAB(''SELECT row_n, col, val FROM ' || matrix_name || ' ORDER BY 1'', ''SELECT generate_series(0, ' || col_max || ')'') AS (' || cols || ');';
cols = 'row_n int, ' || (SELECT string_agg(col_name, ', ') FROM (SELECT 'c' || generate_series(0, col_max) || ' int' AS col_name) AS Subq);
query = 'CREATE TEMPORARY TABLE Temp_show AS SELECT * FROM CROSSTAB(''SELECT row_n, col, val FROM ' || matrix_name || ' ORDER BY 1'', ''SELECT generate_series(0, ' || col_max || ')'') AS (' || cols || ');';
I have two solutions, but they need two queries. The first looks like this:
SELECT show_matrix('matrix');
SELECT * FROM Temp_show;
where show_matrix essentially executes the query defined above and returns void.
The second is
SELECT prepare_matrix('matrix');
SELECT * FROM show_matrix('matrix');
Here show_matrix returns setof needed row type; prepare_matrix creates that type and the show_matrix with the right type in "returns ...".
(EXECUTE 'CREATE TYPE needed_type AS (' || cols ||');';
CREATE FUNCTION show_matrix(text) RETURNS SETOF needed_type AS $$ ...
RETURN QUERY SELECT * FROM Temp_show;)
Both solutions need two subsequent queries, because objects from the second query don't exist before the first is executed. The trouble is that the function's returning type should be defined as soon as function starts execution, but that type depends on the name of a matrix - on an argument.
Triggers, to my knowledge, aren't fired on plain selects, so they can't be used to compress two lines into one; rules on select, on the other hand, allow only one action. An anyelement on the output requires an anyelement of the same type on the input, which I can't get without redefining function or some element of that type to pass as an argument to a polymorphic function...
Triggers, to my knowledge, aren't fired on plain selects, so they can't be used to compress two lines into one; rules on select, on the other hand, allow only one action. An anyelement on the output requires an anyelement of the same type on the input, which I can't get without redefining function or some element of that type to pass as an argument to a polymorphic function...
So, is there any way to solve the problem in one query?
I've started learning PostgreSQL not long ago, so I'm probably missing something obvious. I'd be grateful for any help.
Regards,
Arseniy Nestyuk.
SELECT * FROM show_matrix('name_of_matrix');I'm trying to solve it with one query, as user-friendly as possible. Ideally, it should take the formThere's a table (row int, col int, val int) that represents some matrix. The number of columns in the matrix is arbitrary. I need to show the matrix in the usual form, with columns being the columns of the matrix. So, from the table {(0,0,1), (0,1,2), (1,0,3),(1,1,4)} I want to get the table (row int, col0 int, col1 int) {(0,1,2), (1,3,4)}.Hello everyone.I'm trying to solve the following problem:Currently, I'm generating the required table with the crosstab function:
cols = 'row_n int, ' || (SELECT string_agg(col_name, ', ') FROM (SELECT 'c' || generate_series(0, col_max) || ' int' AS col_name) AS Subq);
query = 'CREATE TEMPORARY TABLE Temp_show AS SELECT * FROM CROSSTAB(''SELECT row_n, col, val FROM ' || matrix_name || ' ORDER BY 1'', ''SELECT generate_series(0, ' || col_max || ')'') AS (' || cols || ');';I have two solutions, but they need two queries. The first looks like this:SELECT show_matrix('matrix');SELECT * FROM Temp_show;where show_matrix essentially executes the query defined above and returns void.The second isSELECT prepare_matrix('matrix');SELECT * FROM show_matrix('matrix');Here show_matrix returns setof needed row type; prepare_matrix creates that type and the show_matrix with the right type in "returns ...".(EXECUTE 'CREATE TYPE needed_type AS (' || cols ||');';CREATE FUNCTION show_matrix(text) RETURNS SETOF needed_type AS $$ ...RETURN QUERY SELECT * FROM Temp_show;)Both solutions need two subsequent queries, because objects from the second query don't exist before the first is executed. The trouble is that the function's returning type should be defined as soon as function starts execution, but that type depends on the name of a matrix - on an argument.
Triggers, to my knowledge, aren't fired on plain selects, so they can't be used to compress two lines into one; rules on select, on the other hand, allow only one action. An anyelement on the output requires an anyelement of the same type on the input, which I can't get without redefining function or some element of that type to pass as an argument to a polymorphic function...So, is there any way to solve the problem in one query?
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.
On Fri, Oct 21, 2016 at 2:45 AM, David G. Johnston <david.g.johnston@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.johnston@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.