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.

2
  • 1
    If you only have these table definitions and have not created the tables, then this question is completely unrelated to Org, IMO.
    – shynur
    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/…
    – nephewtom
    Dec 11, 2023 at 23:56

1 Answer 1

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.

18
  • 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.
    – NickD
    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 :-(
    – NickD
    Dec 12, 2023 at 8:53
  • 1
    @NickD Okay, I will avoid directly displaying GIFs and instead use links as substitutes.
    – shynur
    Dec 12, 2023 at 10:45
  • 1
    @nephewtom I updated my answer. It may help if you are using MS-Windows.
    – shynur
    Dec 21, 2023 at 7:04
  • 1
    @nephewtom: see org-confirm-babel-evaluate
    – shynur
    Jan 10 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.