I have a table with 3 different partitions that is split on the point_of_sale
column. I have constructed a view (massive_table
) to aggregate data from these partitions like so:
CREATE VIEW massive_table as
SELECT * FROM massive_table_UK
UNION ALL
SELECT * FROM massive_table_US
UNION ALL
SELECT * FROM massive_table_DE;
The massive_table_UK/US/DE
tables each have a check constraint imposed on them so that the point_of_sale
column can only contain a string value of either UK
, US
or DE
as appropriate.
Before partitioning it, I'd prepare the table for new data by doing the following:
DELETE FROM massive_table WHERE point_of_sale = 'UK';
What I'd prefer to, however, is to truncate the appropriate partition. Is it possible to do this dynamically? I've looked at the PostgreSQL PL/pgSQL documentation and it says that
Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.
I'm rather new to PostgreSQL programming and I'm struggling to comprehend that sentence; What I'd like to be able to do is have a function that would truncate the appropriate partition based on a given point_of_sale
value. Is that possible?