Postgresql – Column to be populated using other columns values

postgresql

I would like to add a new column in my table using SQL code in PostgreSQL / PostGIS, the values in this column must be filled from the other eight columns and updated with a trigger as I insert data to a new line for the first rows. As I show in the attached image.

I have used this code to alter the table,

ALTER TABLE table_name ADD COLUMN NEW_COLUMN varchar;

UPDATE table_name SET NEW_COLUMN = "PP" || '.' || "MM" || '.' || "DD" || '.' || "BB" || '.' || "QQ" || '.' || "LL" || '.' || "ID"::varchar || '.' || "UU";

But I am having a problem using this trigger because it does not accept:

CREATE OR REPLACE FUNCTION update_en() 
RETURNS TRIGGER AS 
$$ 
BEGIN 
NEW.codigo :=; 
RETURN NEW; 
END; 
$$ 
LANGUAGE plpgsql; 
CREATE TRIGGER update_en 
BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4" 
FOR EACH ROW 
EXECUTE PROCEDURE update_en();

Best Answer

The question on why you think you need that aside, you can simplify the concatenation using concat_ws()which you can easily use in the trigger as well:

CREATE OR REPLACE FUNCTION update_en() 
RETURNS TRIGGER AS 
$$ 
BEGIN 
  NEW.codigo := concat_ws('.', new."PP", new."MM", new."DD", new."BB", new."QQ", new."LL", new."ID", new."UU");
  RETURN NEW; 
END; 
$$ 
LANGUAGE plpgsql;

Related Solutions

Postgresql – Plpgsql seems to be deleting and inserting instead of updating – Why

I don't have 7.4 to test on, but I'm guessing:

every time you do a vacuum full the table compacts
every time you update, the new version of the row (see MVCC) gets shoved at the end of the heap before the old one is removed by a vacuum

See here for the docs explaining this in more detail, but the simple solution is not to run vacuum full at all - just vacuum. Then your table will probably settle into a steady state where 'holes' in the data are left and can be used by later updates.

As for "insert time", I'm surprised at your results. My expectation would be that insert time would be slower after a vacuum full - but if all the blocks are in the cache, the overhead of finding free space inside the current block might be higher than adding the new row at the end of the heap even if the number of blocks accessed is higher

Postgresql – Inherit audit columns and triggers

From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

Best Answer

Related Solutions

Postgresql – Plpgsql seems to be deleting and inserting instead of updating – Why

Postgresql – Inherit audit columns and triggers

Related Question