There is a composite type in postgresql:

Create type CompLaLaLa AS ( Name character varying, Count numeric );

And the table:

 CREATE TABLE "LaLaLa" ( "ID" serial NOT NULL, "Сomposition" CompLaLaLa[] NOT NULL, CONSTRAINT LaLaLa_pk PRIMARY KEY ("ID") );

I connected the database to OpenOffice and when sampling:

 select "Сomposition" from "LaLaLa"

Displays empty cells. If to_json ("Composition"), then just the type of Object displays. How to read this type in OpenOffice?

The request itself in the macro:

 sub main Dim db as Object db = connect_to_database("postgre") SelectPrixod(db,30) disconnect_from_database(db) end sub Sub SelectPrixod(dbName as Object,ID as integer) Dim pSql as String Dim i as Integer Dim oRowSet as Object Dim oResult as String oSql = " SELECT ""Composition"" FROM ""LaLaLa"" Where ""ID""=ID; " oRowSet = createUnoService("com.sun.star.sdb.RowSet") oRowSet.activeConnection = dbName 'Активное подключение oRowSet.Command = oSql 'Запрос oRowSet.execute 'вызов запроса while oRowSet.Next 'бежим oResult = oResult & capitalize (oRowSet.getString(1)) & " " & chr(13) wend msgbox oResult End Sub Function capitalize (iName as String) as String Dim wordStart as String Dim wordEnd as String wordStart = UCase (Mid (iName, 1, 1)) wordEnd = LCase (Mid (iName, 2)) capitalize = wordStart & wordEnd End Function Function connect_to_database (dbName as String) as Object Dim dbContext As Object Dim oDataSource As Object dbContext = createUnoService("com.sun.star.sdb.DatabaseContext") oDataSource = dbContext.getByName(dbName) connect_to_database = oDataSource.GetConnection("admin", "Oma") 'подключение к ДБ End Function Sub disconnect_from_database (db as Object) db.close db.dispose() End Sub

Displays emptiness

  • Are there no openoffice experts? To the contest 2 days = ( - Oma
  • How to connect? For what? What kind of competition? - Sergey Gornostaev
  • Competition here for the question =) Connect via jdbc. For what? - What would be the table =) - Oma
  • I would have a more accurate answer = ( - Oma

1 answer 1

I can not say 100%, but I think that in OpenOffice macros you can’t work with arrays of PostgreSQL custom data types. If the task is to create a spreadsheet from a database selection using a macro, then the answer to your question is in no way without changing the database structure. The data can not be stored in an array of complex type objects, but in a separate table:

 CREATE TABLE LaLaLa ( id SERIAL NOT NULL, PRIMARY KEY (id) ); CREATE TABLE Сomposition ( id SERIAL NOT NULL, lalala_id INT NOT NULL, name VARCHAR, count NUMERIC, PRIMARY KEY (id, lalala_id), FOREIGN KEY (lalala_id) REFERENCES LaLaLa (id) );

And then choose the union:

 select c.* from LaLaLa as l inner join Сomposition as c on l.id = c.lalala_id;

If the use of a macro is not an end in itself, then you can write a program in some other language that will be executed independently, separately from OpenOffice, but will do the same - form a spreadsheet file. In this case, there will be no problems with working with user data arrays.