Postgresql – plpgsql function to replace ‘ ‘ with null after insert using a trigger

plpgsqlpostgresql

I have a table in PostgreSQL 9.5 on which insert statements are run frequently. After an insert statement was ran on the table there are ' ' (single quote, space, single quote) values when no data was inserted for a specific column.
I want to run a trigger function after each insert to replace the ' ' with Null. However, I am struggling with writing the function.

What I have thus far:

CREATE OR REPLACE FUNCTION delete_space() RETURNS TRIGGER AS $_$
BEGIN
UPDATE proj.general
SET deliv1 = Null,
deliv2 = Null,
fmt = Null,
spec_instr = Null
where general.deliv1 = ''
or general.deliv2 = ''
or general.fmt = ''
or general.spec_instr = '';
RETURN NULL;
END $_$ LANGUAGE 'plpgsql';

This clears the ' ' but it also clears any new input in the columns specified in the function. As an example, if 'this is special instructions' is inserted into column general.spec_instr, the input is cleared after the function ran. How do I rectify this?
Also, is there a way to check all columns of new inserts for ' ' without having to specify the specific column name as in the function above?

EDIT:

CREATE TRIGGER del_spaces_trig
  AFTER INSERT
  ON projects.general
  FOR EACH STATEMENT
  EXECUTE PROCEDURE public.delete_space();

Best Answer

Just modify your SET clause and use CASE ... WHEN ... END:

CREATE OR REPLACE FUNCTION delete_space() RETURNS TRIGGER AS $_$
BEGIN
UPDATE proj.general
SET deliv1 = case when deliv1 ='' then NULL else deliv1 end,
SET deliv1 = case when deliv2 ='' then NULL else deliv2 end,
SET deliv1 = case when fmt ='' then NULL else fmt end,
SET deliv1 = case when spec_instr ='' then NULL else spec_instr end
where general.deliv1 = ''
or general.deliv2 = ''
or general.fmt = ''
or general.spec_instr = '';
RETURN NULL;
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 – is it possible to use a plpgsql function in a bash script

As you were hinted in comments above, your RBAR approach might be very inefficient. Consider the suggestions there.

Also, I am not going into details about the different approaches of UPSERT, as it is a very broad topic, especially when one wants to do concurrency-safe. PostgreSQL 9.5 helps a lot in this regard.

So, to your actual question: it is not directly possible writing PL/pgSQL 'commands' interspersed with bash, but the task is definitely solvable.

For this, you need to define a function like this (left out the complexity to show my point more concisely):

CREATE OR REPLACE FUNCTION dynamic_upsert(
    table_to_upsert name, 
    key_column name, 
    value_column name, 
    key integer, 
    value text
)
RETURNS void
LANGUAGE plpgsql
AS $BODY$
BEGIN
    ...
    EXECUTE $$ UPDATE $$ || quote_ident(table_to_upsert) || 
            $$ SET $$ || quote_ident(value_column) || $$ = $2 
               WHERE $$ || quote_ident(key_column) || $$  = $1 $$ 
        USING key, value;
    ...
END;
$BODY$;

Please note that this is fully dynamic in that you can choose any table, match against any column of it and modify any (other) column. The type of the key (integer) and the value (text) are fixed, though.

Now you can call this from a shell script like

psql -h $DBHOST -U $DBUSER -d $DBNAME \
     -c "SELECT dynamic_upsert($TABLE, $KEY_COL, $VAL_COL, $key, $value)"

If you are still RBARing, this will produce an additional overhead of connecting to the database for every single change, so be patient (or set up a connection pool).

Notes:

using concatenation, one has to be absolutely sure to quote the object names properly. This is here done using quote_ident(), which is the way to go if you are concatenating the dynamic statement. In newer versions (from 9.1), one would use the format() function to avoid the mess:

EXECUTE format($$ UPDATE %I SET %I = $2 WHERE %I = $1$$, 
               table_to_upsert, value_column, key_column) 
USING key, value;

here I am using dollar quoting when building the dynamic query. This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example). This way most editors will highlight the statements nicely.

Best Answer

Related Solutions

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

PostgreSQL – is it possible to use a plpgsql function in a bash script

Related Question