Calc integration with PostgreSQL

[fpiazza]

Hello.
I’m trying to integrate Calc to PostgreSQL, in a way that my worksheet would run queries in the database and have the data updated inside the worksheet, to make calculations and graphics.

I would like to be able to refresh the queries when I want like I do in Excel with ODBC.

Ideally, the query would use the contents of a cell in the worksheet, to perform the queries, filtering data accordingly.

Where can I find a good tutorial on this? I managed to register a database with Base, but the data I import to the table is “static”, that is, it doesn’t change as the database changes.

I’m using LibreOffice 24.2.4.2 in Linux Fedora 40. PostgreSQL server runs in the same machine.

Thank you.

[RobertG]

For changing data:
Click on the query or table of your registered database (left mouseclick, hold)
Move to the sheet of base.
Paste here.
If you have changed content in the database:
Data → Refresh Range
will refresh the data in Calc.

[fpiazza]

It worked. Thank you.

Is there a way to use the content of a cell from Calc in the SQL query?
The cell would contain a string that is used as part of the query, with a company code, for example:

SELECT “number”, “code” FROM “public”.“companies” WHERE “code” = ‘PETR’

I would like to replace the ‘PETR’ string with the contents of a cell from Calc, so when I change it there, it would change the query.

[Villeroy]

[Tutorial] Using registered datasources in Calc
Workaround: Database reports with charts in LibreOffice Calc
Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet
https://forum.openoffice.org/en/forum/viewtopic.php?p=96427#p96427 (interaction with databases through forms on sheets)