Date: Mon, 10 Mar 2025 23:10:25 -0400
User-Agent: K-9 Mail for Android
In-Reply-To: <EEE7D9EB-CDFE-420F-9A84-A51E80EC644B@meddatainc.com>
References: <84A5765B-1D3C-485B-B852-99D63D53963E@meddatainc.com> <4835994a-aecf-4fef-bb75-9a4c5f6e0ffd@aklaver.com> <EEE7D9EB-CDFE-420F-9A84-A51E80EC644B@meddatainc.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=utf-8
Content-Transfer-Encoding: quoted-printable
Subject: Re: Creating files with testdata
To: pgsql-general@lists.postgresql.org
From: H <agents@meddatainc.com>
Message-ID: <D2691217-0F2B-439D-9A03-883702A357F8@meddatainc.com>
UI-OutboundReport: notjunk:1;M01:P0:+zPPuuj2AK4=;k/6kgx20ehOL3IQ3m70+6NjiimX
 vXSXsTXqzgYR0u+rbkEs5rKE5e5kweF3ruvWjM8K2GwdiwAdrPxBXEGOIeMLb/UOmLPledw44
 HRxHsbW8ZtIFoORER062+qaoAfT5J0FhQrJ5izcuorlFrUQrOanh4+olffO5NxQLWjB+9uM+u
 rUAciJzmAj92LMNsxwvBVo/ejrmIglwDRt17DT7esZWqxovH+3RZylqv8LC6SOnruvo5BDkPc
 9x2R5B/1ZjDnzgA9eRpFoHIOb3A6HzfTCDUn9/4QpDfT/8JjVtwZpGxZlBV+Efg2S001DMmxL
 g7ANBMeMnS5cJwiZigIzM0J0pLzDr6IjfhHcjhYROuCRgvoKUwVoKhgmatCQYQzAofoY6AvM0
 qs/ExI1DbgHKXW1WtSE2T1kxC+889rxGvlFd3tLxoQfMl++xpyMFbEuigF1/qlO1UelMs6+Eb
 9z2SSrCYJ71ADTen/aCKaPf52B1vKRo99V0JmxqQFTh/XBaxYl3rvQ9cEVwf3L667LY4uer0k
 TSPB1jUNxQ+J2Jcu4kQNOXNU5RdgewmhEVmjdP7NOk/dxHwu464MTQjKiYfWDrAyOu4Tq9rFc
 LFXLuLhdhWsdsB6EvV7KMUtQHeH6cDjXwNdu5oq8x1Er72+EKf7ByO2sq2otdGBxjHbe8Rvys
 MlD5yrBPrhKzuoGOoYnGqRDZMOY9peBrUenh6Xm2hiM+b4VNWOVLAuaOpWZYdfXanU3f5Sa+1
 9FDORtWGHRSRCIbligApvjuptAkEB0jGzvLQN/F+awkja0MEgIex32GUBc1x3vQ/8MctBP2tE
 Ng5hiWvS0NUyj9kW6AhsOi+8FemAv1P1rQHB6As/4VD7kabssxczaKbe4X8wNCvKCDI5AHi9f
 no4udl6C/f/JmFDeHvgz7oV+IP19gVwQrf/ghSJN5sImSQRmWFdDYyprCQ2EcDPtVIEMYjLSC
 JYCZhXe7HaLQPH9otlLbJPVTbN7U8y+Rl30zupEHUQPvmPJ2Qwflt//JP1TqLwuQiVVGTrEf7
 s6lMi0tFirgLS7oqyPxRIxDpg1nzP20JVmDEiUW5X0OCaAUrcQesuKWXjpfv2fEDvqt+a6hgk
 2TYC4tYLZ++uTjgS90zAUS1Dgb8GtifWfTrUpvKJzPt5usYMKAyIeWk2ZF1S0fqVmMsB0xEw+
 DU9ZDecoDYmjbOW9OHPKlP2BaYLRBDvb4vfVVhaa5OEshov9K3MdzWVPgOHP6goE0D4RURyi+
 RVTnQL+rtHy2MCIW9mYQQqyl6gyFQawfmqSNi8gij5v+yNdhBuMYQYH2fffPhQsNzlfliCfGZ
 my0wQPhFxtfgvkR3pjcHRmRC/545D/b6UGp9GOgfFLsVG9z+YsBmCDELeA9BNBhffzT
Archived-At: <https://www.postgresql.org/message-id/D2691217-0F2B-439D-9A03-883702A357F8%40meddatainc.com>
Precedence: bulk

On March 10, 2025 3:15:10 PM EDT, H <agents@meddatainc=2Ecom> wrote:
>On March 10, 2025 2:26:48 PM GMT-04:00, Adrian Klaver
><adrian=2Eklaver@aklaver=2Ecom> wrote:
>>
>>
>>On 3/10/25 11:09 AM, H wrote:
>>> I am developing a complex multi-tenant application in postgresql 16
>>in Linux=2E During the development I would like to be able to enter test
>>data into various related tables at any given time for testing
>>purposes=2E
>>>=20
>>> While this can certainly be accomplished by predefined CTE INSERT
>>statements in an SQL file, I would prefer something more flexible=2E I
>am
>>thinking of using a markdown file as a source for these CTE INSERT
>>statements=2E Specifically, having a certain structure in the markdown
>>file where a given markdown heading level, bullet level etc=2E would
>>correspond to specific tables and columns=2E
>>
>>Why CTE INSERTs?
>>
>>>=20
>>> After entering my test data into the markdown file for the given
>test
>>scenario, I would then run an awk script or similar to create a SQL
>>file with the various CTE INSERT statements=2E Howevever, it gets
>complex
>>since I need to handle 1:N relationships between tables in the
>markdown
>>file=2E=2E=2E
>>>=20
>>> I hope the above outline is understandable and am interested in
>>comments and thoughts on my above approach=2E
>>>=20
>>> Thanks=2E
>>>=20
>>>=20
>
>There are tables referencing each other using randomly generated IDs,
>ie=2E those IDs are not known until after the parent table row is
>inserted=2E

I thought I should give an example of why I need this=2E This is /not/ the=
 application but might be a useful example:

Imagine a very simple Wikipedia-clone=2E You have a couple of DB tables fo=
r article subjects in a couple of levels, a table for body text and a coupl=
e of different tables for references and for images related to the article =
in question=2E References and images are not referenced inline in the body =
text but at the end=2E

Everything is generated manually, ie the body text is written, various ref=
erences added, and a suitable subject area in different levels chosen, all =
by an author=2E

I want to be able to write this "article", with the appropriate references=
, images etc to be done outside this database application=2E My vision is i=
t's done in a markdown document where a strict use of heading levels, bulle=
t lists etc would correlate with subject areas, body text, references, imag=
es and what-nots=2E

A script of some kind that can parse markdown then generates the necessary=
 SQL statements to insert all of this in the appropriate tables, all accord=
ing to how the information was laid out in the markdown document=2E

My initial thought was to use awk but it's (awk)ward since it is strictly =
line/paragraph oriented=2E Pandoc parses markdown very well, generates an A=
ST internally and then the output filter should be able to generate complex=
 SQL statements such as CTEs=2E

Hope that might shed some more light on my thought process=2E