Adding a list of items into a PostgreSQL database using SQL syntax

Question

Adding a list of items into a PostgreSQL database using SQL syntax

I need to perform multiple table inserts, where the last table relies on the one before it and includes an additional list of items. I am working with pg in conjunction with node.js and JavaScript.

Table 1:

insert into col1, col2 values (1, 2) return col1 as table1_id

Table 2:

insert into col1 col2 values (table1_id, val1), (table1_id, val2), ... (table1_id, val_n)

I have observed examples where this has been done with just a single row in the final table. In my case, however, I will have a list of items from val1 to valn that need to be associated with the ID generated from the initial insert in Table 1 and then added to a pre-existing list. The goal is to create a relationship table containing a range of items tied to a record in Table 1.

If anyone has any insights or advice, it would be greatly appreciated.

Thank you

javascript sql postgresql

Answer 1

Answer №1

Consider creating a 'dao' layer within your postgres database. This function can take all necessary parameters and return the data needed for frontend display.

By implementing this postgres function, you can efficiently insert all desired rows into the tables.

Here is an example of how you can handle this:

CREATE OR REPLACE FUNCTION function_what_i_want(val1 integer, val2 integer, my_argument integer[])
RETURNS my_schema_and_table[] AS
$BODY$
DECLARE
result my_schema_and_table[];
BEGIN
insert into table1(col1,col2,col3) VALUES (nextval('seq1'),val1, val2);
insert into table2(c1,c2) 
    select currval('seq1'),m.c2
    from unnest(my_argument) m(c2);
END;

If accessing these tables becomes cumbersome in your project, consider storing the lists as json to simplify the process.

Answer 2

Consider creating a 'dao' layer within your postgres database. This function can take all necessary parameters and return the data needed for frontend display.

By implementing this postgres function, you can efficiently insert all desired rows into the tables.

Here is an example of how you can handle this:

CREATE OR REPLACE FUNCTION function_what_i_want(val1 integer, val2 integer, my_argument integer[])
RETURNS my_schema_and_table[] AS
$BODY$
DECLARE
result my_schema_and_table[];
BEGIN
insert into table1(col1,col2,col3) VALUES (nextval('seq1'),val1, val2);
insert into table2(c1,c2) 
    select currval('seq1'),m.c2
    from unnest(my_argument) m(c2);
END;

If accessing these tables becomes cumbersome in your project, consider storing the lists as json to simplify the process.

Adding a list of items into a PostgreSQL database using SQL syntax

Answer №1

Similar questions

Demonstrate how to pass a newline character from Ruby on Rails to JavaScript

Retrieve the base64 encoded data from localStorage and showcase it within a div element

Experiencing delays when loading more than 200 input fields on the screen due to

Improving React efficiency: What techniques can be used to prevent the entire component from re-rendering every time a prop changes?

Calculate a range while meeting a specific condition using JavaScript

Execute a jQuery function only after a form has been submitted for the first time

Finding the starting point coordinates of a background image that has been adjusted using CSS

Server-based WebRTC video streaming

The issue of SQLCMD truncating row width during CSV file export

Looking for a unique search object specifically designed for mongodb?

Using Fabric.js to manage image controls situated beneath another overlay image

Navigating websites: Clicking buttons and analyzing content

unique list of values separated by commas resulting from a switch statement

Here is a method for retrieving all fields within embedded documents at the same level in MongoDB

What is the best way to create a deep clone of an XMLDocument Object using Javascript?

The JavaScript object is unable to reach the PHP controller

Django: A guide to filtering by DateTimeField while disregarding differences in seconds and milliseconds

Is there a way to execute v-for once the created() lifecycle hook has finished running?

Tips for extracting parameters from a URL using Express JS in a custom manner

Converting a string within the range of 31-40 to an integer using parseInt in