CODEWITHSUNDEEP
postgresqlfor-looptriggersplpgsql
Vantalk
Vantalk

Reputation: 376

Accessing columns of a record programatically in plpgsql

I am trying to log changes, if any were made to a table, but I am stuck when trying to loop through column names. I am receiving a "array value must start with "{" ... line 6 at FOR over SELECT rows" error. I do not understand why this is happening.. The function compiles ok but running an update gives that error.

CREATE TABLE test(x varchar(50))

CREATE OR REPLACE FUNCTION testF()
RETURNS trigger
AS $$
DECLARE 
 col varchar[255]; //don't know if this is the right variable type to use
BEGIN
  IF OLD.* IS DISTINCT FROM NEW.* THEN
    FOR col in SELECT column_name FROM information_schema.columns WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME LOOP
      INSERT INTO test(x) VALUES(col||'oldValue:'||OLD.col||'newValue:'||NEW.col); //I want to put the name and the old and new values in a varchar field
    END LOOP;
END IF;
RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER testT AFTER UPDATE
ON "triggerTable" FOR EACH ROW EXECUTE PROCEDURE testF();

Upvotes: 1

Views: 180

Answers (1)

Jasen
Jasen

Reputation: 12432

to get at the columns of OLD or NEW by name you'll have to use exec an a bunch of typecasts.

something like this:

execute '('||quote_literal(NEW::text)||'::'||quote_ident(pg_typeof(NEW))||
  ').'||quote_ident(col)||'::text';

this may get you imprecise values for some floats

Upvotes: 1

Related Questions