basavaraj

basavaraj

Postgres function call with pq.Array of user defined struct in GO

Reason I am writing this blog is that I need to help fellow developers get this solution quickly and save a lot of time since there is no much direct answers found in google search.

So, here is the use case- I wanted to insert bulk students data in Postgres database using postgres function.

So we need input parameter to accept array of students.

Lets define struct Student.

type Student struct {
   name      string      `db:"name"`
   id        int32       `db:"id"`
}

Lets define add_student function in psql.

CREATE OR REPLACE FUNCTION bulk_add_students(
    _student_list student[]) RETURNS BIGINT

    LANGUAGE plpgsql
AS
$$
DECLARE
    -- variable go here
BEGIN
    -- body goes here
    RETURN count;
END
$$;

student_list is of student[]. Assume, student is table present in DB

So, consider below Go source code to call the postgres function using pq library.

insertedRecordCount := 0

err := s.db.QueryRow(
       `SELECT bulk_add_students($1::student[])`,pq.Array(students)
).Scan(&insertedRecordCount)

This code fails with pq error -

Error insertingpq: malformed array literal: "ARRAY[ROW('Ram',3)]::student[]"

Here is the solution to fix

Implement the Value() Interface on student type

/**
While calling function `bulk_add_student`, we pass ARRAY [Student]
*/
func (s Student) Value() (driver.Value, error) {
    val := fmt.Sprintf("(%s,%d)", s.name, s.id)
    return val, nil
}

We need to implement Value function to parse the Array in postgres format

Note that we donot need to implement Value function fo Go primitives types (e.g. string, int)

Refer for more information - stackoverflow.com/questions/47621459/insert..

 
Share this