How can I dynamically generate a report name and/or column name in PostgreSQL?

Get Help
1

Hello,

How can I write a dynamic report name, such as "My report from October 2023"? Where "October 2023" is dynamic.

Also, how can I set "October 2023" dynamically? Is there a way to set a column name dynamically from a PostgreSQL database?

For example, consider the following table:

CREATE TABLE test (
  id INT,
  displaylabel VARCHAR(255)
);

INSERT INTO test (id, displaylabel) VALUES (1, 'Hello');
INSERT INTO INTO test (id, displaylabel) VALUES (2, 'World');

To get the following output:

October 2023
2
2

chatgpt is great at helping with things like this.

aliasing your column names is easy with a select query.

SELECT displaylabel AS to_char(current_date, 'Month_YYYY') FROM your_table;

metabase can take that query and convert it into a Model. A Model is treated like a raw table.

3

I received the following syntax error message when I tried the query you shared:

Query Error: error: syntax error at or near "("

Here is my test : DB Fiddle - SQL Database Playground

4

Sorry my previous response was total baloney. I didn't think that through.

You are looking for "EXECUTE FORMAT" from PL/SQL Link to the official docs. It is the procedural language that postgres uses. While powerful and performant in that it keeps all your code in the database, it does have some big gotchas. It isn't the most intuitive or modern language to work with, and debugging it is hellish. But I do use it a lot as it keeps business logic in the database so I can be flexible about the tooling I use (like metabase for analytics et etc). If it isn't critical to keep business login in the database, then using another language for dynamic SQL generation may make your life easier.

You really do need to read the fine print on using %I or %L or %s or %1 as value replacements otherwise you open yourself up to a world of pain with SQL injection. It is why postgres doesn't just easily allow dynamic sql.

Here is a simple version to do what you need:

DO $$
DECLARE
alias_name text;
BEGIN
SELECT to_char(current_date, 'Month_YYYY') INTO alias_name;
EXECUTE FORMAT('SELECT foo AS %I FROM your_table', alias_name);
END $$;

But you'll need to enclose it inside a function and then call that. I'm intentionally not giving you a cut and paste solution because you really need to read and understand what you are doing. The postgres docs are great. And once you really get it, the execute format is powerful.

1 Like
5

And now that I think about this again. It sounds like you may want to be doing some sort of pivot table. Usually when you see dates as column names with some numeric value as the cell, it is in the form of a pivot table. Although postgres can do that internally it isn't very easy to work with. You'd be better off to just let Metabase create them for you.
Try this: