CODEWITHSUNDEEP
javapostgresqlfunctionstored-procedurestruncate
Yam Bakshi
Yam Bakshi

Reputation: 147

Truncating table before insert in postgres

I'm trying to create a mechanism that will truncate tables every time an insert command is called on them.

First I tried creating a function that gets an array of rows and truncates the table before inserting them:

CREATE OR REPLACE FUNCTION insert_customers(customers customer[])
    RETURNS VOID AS 
$$  
BEGIN
    TRUNCATE TABLE Customers;
    INSERT INTO Customers (CustomerID, CustomerName) SELECT * FROM UNNEST(customers::Customer[]);
END
$$
LANGUAGE plpgsql;

Where Customer is a user-defined type representing a row in the Customers table:

CREATE TYPE Customer AS (
  customerid VARCHAR(100),
  customername VARCHAR(100));

That worked when I called the function from psql:

SELECT * FROM insert_customers(ARRAY[ROW('ID1', 'Name1'),ROW('ID2', 'Name2')]::Customer[]);

But I haven't found a way to perform this insert from my java code:

String[][] customers = new String[2][];
customers[0] = new String[] { "ID1", "Name1" };
customers[1] = new String[] { "ID2", "Name2" };    

Array sqlArray = connection.createArrayOf("customer", customers);    

CallableStatement cstmt = connection.prepareCall("{ call insert_customers(?) }");
cstmt.setArray(1, sqlArray);
cstmt.execute();

Which created the following statement:

select * from insert_customers ('{{"ID1", "Name1"},{"ID2", "Name2"}}') as result

And threw the following exception:

org.postgresql.util.PSQLException: ERROR: malformed record literal: "ID1"\n Detail: Missing left parenthesis.

Then I tried creating a trigger that will truncate the table on insert event:

CREATE TRIGGER on_insert_customers
    BEFORE INSERT ON Customers
    EXECUTE PROCEDURE truncate_customers();

Where truncate_customers is:

CREATE OR REPLACE FUNCTION truncate_customers()
    RETURNS TRIGGER AS 
$$  
BEGIN
    TRUNCATE TABLE Customers;   
END
$$
LANGUAGE plpgsql;

And that threw the exception:

cannot TRUNCATE "customers" because it is being used by active queries in this session

Any Ideas???

Upvotes: 3

Views: 3924

Answers (1)

klin
klin

Reputation: 121764

Modify the function

The query unnest pairs of elements of a text array:

select cols[1], cols[2]
from (
    select (ordinality- 1)/2, array_agg(unnest) cols
    from unnest('{"id1", "name1", "id2", "name2"}'::text[]) with ordinality
    group by 1
    ) s

 cols | cols  
------+-------
 id1  | name1
 id2  | name2
(2 rows)

Use it in your function so it can have a text array as a parameter:

create or replace function insert_customers(text[])
returns void language plpgsql as $$  
begin
    truncate table customers;
    insert into customers (customerid, customername)
    select cols[1], cols[2]
    from (
        select (ordinality- 1)/2, array_agg(unnest) cols
        from unnest($1) with ordinality
        group by 1
        ) s;
end $$;

select insert_customers('{"id1", "name1", "id2", "name2"}');

Modify the trigger function

You cannot truncate the table in a trigger, but you can delete all rows:

...
    delete from Customers;
    return new;
...

Upvotes: 2

Related Questions