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..