Dynamically update NEW columns in plpgsql trigger

From: Nick <nboutelier(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Dynamically update NEW columns in plpgsql trigger
Date: 2010-09-09 04:12:11
Message-ID: bc7f5c9c-b87c-4c57-b6b7-0801d90d96d5@s24g2000pri.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I need to dynamically update NEW columns. Ive been inserting the NEW
values into a temp table, updating them, then passing the temp table
values back to NEW (is there a better way?). Ive had success with this
method unless there is a null value...

EXECUTE 'CREATE TEMP TABLE new AS SELECT $1.*' USING NEW;
EXECUTE 'UPDATE new SET ' || dyn_col_name || ' = 1';
EXECUTE 'SELECT * FROM new' INTO NEW;
EXECUTE 'DROP TABLE new';

This last line...
EXECUTE 'SELECT $1.' || dyn_col_name INTO val USING NEW;

gives the ERROR: could not identify column "col_one" in record data
type.

However RAISE EXCEPTION '%',NEW.col_one;
returns "1" correctly.

If col_one does does not start out as a null value, then everything
works. Why does the passing from temp table back to NEW lose the USING
functionality?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2010-09-09 05:05:42 line CASE statemelnt in query a la Oracle
Previous Message Craig Ringer 2010-09-09 03:32:37 Re: postgresql cluster on SAN