[Base] Access PostgreSQL ARRAY types in forms

users
#1

Hi all,

I am new to the list, so please excuse me if this is the wrong place or a dumb question...

I have a PostgreSQL data base and want to use LibreOffice Base as nice frontend for adding and modifying data. The Postgres tables contain data arrays, like

CREATE TABLE contact (
  [...]
  email text[]
)

My problem: how can I create a form to fill in such arrays? A simple (multiline) text field will try to insert its content as text, of course leading to a SQL error.

A simple solution would be using the Postgres function string_to_array() or regexp_split_to_array() (or even a more sophisticated Stored Procedure) in the INSERT statement, splitting the multiline text into array items. However, I didn't find a way to tweak the SQL statement like this. If this even possible?

Or is there an other "standard" way for dealing with array data types?

Any help will be highly appreciated!

Thanks in advance,
Albrecht.

#2

Hi Albrecht,

I have a PostgreSQL data base and want to use LibreOffice Base as nice frontend for adding and modifying data. The Postgres tables contain data arrays, like

CREATE TABLE contact (
  [...]
  email text[]
)

How are you connecting to your pg database ?
Via a JDBC driver or using the native postgres driver provided within
LibreOffice ?

Depending on which one you are using, support for a given data type, in
this case, an array, might be different (I don't know off-hand, I
haven't tested).

My problem: how can I create a form to fill in such arrays? A simple (multiline) text field will try to insert its content as text, of course leading to a SQL error.

A simple solution would be using the Postgres function string_to_array() or regexp_split_to_array() (or even a more sophisticated Stored Procedure) in the INSERT statement, splitting the multiline text into array items. However, I didn't find a way to tweak the SQL statement like this. If this even possible?

Using a macro that takes your form control text and inserts it into a
preparedStatement object, it would/should probably be possible. I doubt
that the Form GUI supports this kind of thing directly though (but I'm
far from being an expert with postgres data type support in LibreOffice).

As you mention, it might be more efficient to do this via a stored
procedure in the backend rather than fudging something in the frontend.

Or is there an other "standard" way for dealing with array data types?

Not without using macros, that I know of, but I would love to be proven
wrong.

Alex

#3

Hi Alex:

Thanks a lot for your fast response and for your input!

How are you connecting to your pg database ?
Via a JDBC driver or using the native postgres driver provided within LibreOffice ?

Depending on which one you are using, support for a given data type, in this case, an array, might be different (I don't know off-hand, I haven't tested).

I use the native connector, on a Debian 9 aka stretch Linux boxe; sorry for omitting this information in my first post…

It can actually deal properly with array data afaict; in the table view, it is displayed in the “usual” Postgres style (i.e. in braces).

Using a macro that takes your form control text and inserts it into a preparedStatement object, it would/should probably be possible. I doubt that the Form GUI supports this kind of thing directly though (but I'm far from being an expert with postgres data type support in LibreOffice).

As you mention, it might be more efficient to do this via a stored procedure in the backend rather than fudging something in the frontend.

Hmmm, thinking again about it, if I have to write a stored procedure anyway, it is probably easier to change the data base model and replace the array of single-line texts by a single multiline text. All the validation work would be performed in a post-insert or -update trigger function in Postgres. And it saves me the time to learn coding macros in Libreoffice (lazy me). For the data consumer (not Libreoffice), it doesn't make a big difference; to keep the API as it is now, I just write an other stored procedure splitting the multilines into arrays.

> Or is there an other "standard" way for dealing with array data types?
>

Not without using macros, that I know of, but I would love to be proven wrong.

The ability to deal with arrays would be a cool extension for Libreoffice (IIRC, arrays are part of the SQL standard).

Thanks again,
cheers,
Albrecht.