Function - Insert

[joserivera1990]

HI everyone,

It is not an issue, it is a question about metabase.

I'm new in metabase and I know that this is for reporting and statistics topic.

But I'm trying to use metabase to do an insert.

From metabase can I execute an insert to one table ? I did the test and this was not possible.

I tried creating a function(inside to do a insert) and from metabase to do the call a la function with a select but it did not work.

For example:
function:

CREATE OR REPLACE FUNCTION sbs.crear_reporte(fecha_corte DATE, correo TEXT, reporte TEXT)
RETURNS TEXT
AS $$

DECLARE codigo TEXT;
BEGIN

SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
FROM generate_series(1,5)), '') || '-' || fecha_corte as codigo into codigo;

INSERT INTO SBS_MANAGEMENT.REPORTES (codigo, reporte, fecha_corte, correo, fecha_alta)
VALUES (codigo, reporte, fecha_corte, correo, current_timestamp);

RETURN codigo;
END;
$$
LANGUAGE 'plpgsql';

Call from metabase:

SELECT sbs.crear_reporte('2012-12-15', 'jarivera@email', 'reporte_novedades');

I did the testing from posgresql and work fine but from metabase did not work.

Is this possible ?
How can I do a insert from metabase?

I'm using the verison v0.27.1

Thanks

Jose Rivera

[mazameli]

No, that's not currently possible. Right now, Metabase is read-only by design with regard to your data.

[joserivera1990]

Thanks for your prompt response. I think that it would be a great feature.

[salsakran]

related to #2882

[jornh]

See related discussion and proposals in #3737 (is this a duplicate?) - also related #5287

I don’t think it’s a good idea to open up for full raw SQL INSERTs (even though it’s maybe not as bad as DELETEs and UPDATEs) in any underlying database. It opens up a whole can worms 🐛 of who should be allowed to do that? Who/how can make sure that data is inserted in a consistent way according to business logic defined in another application owning the database?

[joserivera1990]

Hi @jornh
In my specific case I have a requirement where the user can request reports on demand and these reports can spent in time 15 minutes. My idea was to allow to the user from a Question(metabase) choose a date for the report, write an email
and this would return a random code(In the question, I would call an function SQL and save a register in data base).

After, a process in batch does the logica and persist in data base the report and send a email. After the user log in in metabase and he can get the report with the code.

This was my idea beacuse we do not have a application web where the user can interact, I saw metabase useful for this but currently was not possible with this restriction.

I think should be allowed that from functions SQL can do Inserts and give permissions for execute the insert just for the admin.

[lindsay-stevens]

@joserivera1990 I wouldn't do this but I admire your lateral thinking. To give a hint of why it would work in postgres but not metabase, the metabase query transactions have a rollback at the end so that your query / function might return data but otherwise nothing is committed. It's at the bottom of about 10 layers of function calls but the root cause is here. Another relevant constraint (I think coming from the JDBC driver though) is that you can only do one statement per query - this has come up for people wanting to, for example, to set search path 'x'; select y.

Something you could try, if you really want this approach to work, is to break out of the transaction with the dblink extension (or any PL with the ability to create an autonomous connection), and do the insert using a dblink connection. A rollback on the query transaction then won't rollback the dblink commit(s).

Another issue with this approach would be controlling when this SELECT sbs.crear_reporte(); might get called - for example any time a question using it gets refreshed, or if it is on a dashboard then every time the dashboard gets updated.

[joserivera1990]

Hi @lindsay-stevens, Thanks for your explanation.

We have talked in the team arquitecture and we have decidied to create an application web small to request an report.

We saw that it is not good use metabase to insert because this is for reading and show data.

Thanks

[tashiritzy]

I think metabase should be just for viewing data. It allows non sql people to generate reports on their own. If someone wants to insert/update/delete, he/she surely will be someone who knows sql well and there are lot of other tools that does these for a technical person.
Metabase is an analytics/reporting tool that can be used by a non technical person talking directly to the database and reporting does not involve insert/update/delete.

[camsaul]

Closing, it seems like question has been answered adequately.