Looping through an array in an stored function in PostGres

Good Afternoon,

This is based off of my problem at link:
Looping through an array in an embedded procedure in firebird - English - Ask LibreOffice

I have set up postgres local version, 9 with pgAdmin 3. I am working with the following code in java:

import java.net.Socket;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;

 * @author minhaszt
public class PGFunctions extends DaoFunctions {
    public static void main(String[] par)
        PGFunctions pgf = new PGFunctions();
        Connection con = null;
        if (pgf.isAvailable())
            con = pgf.makeConnection();
            String sql = "drop function if exists temp.f_iu7YttW(character varying[]);\n"+
            "delete from temp.table1;\n"+                    
            "create or replace function temp.f_iu7YttW(dname varchar[]) returns void as $$\n" +
"declare\n" +
"counter integer = 1;\n" +
"begin\n" +
"	while counter<1000000 loop\n" +
"		insert into temp.table1(name) values(dname[counter]);\n" +
"		counter=counter+1;\n" +
"	end loop;\n" +
"end; $$ language plpgsql;\n" +
"select temp.f_iu7YttW(?);\n" +
"drop function temp.f_iu7YttW(character varying[]);";
            PreparedStatement stmt = con.prepareStatement(sql);
            ArrayList<String> arvars = new ArrayList<String>();            
            for (int x=0;x<1000000;x++)
            String[] vars ={};
            vars = arvars.toArray(vars);
            Array sqlArray = con.createArrayOf("text", vars);            
            stmt.setArray(1, sqlArray);
            catch(Exception ex)
    public Connection makeConnection()
        String url = "jdbc:postgresql://localhost:5432/postgres";
        Object[] conParams = {url,"root","root"};
        return this.getConnection(conParams);
    public boolean isAvailable() {
        boolean ret = false;
        Socket s =null;
        try {
            s = new Socket("localhost", 5432);
            ret = true;
        finally {
            return ret;

I would like to make an equivalent in Basic. Here is what I have so far:

Sub Main
Dim stmt As Object
Dim rs As Object
Dim sql As String
Dim a As Variant
Dim x As Long
 	executeDDLPg("delete from temp.table1")
 	executeDDLPg("drop function if exists temp.f_iu7YttW(character varying[])")
  	myDict= getScriptForgeDictionary()
 	a= Array(x & "")
	For x = 1 To 50
 		a=SF_Array.Append(a,x & "")
 	Next x
 	max = ubound(a)
	sql ="create or replace function temp.f_iu7YttW(dname varchar[]) returns void as $$ " & _
	"declare "  & _
	"counter integer = 1; "  & _
	"begin "  & _
	"	while counter<" & max & " loop "  & _
	"		insert into temp.table1(name) values(dname[counter]); "  & _
	"		counter=counter+1; "  & _
	"	end loop; "  & _
	"end; $$ language plpgsql;" ' & _
	sql="select temp.f_iu7YttW(?);\n" ' & _
	a = pgCon.createArrayOf("text", a)
	stmt = pgCon.prepareStatement(sql)
	executeDDLPg("drop function temp.f_iu7YttW(character varying[])")
End Sub

Private sub makePGConnection()
Dim sURL$
Dim myDict As Variant
	sURL$ ="sdbc:postgresql:dbname=postgres host=localhost port=5432"
	myDict= getScriptForgeDictionary()
	With myDict
		.Add("user", "root")
		.Add("password", "root")
		.Add("JavaDriverClass", "org.postgresql.Driver")
		oParms = .ConvertToPropertyValues()
	End With
	pgCon=getConnectionWithUserInfo(sURL, oParms)	
End sub

And my Basic code makes LO crash and die at the line:


Any help is greatly appreciated.

I would try to get properties for stmt by xRay.

So I used xRay. Here is the screenshot:

I am certain that i need to insert an array of strings, but this array needs to be converted to an object. Will continue investigation.

I stay away from ScriptForge. It’s like running a marathon and shooting yourself in the foot!
Did a bit of testing with PostgreSQL array fields a few years ago (mentioned here → [Base]: Form Based Filter for Array Data Element - #2 by Ratslinger)
Just tested with this code:

oConnection = thisComponent.Drawpage.Forms(0).activeConnection()
sSQL ="Insert Into sal_emp (name, pay_by_quarter) Values ('Joey3', ARRAY [1850,99])"
oSqlStatement = oConnection.prepareStatement(sSql)
findRecords = oSqlStatement.execute()

Not going any further as your code does not work as is (problems with ScriptForge I believe)
Did multiple minor tests:
Screenshot at 2023-03-28 19-54-29

1 Like

I am not using Firebird for this project. I have shifted to Postgres.

Regarding scriptforge, I am using that to generate the variant array. I am using this variant array as test input for the stored function.

I have isolated the problem for the line


the setArray method requires an input which uses the interface com.sun.star.sdbc.XArray.

Does anyone have any idea what to do next? How would i instantiate the proper datatype that has this interface?

There seems to be some usefull information in the following links:

Accessing SQL array element from Libreoffice Basic - Stack Overflow

I have asked a similar question here as well:

postgresql - Accessing SQL array element from Libreoffice Basic with Postgres DB - Stack Overflow

So if this gives anyone any ideas, please let me know. This will help alot. Thank you.

I was working on another post using Firebird and did not correct this one . It is about PostgreSQL. The link I provided would also show it is PostgreSQL as well as you discovering no array in Firebird in previous question.
My SQL above & in the link is similar to the links you provided.
I still avoid ScriptForge - it is just a Kludge.
Just revisited the array function for looping through the arrays. Works fine being able to get lengths, lower/upper bounds, multi dimentional, etc. Problem may be your PostgreSQL version. I am using v14.7. Here is docs I am referring to → PostgreSQL: Documentation: 15: 8.15. Arrays


Don’t know why

should support updates, but have tried a little bit with PostgreSQL.
Get data into the table by setString coded like {Bob,Mia,Jack,Mary}.

I would test content of the array by msgbox.

I agree, but i am going to have an array list of thousands of elements at a time.

Could you get the values of the array by msgbox a[1] …?
Have tried it with an array and get the same crash.
So I have tested with an array, transformed to a string:

	stAr = "{"
	FOR i = LBound(ar()) TO UBound(ar())
		stAr = stAr & ar(i) & ","
	stAr = Left(stAr,Len(stAr)-1) & "}"

ar will be a in your example
And setString will work.

yes. my array is string type. looping through the array with msgbox displays the values. Please note that i was originally writing this code as a test in java:

String[] vars ={};
Array sqlArray = con.createArrayOf("text", vars);       

As you can see, the connection object (con) has a function which can take a java array convert it to a java.sql.Array, which can be used as an input to a stored function. Does LO have any kind of feature to do the same?

I keep seeing com.sun.star.sdbc.XArray, but I don;t see a way to implement or initialize this.

I might try that. I posted more or less the same question with a bit more detail here:

Couldn’t find any other information how setArray should work. Have written a bug for this, because it leads to a crash: Bug 154464

1 Like

Thank you.