Menelaos
Menelaos

Reputation: 25755

It is possible to reference another SQL file from SQL script

Basically I want to execute an SQL file from an SQL file in Postgres.

Similar question for mysql: is it possible to call a sql script from a stored procedure in another sql script?

Why?

Because I have 2 data files in a project and I want to have one line that can be commented/un-commented that loads the second file.

Clarification: I want to call B.SQL from A.SQL

Clarification2: This is for a Spring Project that uses hibernate to create the database from the initial SQL file (A.SQL).

On further reflection it seems I may have to handle this from java/string/hibernate.

Below is the configuration file:

spring.datasource.url=jdbc:postgresql://localhost:5432/dbname
spring.datasource.username=postgres
spring.datasource.password=root
spring.datasource.driver-class-name=org.postgresql.Driver

spring.datasource.data=classpath:db/migration/postgres/data.sql
spring.jpa.hibernate.ddl-auto=create

Upvotes: 12

Views: 10490

Answers (2)

Tobias Liefke
Tobias Liefke

Reputation: 9022

Hibernate is just:

  1. reading all your SQL files line per line
  2. strip any comment (lines starting with --, // or /*)
  3. removes any ; at the end
  4. executes the result as a single statement

(see SchemaExport.importScript and SingleLineSqlCommandExtractor)

There is no support for an include here.

What you can do:

  • Define your own ImportSqlCommandExtractor which knows how to include a file - you can set that extractor with hibernate.hbm2ddl.import_files_sql_extractor=(fully qualified class name)
  • Define your optional file as additional import file with hibernate.hbm2ddl.import_files=prefix.sql,optional.sql,postfix.sql, you can either add and remove the file reference as you like, or you can even exclude the file from your artifact - a missing file will only create a debug message.
  • Create an Integrator which sets the hibernate.hbm2ddl.import_files property dynamically - depending on some environment property

Upvotes: 1

Tom-db
Tom-db

Reputation: 6868

Import of other files is not supported in Sql, but if you execute the script with psql can you use the \i syntax:

SELECT * FROM table_1;
\i other_script.sql
SELECT * FROM table_2;

This will probably not work if you execute the sql with other clients than psql.

Upvotes: 9

Related Questions