Function/procedure to save a schema as an .sql file in PostgreSQL

Question

I want to create a procedure which when I call creates a backup by creating an .sql file and saves it in my computer.

The procedure's name is trial_gen(). When I execute call trial_gen(), it should create a plain .sql file of the schema.

All solutions I found were only using the SQL shell

Do you want to create the procedure by creating a .sql file, or do you want to create the backup as a .sql file (i.e. a database dump)? Your question is ambiguous, please edit it to clarify. — Bergi
– Bergi, Commented Jan 2, 2023 at 12:53
How do you want to call the procedure? By "saves it in my computer", do you refer to the same computer that the database is running on? — Bergi
– Bergi, Commented Jan 2, 2023 at 12:54
Why not just pg_dump, why do you want this to work as a query in pgAdmin? It's possible to get DDL statements for everything from system tables and spit it out through COPY into an .sql file on your drive (assuming it's a local database), but you'll either have to settle for a subset of your relations, or re-invent a slower, pure-SQL pg_dump. pgAdmin already offers a backup and restore dialog, that's basically a GUI for pg_dump. — Zegarek
– Zegarek, Commented Jan 2, 2023 at 12:58

Gergő Battyányi · Accepted Answer · 2023-01-02 12:36:31Z

0

SQL code is a script, so I think it makes sense to run one from SQL shell. It would be a stored script (text) in a file anyway.

answered Jan 2, 2023 at 12:36

Gergő Battyányi

1

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

2 Comments

Bergi Over a year ago

I don't think that's what the OP meant, but let's wait for them to clarify

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Function/procedure to save a schema as an .sql file in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related