0

Having a PostgreSQL DB model defined in SQL, i would like to generate an org-mode file with a bunch of org mode tables to document it.
So for each CREATE TABLE, it creates an org-mode table with its name and fields:

CREATE TABLE film_actor ( actor_id integer NOT NULL, film_id integer NOT NULL, last_update timestamp without time zone DEFAULT now() NOT NULL ); CREATE TABLE film_category ( film_id integer NOT NULL, category_id integer NOT NULL, last_update timestamp without time zone DEFAULT now() NOT NULL ); ...

Something like:

| film_actor | type | other | | -------- | ---------| --- | | actor_id | integer | NOT NULL | | film_id | integer | NOT NULL | | last_update | timestamp | without time zone DEFAULT now() NOT NULL | | film_category | type | other | | -------- | ---------| --- | | film_id | integer | NOT NULL | | category_id | integer | NOT NULL | | last_update | timestamp | without time zone DEFAULT now() NOT NULL | ...

Is there already some library that does something similar to this?
If not, any hints or strategy how to do the parsing?
My ELisp skills are kind of basic, so any hint will help.

Improve this question
2
  • 1
    If you only have these table definitions and have not created the tables, then this question is completely unrelated to Org, IMO. Commented Dec 11, 2023 at 15:31
  • There are more table definitions and tables have been created in postgresql. You can think of having a schema like this: github.com/jOOQ/sakila/blob/main/postgres-sakila-db/… Commented Dec 11, 2023 at 23:56

1 Answer 1

Reset to default
3

If I understand the question correctly, it is more about PostgreSQL rather than Org Mode. But anyway, to do this job you indeed need to know some about Org Babel to get things configured correctly.

I tested the following code on my machine (MS-Windows; if you're using GNU/Linux it should be easier):

#+BEGIN_SRC sql SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'lowercase_table_name'; #+END_SRC Local Variables: eval: (require 'ob-sql) sql-postgres-program: "/Path/to/Your/psql" org-babel-default-header-args:sql: ((:engine . "postgresql") (:dbhost . "localhost") (:dbport . 5432) (:dbuser . "postgres") (:database . "database_name")) End:

demo here

If you want to use PostgreSQL in Org Babel on MS-Windows, you MUST modify the definition of org-babel-execute:sql and then recompile the file ob-sql.el:

change

(defun org-babel-execute:SQL ... ... ((postgresql postgres) (format "%s%s --set=\"ON_ERROR_STOP=1\" %s -A -P \ footer=off -F \"\t\" %s -f %s -o %s %s" (if dbpassword (format "PGPASSWORD=%s " dbpassword) "") ...)

to

(defun org-babel-execute:SQL ... ... ((postgresql postgres) (format "%s --set=\"ON_ERROR_STOP=1\" %s -A -P \ footer=off -F \"\t\" %s -f %s -o %s %s" ...)

You also need to set your PGPASSWORD EXPLICITLY:

add

eval: (setenv "PGPASSWORD" "Write your password here.")

to your Org file's local variables list.

Improve this answer
17
  • 1
    @shynur's taking advantage of psql to parse the table format is ingenious and probably the best way to go about it. You can parametrize the query for different DBs and use the appropriate command-line program for each. But if you are intent on using your own program to do the parsing, you don't have to do it in elisp: you can e.g. write a python (or C, or C++, or Rust, or Ruby or ....) program to do the parsing: you can either put the program in a Babel src block or you can use a shell block to invoke it. So use whatever language you are comfortable with. Commented Dec 12, 2023 at 8:51
  • 1
    "Alternatively, is there a way that I can stop the animation?" - or maybe have a way to manually run it once. It's the looping that's driving me loopy :-( Commented Dec 12, 2023 at 8:53
  • 1
    @NickD Okay, I will avoid directly displaying GIFs and instead use links as substitutes. Commented Dec 12, 2023 at 10:45
  • 1
    @nephewtom I updated my answer. It may help if you are using MS-Windows. Commented Dec 21, 2023 at 7:04
  • 1
    @nephewtom: see org-confirm-babel-evaluate Commented Jan 10, 2024 at 12:50

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.