CODEWITHSUNDEEP
postgresqlloopssql-updateforeign-keys
Faseela Thayattuchira
Faseela Thayattuchira

Reputation: 527

Accidentally detected all data from a table, insert dummy data to table using loop psql

I had accidentally deleted most of the rows in my Postgres table (data is not important its in my test environment, but I need a dummy data to be insert in to these table).

Let us take three tables,

  1. MAIN_TABLE(main_table_id, main_fields)
  2. ADDRESS_TABLE(address_table_id, main_table_id, address_type, other_fielsds)
  3. CHAID_TABLE(chaid_table_id,main_table_id, shipping_address_id, chaild_fields)

I had accidentally deleted most of the data from ADDRESS_TABLE.

  1. ADDRESS_TABLE has a foreign key from MAIN_TABLE ,i.e. main_table_id. for each row in MAIN_TABLE there is two entries in ADDRESS_TABLE, in which one entry is its address_type is "billing/default" and other entry is for address_type "shipping".

  2. CHAID_TABLE has two foreign keys one from MAIN_TABLE, i.e. main_table_id and other from ADDRESS_TABLE i.e., shipping_address_id. this shipping_address_id is address id of ADDRESS_TABLE, its address_type is shipping and ADDRESS_TABLE.main_table_id = CHAID_TABLE.main_table_id.

These are the things that I needed.

  1. I need to create two dummy address entries for each raw in MAIN_TABLE one is of address type "billing/default" and other is of type "shipping".
  2. I need to insert address_table_id to the CHAID_TABLE whose ADDRESS_TABLE.main_table_id = CHAID_TABLE.main_table_id. and addres_type ="shipping"

if first is done I know how to insert second, because it is a simple insert query. I guess. it can be done like,

UPDATE CHAID_TABLE 
  SET shipping_address_id = ADDRESS_TABLE.address_table_id 
FROM ADDRESS_TABLE  
WHERE ADDRESS_TABLE.main_table_id = CHAID_TABLE.main_table_id 
  AND ADDRESS_TABLE.addres_type ='shipping';

for doing first one i can use loop in psql, ie loop through all the entries in MAIN_TABLE and insert two dummy rows for each rows. But I don't know how to do these please help me to solve this.

Upvotes: 1

Views: 115

Answers (1)

ProgramLover
ProgramLover

Reputation: 36

I hope your solution is this, Create a function that loop through all the rows in MAIN_TABLE, inside the loop do the action you want, here two insert statement, one issue of this solution is you have same data in all address.

CREATE OR REPLACE FUNCTION get_all_MAIN_TABLE () RETURNS SETOF MAIN_TABLE  AS
$BODY$
DECLARE
    r MAIN_TABLE %rowtype;
BEGIN
    FOR r IN
        SELECT * FROM MAIN_TABLE 
    LOOP
        -- can do some processing here
        INSERT INTO ADDRESS_TABLE ( main_table_id, address_type, other_fielsds) 
                      VALUES('shipping', r.main_table_id,'NAME','','other_fielsds');
         INSERT INTO ADDRESS_TABLE ( main_table_id, address_type, other_fielsds) 
                      VALUES('billing/default',r.main_table_id,'NAME','','other_fielsds'); 
                           
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_MAIN_TABLE ();

Upvotes: 2

Related Questions