11

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
Community
  • 1
  • 1
Menelaos
  • 23,508
  • 18
  • 90
  • 155

2 Answers2

9

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.

Tom-db
  • 6,528
  • 3
  • 30
  • 44
  • Yeah, you still don't say how do you want to execute the script – Tom-db Oct 14 '15 at 08:16
  • It's run through hibernate in a Java Spring Project. Your right, I didn't. I think that maybe I need to handle this in hibernate/java. – Menelaos Oct 14 '15 at 08:18
1

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
Tobias Liefke
  • 8,637
  • 2
  • 41
  • 58