This is the function I came up with, but I am not certain that there is not a better, more efficient way:
function generateInsertSql(conn::LibPQ.Connection, schemaName::String, tableName::String, resultSqlFullTableName::String)::String
try
executeResult = execute(conn, "SELECT * FROM $schemaName.$tableName")
if !isempty(executeResult)
frame = DataFrame(executeResult)
numOfRows = size(frame, 1)
iob = IOBuffer()
Base.write(iob, "\nINSERT INTO $resultSqlFullTableName VALUES \n")
for n in 1:numOfRows
Base.write(iob, "(")
bFirst::Bool = true
for x in propertynames(frame)
if bFirst
bFirst = false
else
Base.write(iob, ",")
end
if isa(frame[!, x][n], Missing)
Base.write(iob, "NULL")
elseif isa(frame[!, x][n], Int32) || isa(frame[!, x][n], Float64) || isa(frame[!, x][n], Bool)
Base.write(iob, string(frame[!, x][n]))
elseif isa(frame[!, x][n], String)
Base.write(iob, "'$(replace(frame[!, x][n], "'" => "''"))'")
elseif isa(frame[!, x][n], DateTime)
Base.write(iob, "'$(Dates.format(frame[!, x][n], dateformat"yyyy-mm-dd HH:MM:SS.sss"))'")
elseif isa(frame[!, x][n], Date)
Base.write(iob, "'$(Dates.format(frame[!, x][n], dateformat"yyyy-mm-dd"))'")
elseif isa(frame[!, x][n], Time)
Base.write(iob, "'$(Dates.format(frame[!, x][n], dateformat"HH:MM:SS.sss"))'")
elseif isa(frame[!, x][n], Dates.CompoundPeriod)
Base.write(iob, compoundPeriodToString(frame[!, x][n]))
else
println(typeof(frame[!, x][n]))
Base.write(iob, frame[!, x][n])
end
end
if n === numOfRows
Base.write(iob, ");\n")
else
Base.write(iob, "),\n")
end
end
return String(take!(iob))
end
catch exc
# do nothing
println(exc)
println("Didn't generate INSERT SQL for table $tableName.")
end
return ""
end
Could this be made better using somehow join or CSV.write, or something like that?