How to copy bytea in postgres trigger?

Question

Anyone who can help me how to insert bytea from another table in postgres trigger?

CREATE OR REPLACE FUNCTION public.image_insert_trigger()
    RETURNS trigger AS
$BODY$DECLARE
    dyn_sql text;
    tbname text;
    img text;
BEGIN
img:=NEW.img;
tbname:='t'||left(NEW.code,4);
dyn_sql:='INSERT INTO '||tbname||' (id,code,img) VALUES ('||NEW.id||','||NEW.code||','||''''||img::bytea||''''||')';    
execute dyn_sql;
return NULL;
END;$BODY$ 
LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION public.image_insert_trigger() OWNER TO postgres;

user330315 · Accepted Answer · 2017-08-21 05:31:21Z

2

Don't use string literals, use parameters

begin
  tbname :='t'||left(NEW.code,4);
  dyn_sql := 'INSERT INTO '||quote_ident(tbname)||' (id,code,img) VALUES ($1, $2, $3)';
  execute dyn_sql 
     using new.id, new.code, new.img;
  return NULL;
END;

I prefer the format() function to define dynamic SQL as it makes the actual SQL easier to read (at least for me)

dyn_sql := format('INSERT INTO %I (id,code,img) VALUES ($1, $2, $3)', tbname);

answered Aug 21, 2017 at 5:31

user330315

Sign up to request clarification or add additional context in comments.

Comments

clemens · Accepted Answer · 2017-08-21 05:32:33Z

1

It should be much easier to pass the parameters not inline:

dyn_sql:='INSERT INTO '||tbname||' (id,code,img) VALUES ($1,$2,$3)';    
execute dyn_sql using NEW.id, NEW.code, img::bytea;

answered Aug 21, 2017 at 5:32

clemens

17.9k12 gold badges52 silver badges71 bronze badges

Collectives™ on Stack Overflow

How to copy bytea in postgres trigger?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related