Re: Creating files with testdata - Mailing list pgsql-general

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

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

Imagine a very simple Wikipedia-clone. You have a couple of DB tables for article subjects in a couple of levels, a
tablefor body text and a couple of different tables for references and for images related to the article in question.
Referencesand images are not referenced inline in the body text but at the end. 

Everything is generated manually, ie the body text is written, various references added, and a suitable subject area in
differentlevels chosen, all by an author. 

I want to be able to write this "article", with the appropriate references, images etc to be done outside this database
application.My vision is it's done in a markdown document where a strict use of heading levels, bullet lists etc would
correlatewith subject areas, body text, references, images and what-nots. 

A script of some kind that can parse markdown then generates the necessary SQL statements to insert all of this in the
appropriatetables, all according to how the information was laid out in the markdown document. 

My initial thought was to use awk but it's (awk)ward since it is strictly line/paragraph oriented. Pandoc parses
markdownvery well, generates an AST internally and then the output filter should be able to generate complex SQL
statementssuch as CTEs. 

Hope that might shed some more light on my thought process.