Run function then select table created

Hello guys!
How are you all ?

Well, I’ve been struggling with this a few days and I can’t think in any way to solve it.
I have this function that generates a TEMP TABLE and I wish to query this table after it has been generated.
The key aspect is that my TEMP TABLE doesn’t has a fixed number of columns, it can vary.

I’m using Postgres 9.5+. Any tips ? =/

Thanks in advance.

– Update

I forgot to mention that I could do that on Postgres, but I couldn’t do it on Metabase:

select function_that_generates_table(parameter_);
select * from temp_table;

Are the data types of each column always the same? Are you using all the columns in Metabase? Are the column names always the same?

If so, you could use a custom query. If not, it all looks like a really bad idea and you should write a different function.

Data types is always the same, but the number of columns can vary. I’m using all columns from table in Metabase. Column names always will vary.

it all looks like a really bad idea and you should write a different function.

As I thought.

Can you see any way out to customize columns name ? Or to run a dynamic SQL ?

Thank you for your reply! =)

How would Metabase know that there were more columns this time than last? Can you give a couple of examples of what you’re doing?

How would Metabase know that there were more columns this time than last?

This is the question. My main idea was to simply “select * from temp_table”.

Imagine a structure of questions and answers. Question1, 2, 3 and so on. My goal is to divide all rows into columns per question. So the quantity of questions can vary. Sometimes it would be 1, 2, 3, […] questions.

Now it doesn’t matter the name of the columns, I’m trying only to find a way to dynamically use only one “Question”/Report to exhibit this.

Any ideas to solve this ?

Thanks for your help.

From a database perspective, that’s a horrible idea!
You need to normalize the structure.
You’re thinking of:
image

You need to change that to the normalized:
image

Adding extra columns for things like who was answering the questions.

Actually the structure is like this second image.

The case is that Question 1 and 2 is part of another Parent structure. So I need to correlate this two questions in one row. Something like this:
22

Answer 1, for example, is the answer for each one of the questions (different answers, but same parent) and each row corresponds to one parent structure. Hope that I could express myself clearly.

So the quantity of questions could vary, then I couldn’t structure a fixed number of columns in the table.

I discarded the possibility to bring the question label as a column. I’m bringing it as the first row, but the only ways to do this is to work as crosstab in Postgres, crosstab2, crosstab3 and so on. Now I’m struggling in how to do this in only one “Metabase-Question”.

Thank you again!! I really appreciate your help! :slight_smile:

Then your result needs to look more like this:
image
It doesn’t matter how the data is stored. The important bit is that the resultset is easy to work with.

My original table is structured as you have shown. One question/answer per row.
But when I will summarize everything I need to know how the user answered that one parent structure.

Imagine that I bought a T-Shirt and I answered that I want a M, Black T-Shirt. So I need to correlate this two information in one row. Then would be, in this case, two question. Which size ?, Which Color ?.

I can do this using crosstab in Postgres, but the problem is that crosstab expect me to inform a return type and that is where I’m struggling, because the number of columns will vary. Theoretically I would need to do a report/nativequery per number of columns.

Thanks again for your help and patience! :slight_smile:

Can you sketch how you want the answer to appear?

Beak them in columns…

22

I meant in Metabase.