github.com/tuhaihe/gpbackup@v1.0.3/integration/predata_functions_queries_test.go (about) 1 package integration 2 3 import ( 4 "database/sql" 5 "fmt" 6 7 "github.com/tuhaihe/gp-common-go-libs/structmatcher" 8 "github.com/tuhaihe/gp-common-go-libs/testhelper" 9 "github.com/tuhaihe/gpbackup/backup" 10 "github.com/tuhaihe/gpbackup/options" 11 "github.com/tuhaihe/gpbackup/testutils" 12 13 . "github.com/onsi/ginkgo/v2" 14 . "github.com/onsi/gomega" 15 ) 16 17 var _ = Describe("backup integration tests", func() { 18 Describe("GetFunctions", func() { 19 var prokindValue string 20 var plannerSupportValue string 21 var proparallelValue string 22 BeforeEach(func() { 23 testutils.SkipIfBefore5(connectionPool) 24 if true { 25 prokindValue = "f" 26 plannerSupportValue = "-" 27 proparallelValue = "u" 28 } else { 29 prokindValue = "" 30 plannerSupportValue = "" 31 proparallelValue = "" 32 } 33 }) 34 It("returns a slice of functions", func() { 35 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.add(integer, integer) RETURNS integer 36 AS 'SELECT $1 + $2' 37 LANGUAGE SQL`) 38 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.add(integer, integer)") 39 testhelper.AssertQueryRuns(connectionPool, ` 40 CREATE FUNCTION public.append(integer, integer) RETURNS SETOF record 41 AS 'SELECT ($1, $2)' 42 LANGUAGE SQL 43 SECURITY DEFINER 44 STRICT 45 STABLE 46 COST 200 47 ROWS 200 48 SET search_path = pg_temp 49 MODIFIES SQL DATA 50 `) 51 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.append(integer, integer)") 52 testhelper.AssertQueryRuns(connectionPool, "COMMENT ON FUNCTION public.append(integer, integer) IS 'this is a function comment'") 53 54 results := backup.GetFunctions(connectionPool) 55 56 addFunction := backup.Function{ 57 Schema: "public", Name: "add", Kind: prokindValue, ReturnsSet: false, FunctionBody: "SELECT $1 + $2", 58 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 59 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 60 ResultType: sql.NullString{String: "integer", Valid: true}, 61 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, PlannerSupport: plannerSupportValue, Config: "", Cost: 100, NumRows: 0, 62 DataAccess: "c", Language: "sql", ExecLocation: "a", Parallel: proparallelValue} 63 appendFunction := backup.Function{ 64 Schema: "public", Name: "append", Kind: prokindValue, ReturnsSet: true, FunctionBody: "SELECT ($1, $2)", 65 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 66 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 67 ResultType: sql.NullString{String: "SETOF record", Valid: true}, 68 Volatility: "s", IsStrict: true, IsSecurityDefiner: true, PlannerSupport: plannerSupportValue, Config: `SET search_path TO 'pg_temp'`, Cost: 200, 69 NumRows: 200, DataAccess: "m", Language: "sql", ExecLocation: "a", Parallel: proparallelValue} 70 71 Expect(results).To(HaveLen(2)) 72 structmatcher.ExpectStructsToMatchExcluding(&results[0], &addFunction, "Oid") 73 structmatcher.ExpectStructsToMatchExcluding(&results[1], &appendFunction, "Oid") 74 }) 75 It("returns a slice of functions in a specific schema", func() { 76 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.add(integer, integer) RETURNS integer 77 AS 'SELECT $1 + $2' 78 LANGUAGE SQL`) 79 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.add(integer, integer)") 80 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 81 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 82 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION testschema.add(integer, integer) RETURNS integer 83 AS 'SELECT $1 + $2' 84 LANGUAGE SQL`) 85 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION testschema.add(integer, integer)") 86 87 addFunction := backup.Function{ 88 Schema: "testschema", Name: "add", Kind: prokindValue, ReturnsSet: false, FunctionBody: "SELECT $1 + $2", 89 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 90 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 91 ResultType: sql.NullString{String: "integer", Valid: true}, 92 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, PlannerSupport: plannerSupportValue, Config: "", Cost: 100, NumRows: 0, DataAccess: "c", 93 Language: "sql", ExecLocation: "a", Parallel: proparallelValue} 94 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 95 results := backup.GetFunctions(connectionPool) 96 97 Expect(results).To(HaveLen(1)) 98 structmatcher.ExpectStructsToMatchExcluding(&results[0], &addFunction, "Oid") 99 }) 100 It("returns a window function", func() { 101 testutils.SkipIfBefore6(connectionPool) 102 if true { 103 // GPDB7 only allows set-returning functions to execute on coordinator 104 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.add(integer, integer) RETURNS SETOF integer 105 AS 'SELECT $1 + $2' 106 LANGUAGE SQL WINDOW`) 107 } else { 108 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.add(integer, integer) RETURNS integer 109 AS 'SELECT $1 + $2' 110 LANGUAGE SQL WINDOW`) 111 } 112 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.add(integer, integer)") 113 114 results := backup.GetFunctions(connectionPool) 115 116 var windowFunction backup.Function 117 if true { 118 windowFunction = backup.Function{ 119 Schema: "public", Name: "add", ReturnsSet: true, FunctionBody: "SELECT $1 + $2", 120 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 121 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 122 ResultType: sql.NullString{String: "SETOF integer", Valid: true}, 123 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, PlannerSupport: plannerSupportValue, Config: "", Cost: 100, NumRows: 1000, DataAccess: "c", 124 Language: "sql", Kind: "w", ExecLocation: "a", Parallel: proparallelValue, IsWindow: true} 125 } else { 126 windowFunction = backup.Function{ 127 Schema: "public", Name: "add", ReturnsSet: false, FunctionBody: "SELECT $1 + $2", 128 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 129 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 130 ResultType: sql.NullString{String: "integer", Valid: true}, 131 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, 132 PlannerSupport: plannerSupportValue, Config: "", Cost: 100, NumRows: 0, DataAccess: "c", 133 Language: "sql", IsWindow: true, ExecLocation: "a", Parallel: proparallelValue} 134 } 135 Expect(results).To(HaveLen(1)) 136 structmatcher.ExpectStructsToMatchExcluding(&results[0], &windowFunction, "Oid") 137 }) 138 It("returns a function to execute on coordinator and all segments", func() { 139 testutils.SkipIfBefore6(connectionPool) 140 141 if false { 142 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.srf_on_coordinator(integer, integer) RETURNS integer 143 AS 'SELECT $1 + $2' 144 LANGUAGE SQL WINDOW 145 EXECUTE ON MASTER;`) 146 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.srf_on_all_segments(integer, integer) RETURNS integer 147 AS 'SELECT $1 + $2' 148 LANGUAGE SQL WINDOW 149 EXECUTE ON ALL SEGMENTS;`) 150 } else { 151 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.srf_on_coordinator(integer, integer) RETURNS SETOF integer 152 AS 'SELECT $1 + $2' 153 LANGUAGE SQL WINDOW 154 EXECUTE ON COORDINATOR;`) 155 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.srf_on_all_segments(integer, integer) RETURNS SETOF integer 156 AS 'SELECT $1 + $2' 157 LANGUAGE SQL WINDOW 158 EXECUTE ON ALL SEGMENTS;`) 159 } 160 161 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.srf_on_coordinator(integer, integer)") 162 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.srf_on_all_segments(integer, integer)") 163 164 results := backup.GetFunctions(connectionPool) 165 var prokindValue string 166 if true { 167 prokindValue = "w" 168 } else { 169 prokindValue = "" 170 } 171 172 srfOnCoordinatorFunction := backup.Function{ 173 Schema: "public", Name: "srf_on_coordinator", Kind: prokindValue, ReturnsSet: false, FunctionBody: "SELECT $1 + $2", 174 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 175 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 176 ResultType: sql.NullString{String: "integer", Valid: true}, 177 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, 178 PlannerSupport: plannerSupportValue, Config: "", Cost: 100, NumRows: 0, DataAccess: "c", 179 Language: "sql", IsWindow: true, ExecLocation: "m", Parallel: proparallelValue} 180 if true { 181 srfOnCoordinatorFunction.ExecLocation = "c" 182 183 // GPDB7 only allows set-returning functions to execute on coordinator 184 srfOnCoordinatorFunction.ReturnsSet = true 185 srfOnCoordinatorFunction.NumRows = 1000 186 srfOnCoordinatorFunction.ResultType = sql.NullString{String: "SETOF integer", Valid: true} 187 } 188 189 srfOnAllSegmentsFunction := backup.Function{ 190 Schema: "public", Name: "srf_on_all_segments", Kind: prokindValue, ReturnsSet: false, FunctionBody: "SELECT $1 + $2", 191 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 192 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 193 ResultType: sql.NullString{String: "integer", Valid: true}, 194 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, 195 PlannerSupport: plannerSupportValue, Config: "", Cost: 100, NumRows: 0, DataAccess: "c", 196 Language: "sql", IsWindow: true, ExecLocation: "s", Parallel: proparallelValue} 197 if true { 198 // GPDB7 only allows set-returning functions to execute on all segments 199 srfOnAllSegmentsFunction.ReturnsSet = true 200 srfOnAllSegmentsFunction.NumRows = 1000 201 srfOnAllSegmentsFunction.ResultType = sql.NullString{String: "SETOF integer", Valid: true} 202 } 203 204 Expect(results).To(HaveLen(2)) 205 structmatcher.ExpectStructsToMatchExcluding(&results[0], &srfOnAllSegmentsFunction, "Oid") 206 structmatcher.ExpectStructsToMatchExcluding(&results[1], &srfOnCoordinatorFunction, "Oid") 207 }) 208 It("returns a function to execute on initplan", func() { 209 if false { 210 Skip("Test only applicable to GPDB6.5 and above") 211 } 212 213 if true { 214 // GPDB7 only allows set-returning functions to execute on coordinator 215 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.srf_on_initplan(integer, integer) RETURNS SETOF integer 216 AS 'SELECT $1 + $2' 217 LANGUAGE SQL WINDOW 218 EXECUTE ON INITPLAN;`) 219 } else { 220 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.srf_on_initplan(integer, integer) RETURNS integer 221 AS 'SELECT $1 + $2' 222 LANGUAGE SQL WINDOW 223 EXECUTE ON INITPLAN;`) 224 } 225 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.srf_on_initplan(integer, integer)") 226 227 results := backup.GetFunctions(connectionPool) 228 229 var srfOnInitplan backup.Function 230 if true { 231 // GPDB7 only allows set-returning functions to execute on coordinator 232 srfOnInitplan = backup.Function{ 233 Schema: "public", Name: "srf_on_initplan", ReturnsSet: true, FunctionBody: "SELECT $1 + $2", 234 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 235 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 236 ResultType: sql.NullString{String: "SETOF integer", Valid: true}, 237 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, Config: "", Cost: 100, NumRows: 1000, DataAccess: "c", 238 PlannerSupport: plannerSupportValue, Language: "sql", IsWindow: true, ExecLocation: "i", 239 Parallel: proparallelValue, Kind: "w"} 240 } else { 241 srfOnInitplan = backup.Function{ 242 Schema: "public", Name: "srf_on_initplan", ReturnsSet: false, FunctionBody: "SELECT $1 + $2", 243 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 244 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 245 ResultType: sql.NullString{String: "integer", Valid: true}, 246 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, Config: "", Cost: 100, NumRows: 0, DataAccess: "c", 247 PlannerSupport: plannerSupportValue, Language: "sql", IsWindow: true, ExecLocation: "i", 248 Parallel: proparallelValue} 249 } 250 Expect(results).To(HaveLen(1)) 251 structmatcher.ExpectStructsToMatchExcluding(&results[0], &srfOnInitplan, "Oid") 252 }) 253 It("returns a function with LEAKPROOF", func() { 254 testutils.SkipIfBefore6(connectionPool) 255 testhelper.AssertQueryRuns(connectionPool, ` 256 CREATE FUNCTION public.append(integer, integer) RETURNS SETOF record 257 AS 'SELECT ($1, $2)' 258 LANGUAGE SQL 259 SECURITY DEFINER 260 STRICT 261 LEAKPROOF 262 STABLE 263 COST 200 264 ROWS 200 265 SET search_path = pg_temp 266 MODIFIES SQL DATA 267 `) 268 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.append(integer, integer)") 269 270 results := backup.GetFunctions(connectionPool) 271 272 appendFunction := backup.Function{ 273 Schema: "public", Name: "append", Kind: prokindValue, ReturnsSet: true, FunctionBody: "SELECT ($1, $2)", 274 BinaryPath: "", Arguments: sql.NullString{String: "integer, integer", Valid: true}, 275 IdentArgs: sql.NullString{String: "integer, integer", Valid: true}, 276 ResultType: sql.NullString{String: "SETOF record", Valid: true}, 277 Volatility: "s", IsStrict: true, IsLeakProof: true, IsSecurityDefiner: true, 278 PlannerSupport: plannerSupportValue, Config: `SET search_path TO 'pg_temp'`, Cost: 200, 279 NumRows: 200, DataAccess: "m", Language: "sql", ExecLocation: "a", Parallel: proparallelValue} 280 281 Expect(results).To(HaveLen(1)) 282 structmatcher.ExpectStructsToMatchExcluding(&results[0], &appendFunction, "Oid") 283 }) 284 It("does not return range type constructor functions", func() { 285 testutils.SkipIfBefore6(connectionPool) 286 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.textrange AS RANGE (SUBTYPE = pg_catalog.text)") 287 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.textrange") 288 289 results := backup.GetFunctions(connectionPool) 290 291 Expect(results).To(HaveLen(0)) 292 }) 293 It("returns a function that has quotes in Config", func() { 294 testhelper.AssertQueryRuns(connectionPool, ` 295 CREATE FUNCTION public.myfunc(integer) RETURNS text 296 LANGUAGE plpgsql NO SQL 297 SET work_mem TO '1MB' 298 AS $_$ 299 begin 300 set work_mem = '2MB'; 301 perform 1/$1; 302 return current_setting('work_mem'); 303 end $_$; 304 `) 305 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.myfunc(integer)") 306 307 results := backup.GetFunctions(connectionPool) 308 appendFunction := backup.Function{ 309 Schema: "public", Name: "myfunc", Kind: prokindValue, ReturnsSet: false, FunctionBody: ` 310 begin 311 set work_mem = '2MB'; 312 perform 1/$1; 313 return current_setting('work_mem'); 314 end `, 315 BinaryPath: "", Arguments: sql.NullString{String: "integer", Valid: true}, 316 IdentArgs: sql.NullString{String: "integer", Valid: true}, 317 ResultType: sql.NullString{String: "text", Valid: true}, 318 Volatility: "v", IsStrict: false, IsLeakProof: false, IsSecurityDefiner: false, 319 PlannerSupport: plannerSupportValue, Config: "SET work_mem TO '1MB'", Cost: 100, 320 NumRows: 0, DataAccess: "n", Language: "plpgsql", ExecLocation: "a", Parallel: proparallelValue} 321 Expect(results).To(HaveLen(1)) 322 structmatcher.ExpectStructsToMatchExcluding(&results[0], &appendFunction, "Oid") 323 }) 324 It("returns a function that sets a GUC with a string array value with quoted items", func() { 325 testhelper.AssertQueryRuns(connectionPool, `CREATE SCHEMA "abc""def"`) 326 327 testhelper.AssertQueryRuns(connectionPool, ` 328 CREATE FUNCTION public.myfunc(integer) RETURNS text 329 LANGUAGE plpgsql NO SQL 330 SET search_path TO "$user", public, "abc""def" 331 AS $_$ 332 begin 333 set work_mem = '2MB'; 334 perform 1/$1; 335 return current_setting('work_mem'); 336 end $_$; 337 `) 338 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.myfunc(integer)") 339 defer testhelper.AssertQueryRuns(connectionPool, `DROP SCHEMA "abc""def"`) 340 341 results := backup.GetFunctions(connectionPool) 342 343 appendFunction := backup.Function{ 344 Schema: "public", Name: "myfunc", Kind: prokindValue, ReturnsSet: false, FunctionBody: ` 345 begin 346 set work_mem = '2MB'; 347 perform 1/$1; 348 return current_setting('work_mem'); 349 end `, 350 BinaryPath: "", Arguments: sql.NullString{String: "integer", Valid: true}, 351 IdentArgs: sql.NullString{String: "integer", Valid: true}, 352 ResultType: sql.NullString{String: "text", Valid: true}, 353 Volatility: "v", IsStrict: false, IsLeakProof: false, IsSecurityDefiner: false, 354 PlannerSupport: plannerSupportValue, Config: `SET search_path TO '$user', 'public', 'abc"def'`, Cost: 100, 355 NumRows: 0, DataAccess: "n", Language: "plpgsql", ExecLocation: "a", Parallel: proparallelValue} 356 357 Expect(results).To(HaveLen(1)) 358 structmatcher.ExpectStructsToMatchExcluding(&results[0], &appendFunction, "Oid") 359 }) 360 It("includes stored procedures in the result", func() { 361 testutils.SkipIfBefore7(connectionPool) 362 363 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.tbl (n int);`) 364 defer testhelper.AssertQueryRuns(connectionPool, `DROP TABLE public.tbl;`) 365 366 testhelper.AssertQueryRuns(connectionPool, ` 367 CREATE PROCEDURE 368 public.insert_data(a integer, b integer) LANGUAGE SQL AS $$ 369 INSERT INTO public.tbl VALUES (a); 370 INSERT INTO public.tbl VALUES (b); 371 $$;`) 372 defer testhelper.AssertQueryRuns(connectionPool, `DROP PROCEDURE public.insert_data(a integer, b integer);`) 373 374 testhelper.AssertQueryRuns(connectionPool, ` 375 CREATE PROCEDURE 376 public.insert_more_data(a integer, b integer) LANGUAGE SQL AS $$ 377 INSERT INTO public.tbl VALUES (a); 378 INSERT INTO public.tbl VALUES (b); 379 $$;`) 380 defer testhelper.AssertQueryRuns(connectionPool, `DROP PROCEDURE public.insert_more_data(a integer, b integer);`) 381 382 results := backup.GetFunctions(connectionPool) 383 384 firstProcedure := backup.Function{ 385 Schema: "public", Name: "insert_data", Kind: "p", ReturnsSet: false, FunctionBody: ` 386 INSERT INTO public.tbl VALUES (a); 387 INSERT INTO public.tbl VALUES (b); 388 `, 389 BinaryPath: "", Arguments: sql.NullString{String: "a integer, b integer", Valid: true}, 390 IdentArgs: sql.NullString{String: "a integer, b integer", Valid: true}, 391 ResultType: sql.NullString{String: "", Valid: false}, 392 Volatility: "v", IsSecurityDefiner: false, PlannerSupport: plannerSupportValue, 393 Config: "", Cost: 100, NumRows: 0, DataAccess: "c", 394 Language: "sql", ExecLocation: "a", Parallel: proparallelValue} 395 secondProcedure := backup.Function{ 396 Schema: "public", Name: "insert_more_data", Kind: "p", ReturnsSet: false, FunctionBody: ` 397 INSERT INTO public.tbl VALUES (a); 398 INSERT INTO public.tbl VALUES (b); 399 `, 400 BinaryPath: "", Arguments: sql.NullString{String: "a integer, b integer", Valid: true}, 401 IdentArgs: sql.NullString{String: "a integer, b integer", Valid: true}, 402 ResultType: sql.NullString{String: "", Valid: false}, 403 Volatility: "v", IsSecurityDefiner: false, PlannerSupport: plannerSupportValue, 404 Config: "", Cost: 100, NumRows: 0, DataAccess: "c", 405 Language: "sql", ExecLocation: "a", Parallel: proparallelValue} 406 407 Expect(results).To(HaveLen(2)) 408 structmatcher.ExpectStructsToMatchExcluding(&results[0], &firstProcedure, "Oid") 409 structmatcher.ExpectStructsToMatchExcluding(&results[1], &secondProcedure, "Oid") 410 }) 411 }) 412 Describe("GetFunctions4", func() { 413 BeforeEach(func() { 414 testutils.SkipIfNot4(connectionPool) 415 }) 416 It("returns a slice of functions", func() { 417 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.add(numeric, integer) RETURNS numeric 418 AS 'SELECT $1 + $2' 419 LANGUAGE SQL`) 420 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.add(numeric, integer)") 421 testhelper.AssertQueryRuns(connectionPool, ` 422 CREATE FUNCTION public.append(float, integer) RETURNS SETOF record 423 AS 'SELECT ($1, $2)' 424 LANGUAGE SQL 425 SECURITY DEFINER 426 STRICT 427 STABLE 428 `) 429 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.append(float, integer)") 430 testhelper.AssertQueryRuns(connectionPool, "COMMENT ON FUNCTION public.append(float, integer) IS 'this is a function comment'") 431 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public."specChar"(t text, "precision" double precision) RETURNS double precision AS $$BEGIN RETURN precision + 1; END;$$ LANGUAGE PLPGSQL;`) 432 defer testhelper.AssertQueryRuns(connectionPool, `DROP FUNCTION public."specChar"(text, double precision)`) 433 434 results := backup.GetFunctions4(connectionPool) 435 436 addFunction := backup.Function{ 437 Schema: "public", Name: "add", ReturnsSet: false, FunctionBody: "SELECT $1 + $2", BinaryPath: "", 438 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, NumRows: 0, Language: "sql", ExecLocation: "a"} 439 appendFunction := backup.Function{ 440 Schema: "public", Name: "append", ReturnsSet: true, FunctionBody: "SELECT ($1, $2)", BinaryPath: "", 441 Volatility: "s", IsStrict: true, IsSecurityDefiner: true, Language: "sql", ExecLocation: "a"} 442 specCharFunction := backup.Function{ 443 Schema: "public", Name: `"specChar"`, ReturnsSet: false, FunctionBody: "BEGIN RETURN precision + 1; END;", BinaryPath: "", 444 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, NumRows: 0, Language: "plpgsql", ExecLocation: "a"} 445 446 Expect(results).To(HaveLen(3)) 447 structmatcher.ExpectStructsToMatchExcluding(&results[0], &addFunction, "Oid") 448 structmatcher.ExpectStructsToMatchExcluding(&results[1], &appendFunction, "Oid") 449 structmatcher.ExpectStructsToMatchExcluding(&results[2], &specCharFunction, "Oid") 450 }) 451 It("returns a slice of functions in a specific schema", func() { 452 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.add(numeric, integer) RETURNS numeric 453 AS 'SELECT $1 + $2' 454 LANGUAGE SQL`) 455 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.add(numeric, integer)") 456 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 457 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 458 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION testschema.add(float, integer) RETURNS float 459 AS 'SELECT $1 + $2' 460 LANGUAGE SQL`) 461 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION testschema.add(float, integer)") 462 463 addFunction := backup.Function{ 464 Schema: "testschema", Name: "add", ReturnsSet: false, FunctionBody: "SELECT $1 + $2", BinaryPath: "", 465 Volatility: "v", IsStrict: false, IsSecurityDefiner: false, Language: "sql", ExecLocation: "a"} 466 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 467 results := backup.GetFunctions4(connectionPool) 468 469 Expect(results).To(HaveLen(1)) 470 structmatcher.ExpectStructsToMatchExcluding(&results[0], &addFunction, "Oid") 471 }) 472 }) 473 Describe("GetAggregates", func() { 474 BeforeEach(func() { 475 testhelper.AssertQueryRuns(connectionPool, ` 476 CREATE FUNCTION public.mysfunc_accum(numeric, numeric, numeric) 477 RETURNS numeric 478 AS 'select $1 + $2 + $3' 479 LANGUAGE SQL 480 IMMUTABLE; 481 `) 482 testhelper.AssertQueryRuns(connectionPool, ` 483 CREATE FUNCTION public.mypre_accum(numeric, numeric) 484 RETURNS numeric 485 AS 'select $1 + $2' 486 LANGUAGE SQL 487 IMMUTABLE 488 RETURNS NULL ON NULL INPUT; 489 `) 490 }) 491 AfterEach(func() { 492 testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.mysfunc_accum(numeric, numeric, numeric)") 493 testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.mypre_accum(numeric, numeric)") 494 }) 495 It("creates an aggregate with sortop in pg_catalog", func() { 496 testhelper.AssertQueryRuns(connectionPool, ` 497 CREATE FUNCTION public.ascii_larger(prev character, curr character) RETURNS character 498 AS $$ 499 begin if (prev ~>~ curr) then return prev; end if; return curr; end; $$ 500 LANGUAGE plpgsql IMMUTABLE NO SQL;`) 501 transitionOid := testutils.OidFromObjectName(connectionPool, "public", "ascii_larger", backup.TYPE_FUNCTION) 502 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.ascii_larger(character, character)") 503 504 testhelper.AssertQueryRuns(connectionPool, ` 505 CREATE AGGREGATE public.ascii_max(character) ( 506 SFUNC = public.ascii_larger, 507 STYPE = character, 508 SORTOP = ~>~ );`) 509 defer testhelper.AssertQueryRuns(connectionPool, "DROP AGGREGATE public.ascii_max(character)") 510 511 resultAggregates := backup.GetAggregates(connectionPool) 512 aggregateDef := backup.Aggregate{ 513 Schema: "public", Name: "ascii_max", Arguments: sql.NullString{String: "character", Valid: true}, 514 IdentArgs: sql.NullString{String: "character", Valid: true}, 515 TransitionFunction: transitionOid, FinalFunction: 0, SortOperator: "~>~", SortOperatorSchema: "pg_catalog", TransitionDataType: "character", 516 InitialValue: "", InitValIsNull: true, MInitValIsNull: true, IsOrdered: false, 517 } 518 if true { 519 aggregateDef.Kind = "n" 520 aggregateDef.Finalmodify = "r" 521 aggregateDef.Mfinalmodify = "r" 522 aggregateDef.Parallel = "u" 523 } 524 525 Expect(resultAggregates).To(HaveLen(1)) 526 structmatcher.ExpectStructsToMatchExcluding(&resultAggregates[0], &aggregateDef, "Oid") 527 }) 528 It("returns a slice of aggregates", func() { 529 testhelper.AssertQueryRuns(connectionPool, ` 530 CREATE AGGREGATE public.agg_prefunc(numeric, numeric) ( 531 SFUNC = public.mysfunc_accum, 532 STYPE = numeric, 533 PREFUNC = public.mypre_accum, 534 INITCOND = 0 ); 535 `) 536 defer testhelper.AssertQueryRuns(connectionPool, "DROP AGGREGATE public.agg_prefunc(numeric, numeric)") 537 538 transitionOid := testutils.OidFromObjectName(connectionPool, "public", "mysfunc_accum", backup.TYPE_FUNCTION) 539 prelimOid := testutils.OidFromObjectName(connectionPool, "public", "mypre_accum", backup.TYPE_FUNCTION) 540 541 result := backup.GetAggregates(connectionPool) 542 543 aggregateDef := backup.Aggregate{ 544 Schema: "public", Name: "agg_prefunc", Arguments: sql.NullString{String: "numeric, numeric", Valid: true}, 545 IdentArgs: sql.NullString{String: "numeric, numeric", Valid: true}, TransitionFunction: transitionOid, PreliminaryFunction: prelimOid, 546 FinalFunction: 0, SortOperator: "", TransitionDataType: "numeric", InitialValue: "0", MInitValIsNull: true, IsOrdered: false, 547 } 548 if true { 549 aggregateDef.PreliminaryFunction = 0 550 aggregateDef.CombineFunction = prelimOid 551 } 552 if true { 553 aggregateDef.Kind = "n" 554 aggregateDef.Finalmodify = "r" 555 aggregateDef.Mfinalmodify = "r" 556 aggregateDef.Parallel = "u" 557 } 558 559 Expect(result).To(HaveLen(1)) 560 structmatcher.ExpectStructsToMatchExcluding(&result[0], &aggregateDef, "Oid") 561 }) 562 It("returns a slice of aggregates in a specific schema", func() { 563 testhelper.AssertQueryRuns(connectionPool, ` 564 CREATE AGGREGATE public.agg_prefunc(numeric, numeric) ( 565 SFUNC = public.mysfunc_accum, 566 STYPE = numeric, 567 PREFUNC = public.mypre_accum, 568 INITCOND = 0 ); 569 `) 570 defer testhelper.AssertQueryRuns(connectionPool, "DROP AGGREGATE public.agg_prefunc(numeric, numeric)") 571 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 572 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 573 testhelper.AssertQueryRuns(connectionPool, ` 574 CREATE AGGREGATE testschema.agg_prefunc(numeric, numeric) ( 575 SFUNC = public.mysfunc_accum, 576 STYPE = numeric, 577 PREFUNC = public.mypre_accum, 578 INITCOND = 0 ); 579 `) 580 defer testhelper.AssertQueryRuns(connectionPool, "DROP AGGREGATE testschema.agg_prefunc(numeric, numeric)") 581 582 transitionOid := testutils.OidFromObjectName(connectionPool, "public", "mysfunc_accum", backup.TYPE_FUNCTION) 583 prelimOid := testutils.OidFromObjectName(connectionPool, "public", "mypre_accum", backup.TYPE_FUNCTION) 584 aggregateDef := backup.Aggregate{ 585 Schema: "testschema", Name: "agg_prefunc", Arguments: sql.NullString{String: "numeric, numeric", Valid: true}, 586 IdentArgs: sql.NullString{String: "numeric, numeric", Valid: true}, TransitionFunction: transitionOid, PreliminaryFunction: prelimOid, 587 FinalFunction: 0, SortOperator: "", TransitionDataType: "numeric", InitialValue: "0", MInitValIsNull: true, IsOrdered: false, 588 } 589 if true { 590 aggregateDef.PreliminaryFunction = 0 591 aggregateDef.CombineFunction = prelimOid 592 } 593 if true { 594 aggregateDef.Kind = "n" 595 aggregateDef.Finalmodify = "r" 596 aggregateDef.Mfinalmodify = "r" 597 aggregateDef.Parallel = "u" 598 } 599 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 600 601 result := backup.GetAggregates(connectionPool) 602 603 Expect(result).To(HaveLen(1)) 604 structmatcher.ExpectStructsToMatchExcluding(&result[0], &aggregateDef, "Oid") 605 }) 606 It("returns a slice for a hypothetical ordered-set aggregate", func() { 607 testutils.SkipIfBefore6(connectionPool) 608 609 testhelper.AssertQueryRuns(connectionPool, ` 610 CREATE AGGREGATE public.agg_hypo_ord (VARIADIC "any" ORDER BY VARIADIC "any") 611 ( 612 SFUNC = pg_catalog.ordered_set_transition_multi, 613 STYPE = internal, 614 FINALFUNC = pg_catalog.rank_final, 615 FINALFUNC_EXTRA, 616 HYPOTHETICAL 617 );`) 618 defer testhelper.AssertQueryRuns(connectionPool, `DROP AGGREGATE public.agg_hypo_ord(VARIADIC "any" ORDER BY VARIADIC "any")`) 619 620 transitionOid := testutils.OidFromObjectName(connectionPool, "pg_catalog", "ordered_set_transition_multi", backup.TYPE_FUNCTION) 621 finalOid := testutils.OidFromObjectName(connectionPool, "pg_catalog", "rank_final", backup.TYPE_FUNCTION) 622 623 result := backup.GetAggregates(connectionPool) 624 625 aggregateDef := backup.Aggregate{ 626 Schema: "public", Name: "agg_hypo_ord", Arguments: sql.NullString{String: `VARIADIC "any" ORDER BY VARIADIC "any"`, Valid: true}, 627 IdentArgs: sql.NullString{String: `VARIADIC "any" ORDER BY VARIADIC "any"`, Valid: true}, TransitionFunction: transitionOid, 628 FinalFunction: finalOid, TransitionDataType: "internal", InitValIsNull: true, MInitValIsNull: true, FinalFuncExtra: true, Hypothetical: true, 629 } 630 if true { 631 aggregateDef.Hypothetical = false 632 aggregateDef.Kind = "h" 633 aggregateDef.Finalmodify = "w" 634 aggregateDef.Mfinalmodify = "w" 635 aggregateDef.Parallel = "u" 636 } 637 638 Expect(result).To(HaveLen(1)) 639 structmatcher.ExpectStructsToMatchExcluding(&result[0], &aggregateDef, "Oid") 640 }) 641 It("returns a slice of aggregates with a combine function and transition data size", func() { 642 testutils.SkipIfBefore6(connectionPool) 643 testhelper.AssertQueryRuns(connectionPool, ` 644 CREATE AGGREGATE public.agg_combinefunc(numeric, numeric) ( 645 SFUNC = public.mysfunc_accum, 646 STYPE = numeric, 647 SSPACE = 1000, 648 COMBINEFUNC = public.mypre_accum, 649 INITCOND = 0 ); 650 `) 651 defer testhelper.AssertQueryRuns(connectionPool, "DROP AGGREGATE public.agg_combinefunc(numeric, numeric)") 652 653 transitionOid := testutils.OidFromObjectName(connectionPool, "public", "mysfunc_accum", backup.TYPE_FUNCTION) 654 combineOid := testutils.OidFromObjectName(connectionPool, "public", "mypre_accum", backup.TYPE_FUNCTION) 655 656 result := backup.GetAggregates(connectionPool) 657 658 aggregateDef := backup.Aggregate{ 659 Schema: "public", Name: "agg_combinefunc", Arguments: sql.NullString{String: "numeric, numeric", Valid: true}, 660 IdentArgs: sql.NullString{String: "numeric, numeric", Valid: true}, TransitionFunction: transitionOid, CombineFunction: combineOid, 661 FinalFunction: 0, SortOperator: "", TransitionDataType: "numeric", TransitionDataSize: 1000, 662 InitialValue: "0", MInitValIsNull: true, IsOrdered: false, 663 } 664 if true { 665 aggregateDef.Kind = "n" 666 aggregateDef.Finalmodify = "r" 667 aggregateDef.Mfinalmodify = "r" 668 aggregateDef.Parallel = "u" 669 } 670 671 Expect(result).To(HaveLen(1)) 672 structmatcher.ExpectStructsToMatchExcluding(&result[0], &aggregateDef, "Oid") 673 }) 674 It("returns a slice of aggregates with serial/deserial functions", func() { 675 testutils.SkipIfBefore6(connectionPool) 676 testhelper.AssertQueryRuns(connectionPool, ` 677 CREATE AGGREGATE public.myavg (numeric) ( 678 stype = internal, 679 sfunc = numeric_avg_accum, 680 finalfunc = numeric_avg, 681 serialfunc = numeric_avg_serialize, 682 deserialfunc = numeric_avg_deserialize); 683 `) 684 defer testhelper.AssertQueryRuns(connectionPool, "DROP AGGREGATE public.myavg(numeric)") 685 686 serialOid := testutils.OidFromObjectName(connectionPool, "pg_catalog", "numeric_avg_serialize", backup.TYPE_FUNCTION) 687 deserialOid := testutils.OidFromObjectName(connectionPool, "pg_catalog", "numeric_avg_deserialize", backup.TYPE_FUNCTION) 688 689 result := backup.GetAggregates(connectionPool) 690 691 aggregateDef := backup.Aggregate{ 692 Schema: "public", Name: "myavg", Arguments: sql.NullString{String: "numeric", Valid: true}, 693 IdentArgs: sql.NullString{String: "numeric", Valid: true}, SerialFunction: serialOid, DeserialFunction: deserialOid, 694 FinalFunction: 0, SortOperator: "", TransitionDataType: "internal", 695 IsOrdered: false, InitValIsNull: true, MInitValIsNull: true, 696 } 697 if true { 698 aggregateDef.Kind = "n" 699 aggregateDef.Finalmodify = "r" 700 aggregateDef.Mfinalmodify = "r" 701 aggregateDef.Parallel = "u" 702 } 703 704 Expect(result).To(HaveLen(1)) 705 structmatcher.ExpectStructsToMatchExcluding(&result[0], &aggregateDef, "Oid", "TransitionFunction", "FinalFunction") 706 }) 707 It("returns a slice of aggregates with moving attributes", func() { 708 testutils.SkipIfBefore6(connectionPool) 709 testhelper.AssertQueryRuns(connectionPool, ` 710 CREATE AGGREGATE public.moving_agg(numeric,numeric) ( 711 SFUNC = public.mysfunc_accum, 712 STYPE = numeric, 713 MSFUNC = public.mysfunc_accum, 714 MINVFUNC = public.mysfunc_accum, 715 MSTYPE = numeric, 716 MSSPACE = 100, 717 MFINALFUNC = public.mysfunc_accum, 718 MFINALFUNC_EXTRA, 719 MINITCOND = 0 720 ); 721 `) 722 defer testhelper.AssertQueryRuns(connectionPool, "DROP AGGREGATE public.moving_agg(numeric, numeric)") 723 724 sfuncOid := testutils.OidFromObjectName(connectionPool, "public", "mysfunc_accum", backup.TYPE_FUNCTION) 725 726 result := backup.GetAggregates(connectionPool) 727 728 aggregateDef := backup.Aggregate{ 729 Schema: "public", Name: "moving_agg", Arguments: sql.NullString{String: "numeric, numeric", Valid: true}, 730 IdentArgs: sql.NullString{String: "numeric, numeric", Valid: true}, TransitionFunction: sfuncOid, TransitionDataType: "numeric", 731 InitValIsNull: true, MTransitionFunction: sfuncOid, MInverseTransitionFunction: sfuncOid, 732 MTransitionDataType: "numeric", MTransitionDataSize: 100, MFinalFunction: sfuncOid, 733 MFinalFuncExtra: true, MInitialValue: "0", MInitValIsNull: false, 734 } 735 if true { 736 aggregateDef.Kind = "n" 737 aggregateDef.Finalmodify = "r" 738 aggregateDef.Mfinalmodify = "r" 739 aggregateDef.Parallel = "u" 740 } 741 742 Expect(result).To(HaveLen(1)) 743 structmatcher.ExpectStructsToMatchExcluding(&result[0], &aggregateDef, "Oid") 744 }) 745 }) 746 Describe("GetFunctionOidToInfoMap", func() { 747 It("returns map containing function information", func() { 748 result := backup.GetFunctionOidToInfoMap(connectionPool) 749 initialLength := len(result) 750 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION public.add(integer, integer) RETURNS integer 751 AS 'SELECT $1 + $2' 752 LANGUAGE SQL`) 753 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.add(integer, integer)") 754 755 result = backup.GetFunctionOidToInfoMap(connectionPool) 756 oid := testutils.OidFromObjectName(connectionPool, "public", "add", backup.TYPE_FUNCTION) 757 Expect(result).To(HaveLen(initialLength + 1)) 758 Expect(result[oid].QualifiedName).To(Equal("public.add")) 759 Expect(result[oid].Arguments.String).To(Equal("integer, integer")) 760 Expect(result[oid].IsInternal).To(BeFalse()) 761 }) 762 It("returns a map containing an internal function", func() { 763 result := backup.GetFunctionOidToInfoMap(connectionPool) 764 765 oid := testutils.OidFromObjectName(connectionPool, "pg_catalog", "boolin", backup.TYPE_FUNCTION) 766 Expect(result[oid].QualifiedName).To(Equal("pg_catalog.boolin")) 767 Expect(result[oid].IsInternal).To(BeTrue()) 768 }) 769 }) 770 Describe("GetCasts", func() { 771 It("returns a slice for a basic cast with a function in 4.3", func() { 772 testutils.SkipIfNot4(connectionPool) 773 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.casttotext(bool) RETURNS pg_catalog.text STRICT IMMUTABLE LANGUAGE PLPGSQL AS $$ BEGIN IF $1 IS TRUE THEN RETURN 'true'; ELSE RETURN 'false'; END IF; END; $$;") 774 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.casttotext(bool)") 775 testhelper.AssertQueryRuns(connectionPool, "CREATE CAST (bool AS text) WITH FUNCTION public.casttotext(bool) AS ASSIGNMENT") 776 defer testhelper.AssertQueryRuns(connectionPool, "DROP CAST (bool AS text)") 777 778 results := backup.GetCasts(connectionPool) 779 780 castDef := backup.Cast{Oid: 0, SourceTypeFQN: "pg_catalog.bool", TargetTypeFQN: "pg_catalog.text", FunctionSchema: "public", FunctionName: "casttotext", FunctionArgs: "boolean", CastContext: "a", CastMethod: "f"} 781 782 Expect(results).To(HaveLen(1)) 783 structmatcher.ExpectStructsToMatchExcluding(&castDef, &results[0], "Oid", "FunctionOid") 784 }) 785 It("returns a slice for a basic cast with a function in 5 and 6", func() { 786 testutils.SkipIfBefore5(connectionPool) 787 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.casttoint(text) RETURNS integer STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT cast($1 as integer);'") 788 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.casttoint(text)") 789 testhelper.AssertQueryRuns(connectionPool, "CREATE CAST (text AS integer) WITH FUNCTION public.casttoint(text) AS ASSIGNMENT") 790 defer testhelper.AssertQueryRuns(connectionPool, "DROP CAST (text AS int4)") 791 792 results := backup.GetCasts(connectionPool) 793 794 castDef := backup.Cast{Oid: 0, SourceTypeFQN: "pg_catalog.text", TargetTypeFQN: "pg_catalog.int4", FunctionSchema: "public", FunctionName: "casttoint", FunctionArgs: "text", CastContext: "a", CastMethod: "f"} 795 796 Expect(results).To(HaveLen(1)) 797 structmatcher.ExpectStructsToMatchExcluding(&castDef, &results[0], "Oid") 798 }) 799 It("returns a slice for a basic cast without a function", func() { 800 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.cast_in(cstring) RETURNS public.casttesttype AS $$textin$$ LANGUAGE internal STRICT NO SQL") 801 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.cast_out(public.casttesttype) RETURNS cstring AS $$textout$$ LANGUAGE internal STRICT NO SQL") 802 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.casttesttype (INTERNALLENGTH = variable, INPUT = public.cast_in, OUTPUT = public.cast_out)") 803 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.casttesttype CASCADE") 804 testhelper.AssertQueryRuns(connectionPool, "CREATE CAST (text AS public.casttesttype) WITHOUT FUNCTION AS IMPLICIT") 805 defer testhelper.AssertQueryRuns(connectionPool, "DROP CAST (text AS public.casttesttype)") 806 807 results := backup.GetCasts(connectionPool) 808 809 castDef := backup.Cast{Oid: 0, SourceTypeFQN: "pg_catalog.text", TargetTypeFQN: "public.casttesttype", FunctionSchema: "", FunctionName: "", FunctionArgs: "", CastContext: "i", CastMethod: "b"} 810 811 Expect(results).To(HaveLen(1)) 812 structmatcher.ExpectStructsToMatchExcluding(&castDef, &results[0], "Oid") 813 }) 814 It("returns a slice of casts with the source and target types in a different schema", func() { 815 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema1") 816 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema1") 817 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION cast_in(cstring) RETURNS testschema1.casttesttype AS $$textin$$ LANGUAGE internal STRICT NO SQL") 818 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION cast_out(testschema1.casttesttype) RETURNS cstring AS $$textout$$ LANGUAGE internal STRICT NO SQL") 819 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE testschema1.casttesttype (INTERNALLENGTH = variable, INPUT = cast_in, OUTPUT = cast_out)") 820 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE testschema1.casttesttype CASCADE") 821 822 testhelper.AssertQueryRuns(connectionPool, "CREATE CAST (text AS testschema1.casttesttype) WITHOUT FUNCTION AS IMPLICIT") 823 defer testhelper.AssertQueryRuns(connectionPool, "DROP CAST (text AS testschema1.casttesttype)") 824 testhelper.AssertQueryRuns(connectionPool, "CREATE CAST (testschema1.casttesttype AS text) WITHOUT FUNCTION AS IMPLICIT") 825 defer testhelper.AssertQueryRuns(connectionPool, "DROP CAST (testschema1.casttesttype AS text)") 826 827 results := backup.GetCasts(connectionPool) 828 829 castDefTarget := backup.Cast{Oid: 0, SourceTypeFQN: "pg_catalog.text", TargetTypeFQN: "testschema1.casttesttype", FunctionSchema: "", FunctionName: "", FunctionArgs: "", CastContext: "i", CastMethod: "b"} 830 castDefSource := backup.Cast{Oid: 0, SourceTypeFQN: "testschema1.casttesttype", TargetTypeFQN: "pg_catalog.text", FunctionSchema: "", FunctionName: "", FunctionArgs: "", CastContext: "i", CastMethod: "b"} 831 832 Expect(results).To(HaveLen(2)) 833 structmatcher.ExpectStructsToMatchExcluding(&castDefTarget, &results[0], "Oid") 834 structmatcher.ExpectStructsToMatchExcluding(&castDefSource, &results[1], "Oid") 835 }) 836 It("returns a slice for an inout cast", func() { 837 testutils.SkipIfBefore6(connectionPool) 838 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.custom_numeric AS (i numeric)") 839 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.custom_numeric") 840 testhelper.AssertQueryRuns(connectionPool, "CREATE CAST (varchar AS public.custom_numeric) WITH INOUT") 841 defer testhelper.AssertQueryRuns(connectionPool, "DROP CAST (varchar AS public.custom_numeric)") 842 843 results := backup.GetCasts(connectionPool) 844 845 castDef := backup.Cast{Oid: 0, SourceTypeFQN: `pg_catalog."varchar"`, TargetTypeFQN: "public.custom_numeric", FunctionSchema: "", FunctionName: "", FunctionArgs: "", CastContext: "e", CastMethod: "i"} 846 847 Expect(results).To(HaveLen(1)) 848 structmatcher.ExpectStructsToMatchExcluding(&castDef, &results[0], "Oid") 849 }) 850 }) 851 Describe("GetExtensions", func() { 852 It("returns a slice of extension", func() { 853 testutils.SkipIfBefore5(connectionPool) 854 testhelper.AssertQueryRuns(connectionPool, "CREATE EXTENSION plperl") 855 defer testhelper.AssertQueryRuns(connectionPool, "DROP EXTENSION plperl") 856 857 results := backup.GetExtensions(connectionPool) 858 859 Expect(results).To(HaveLen(1)) 860 861 plperlDef := backup.Extension{Oid: 0, Name: "plperl", Schema: "pg_catalog"} 862 structmatcher.ExpectStructsToMatchExcluding(&plperlDef, &results[0], "Oid") 863 }) 864 }) 865 Describe("GetProceduralLanguages", func() { 866 It("returns a slice of procedural languages", func() { 867 plpythonString := "plpython" 868 if true { 869 plpythonString = "plpython3" 870 } 871 872 testhelper.AssertQueryRuns(connectionPool, fmt.Sprintf("CREATE LANGUAGE %su", plpythonString)) 873 defer testhelper.AssertQueryRuns(connectionPool, fmt.Sprintf("DROP LANGUAGE %su", plpythonString)) 874 875 pythonHandlerOid := testutils.OidFromObjectName(connectionPool, "pg_catalog", fmt.Sprintf("%s_call_handler", plpythonString), backup.TYPE_FUNCTION) 876 877 expectedPlpythonInfo := backup.ProceduralLanguage{Oid: 1, Name: fmt.Sprintf("%su", plpythonString), Owner: "testrole", IsPl: true, PlTrusted: false, Handler: pythonHandlerOid, Inline: 0, Validator: 0} 878 if true { 879 pythonInlineOid := testutils.OidFromObjectName(connectionPool, "pg_catalog", fmt.Sprintf("%s_inline_handler", plpythonString), backup.TYPE_FUNCTION) 880 expectedPlpythonInfo.Inline = pythonInlineOid 881 } 882 if true { 883 expectedPlpythonInfo.Validator = testutils.OidFromObjectName(connectionPool, "pg_catalog", fmt.Sprintf("%s_validator", plpythonString), backup.TYPE_FUNCTION) 884 } 885 886 resultProcLangs := backup.GetProceduralLanguages(connectionPool) 887 888 Expect(resultProcLangs).To(HaveLen(1)) 889 structmatcher.ExpectStructsToMatchExcluding(&expectedPlpythonInfo, &resultProcLangs[0], "Oid", "Owner") 890 }) 891 }) 892 Describe("GetConversions", func() { 893 It("returns a slice of conversions", func() { 894 testhelper.AssertQueryRuns(connectionPool, "CREATE CONVERSION public.testconv FOR 'LATIN1' TO 'MULE_INTERNAL' FROM latin1_to_mic") 895 defer testhelper.AssertQueryRuns(connectionPool, "DROP CONVERSION public.testconv") 896 897 expectedConversion := backup.Conversion{Oid: 0, Schema: "public", Name: "testconv", ForEncoding: "LATIN1", ToEncoding: "MULE_INTERNAL", ConversionFunction: "pg_catalog.latin1_to_mic", IsDefault: false} 898 899 resultConversions := backup.GetConversions(connectionPool) 900 901 Expect(resultConversions).To(HaveLen(1)) 902 structmatcher.ExpectStructsToMatchExcluding(&expectedConversion, &resultConversions[0], "Oid") 903 }) 904 It("returns a slice of conversions in a specific schema", func() { 905 testhelper.AssertQueryRuns(connectionPool, "CREATE CONVERSION public.testconv FOR 'LATIN1' TO 'MULE_INTERNAL' FROM latin1_to_mic") 906 defer testhelper.AssertQueryRuns(connectionPool, "DROP CONVERSION public.testconv") 907 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 908 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 909 testhelper.AssertQueryRuns(connectionPool, "CREATE CONVERSION testschema.testconv FOR 'LATIN1' TO 'MULE_INTERNAL' FROM latin1_to_mic") 910 defer testhelper.AssertQueryRuns(connectionPool, "DROP CONVERSION testschema.testconv") 911 912 expectedConversion := backup.Conversion{Oid: 0, Schema: "testschema", Name: "testconv", ForEncoding: "LATIN1", ToEncoding: "MULE_INTERNAL", ConversionFunction: "pg_catalog.latin1_to_mic", IsDefault: false} 913 914 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 915 resultConversions := backup.GetConversions(connectionPool) 916 917 Expect(resultConversions).To(HaveLen(1)) 918 structmatcher.ExpectStructsToMatchExcluding(&expectedConversion, &resultConversions[0], "Oid") 919 }) 920 }) 921 Describe("GetTransforms", func() { 922 BeforeEach(func() { 923 testutils.SkipIfBefore7(connectionPool) 924 }) 925 It("returns a slice of transfroms", func() { 926 testhelper.AssertQueryRuns(connectionPool, "CREATE TRANSFORM FOR pg_catalog.int4 LANGUAGE c (FROM SQL WITH FUNCTION numeric_support(internal), TO SQL WITH FUNCTION int4recv(internal));") 927 defer testhelper.AssertQueryRuns(connectionPool, "DROP TRANSFORM FOR int4 LANGUAGE c") 928 929 fromSQLFuncOid := testutils.OidFromObjectName(connectionPool, "pg_catalog", "numeric_support", backup.TYPE_FUNCTION) 930 toSQLFuncOid := testutils.OidFromObjectName(connectionPool, "pg_catalog", "int4recv", backup.TYPE_FUNCTION) 931 932 expectedTransforms := backup.Transform{TypeNamespace: "pg_catalog", TypeName: "int4", LanguageName: "c", FromSQLFunc: fromSQLFuncOid, ToSQLFunc: toSQLFuncOid} 933 934 resultTransforms := backup.GetTransforms(connectionPool) 935 936 Expect(resultTransforms).To(HaveLen(1)) 937 structmatcher.ExpectStructsToMatchExcluding(&expectedTransforms, &resultTransforms[0], "Oid") 938 }) 939 }) 940 Describe("GetForeignDataWrappers", func() { 941 BeforeEach(func() { 942 testutils.SkipIfBefore6(connectionPool) 943 }) 944 It("returns a slice of foreign data wrappers", func() { 945 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN DATA WRAPPER foreigndatawrapper") 946 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN DATA WRAPPER foreigndatawrapper") 947 948 expectedForeignDataWrapper := backup.ForeignDataWrapper{Oid: 0, Name: "foreigndatawrapper"} 949 950 resultForeignDataWrapper := backup.GetForeignDataWrappers(connectionPool) 951 952 Expect(resultForeignDataWrapper).To(HaveLen(1)) 953 structmatcher.ExpectStructsToMatchExcluding(&expectedForeignDataWrapper, &resultForeignDataWrapper[0], "Oid") 954 }) 955 It("returns a slice of foreign data wrappers with a validator", func() { 956 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN DATA WRAPPER foreigndatawrapper VALIDATOR postgresql_fdw_validator") 957 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN DATA WRAPPER foreigndatawrapper") 958 959 validatorOid := testutils.OidFromObjectName(connectionPool, "pg_catalog", "postgresql_fdw_validator", backup.TYPE_FUNCTION) 960 expectedForeignDataWrapper := backup.ForeignDataWrapper{Oid: 0, Name: "foreigndatawrapper", Validator: validatorOid} 961 962 resultForeignDataWrapper := backup.GetForeignDataWrappers(connectionPool) 963 964 Expect(resultForeignDataWrapper).To(HaveLen(1)) 965 structmatcher.ExpectStructsToMatchExcluding(&expectedForeignDataWrapper, &resultForeignDataWrapper[0], "Oid") 966 }) 967 It("returns a slice of foreign data wrappers with options", func() { 968 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN DATA WRAPPER foreigndatawrapper OPTIONS (dbname 'testdb', debug 'true')") 969 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN DATA WRAPPER foreigndatawrapper") 970 971 expectedForeignDataWrapper := backup.ForeignDataWrapper{Oid: 0, Name: "foreigndatawrapper", Options: "dbname 'testdb', debug 'true'"} 972 973 resultForeignDataWrappers := backup.GetForeignDataWrappers(connectionPool) 974 975 Expect(resultForeignDataWrappers).To(HaveLen(1)) 976 structmatcher.ExpectStructsToMatchExcluding(&expectedForeignDataWrapper, &resultForeignDataWrappers[0], "Oid") 977 }) 978 }) 979 Describe("GetForeignServers", func() { 980 BeforeEach(func() { 981 testutils.SkipIfBefore6(connectionPool) 982 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN DATA WRAPPER foreigndatawrapper") 983 }) 984 AfterEach(func() { 985 testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN DATA WRAPPER foreigndatawrapper CASCADE") 986 }) 987 It("returns a slice of foreign servers", func() { 988 testhelper.AssertQueryRuns(connectionPool, "CREATE SERVER foreignserver FOREIGN DATA WRAPPER foreigndatawrapper") 989 990 expectedServer := backup.ForeignServer{Oid: 1, Name: "foreignserver", ForeignDataWrapper: "foreigndatawrapper"} 991 992 resultServers := backup.GetForeignServers(connectionPool) 993 994 Expect(resultServers).To(HaveLen(1)) 995 structmatcher.ExpectStructsToMatchExcluding(&expectedServer, &resultServers[0], "Oid") 996 }) 997 It("returns a slice of foreign servers with a type and version", func() { 998 testhelper.AssertQueryRuns(connectionPool, "CREATE SERVER foreignserver TYPE 'mytype' VERSION 'myversion' FOREIGN DATA WRAPPER foreigndatawrapper") 999 1000 expectedServer := backup.ForeignServer{Oid: 1, Name: "foreignserver", Type: "mytype", Version: "myversion", ForeignDataWrapper: "foreigndatawrapper"} 1001 1002 resultServers := backup.GetForeignServers(connectionPool) 1003 1004 Expect(resultServers).To(HaveLen(1)) 1005 structmatcher.ExpectStructsToMatchExcluding(&expectedServer, &resultServers[0], "Oid") 1006 }) 1007 It("returns a slice of foreign servers with options", func() { 1008 testhelper.AssertQueryRuns(connectionPool, "CREATE SERVER foreignserver FOREIGN DATA WRAPPER foreigndatawrapper OPTIONS (dbname 'testdb', host 'localhost')") 1009 1010 expectedServer := backup.ForeignServer{Oid: 1, Name: "foreignserver", ForeignDataWrapper: "foreigndatawrapper", Options: "dbname 'testdb', host 'localhost'"} 1011 1012 resultServers := backup.GetForeignServers(connectionPool) 1013 1014 Expect(resultServers).To(HaveLen(1)) 1015 structmatcher.ExpectStructsToMatchExcluding(&expectedServer, &resultServers[0], "Oid") 1016 }) 1017 }) 1018 Describe("GetUserMappings", func() { 1019 BeforeEach(func() { 1020 testutils.SkipIfBefore6(connectionPool) 1021 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN DATA WRAPPER foreigndatawrapper") 1022 testhelper.AssertQueryRuns(connectionPool, "CREATE SERVER foreignserver FOREIGN DATA WRAPPER foreigndatawrapper") 1023 }) 1024 AfterEach(func() { 1025 testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN DATA WRAPPER foreigndatawrapper CASCADE") 1026 }) 1027 It("returns a slice of user mappings", func() { 1028 testhelper.AssertQueryRuns(connectionPool, "CREATE USER MAPPING FOR testrole SERVER foreignserver") 1029 1030 expectedMapping := backup.UserMapping{Oid: 1, User: "testrole", Server: "foreignserver"} 1031 1032 resultMappings := backup.GetUserMappings(connectionPool) 1033 1034 Expect(resultMappings).To(HaveLen(1)) 1035 structmatcher.ExpectStructsToMatchExcluding(&expectedMapping, &resultMappings[0], "Oid") 1036 }) 1037 It("returns a slice of user mappings with options", func() { 1038 testhelper.AssertQueryRuns(connectionPool, "CREATE USER MAPPING FOR public SERVER foreignserver OPTIONS (dbname 'testdb', host 'localhost')") 1039 1040 expectedMapping := backup.UserMapping{Oid: 1, User: "public", Server: "foreignserver", Options: "dbname 'testdb', host 'localhost'"} 1041 1042 resultMappings := backup.GetUserMappings(connectionPool) 1043 1044 Expect(resultMappings).To(HaveLen(1)) 1045 structmatcher.ExpectStructsToMatchExcluding(&expectedMapping, &resultMappings[0], "Oid") 1046 }) 1047 It("returns a slice of user mappings in sorted order", func() { 1048 testhelper.AssertQueryRuns(connectionPool, "CREATE USER MAPPING FOR testrole SERVER foreignserver") 1049 testhelper.AssertQueryRuns(connectionPool, "CREATE USER MAPPING FOR anothertestrole SERVER foreignserver") 1050 1051 expectedMapping := []backup.UserMapping{ 1052 {Oid: 1, User: "anothertestrole", Server: "foreignserver"}, 1053 {Oid: 1, User: "testrole", Server: "foreignserver"}, 1054 } 1055 1056 resultMappings := backup.GetUserMappings(connectionPool) 1057 1058 Expect(resultMappings).To(HaveLen(2)) 1059 for idx := range expectedMapping { 1060 structmatcher.ExpectStructsToMatchExcluding(&expectedMapping[idx], &resultMappings[idx], "Oid") 1061 } 1062 }) 1063 }) 1064 })