It is possible to reference another SQL file from SQL script

Question

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

@Tommaso Di Bucchianico I want to call one sql file from another file. — Μenelaοs
– Μenelaοs, Commented Oct 14, 2015 at 8:11

Tom-db · Accepted Answer · 2015-10-14 08:19:15Z

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.

edited Oct 14, 2015 at 8:19

answered Oct 14, 2015 at 8:13

Tom-db

6,9584 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom-db Over a year ago

Yeah, you still don't say how do you want to execute the script

Μenelaοs Over a year ago

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.

Tobias Liefke · Accepted Answer · 2015-10-14 09:21:30Z

Hibernate is just:

reading all your SQL files line per line
strip any comment (lines starting with --, // or /*)
removes any ; at the end
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

Collectives™ on Stack Overflow

It is possible to reference another SQL file from SQL script

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related