Insert binaries into a PostgreSQL table as “bytea” data type

In older versions of PostgreSQL a native function to insert binaries as “bytea” data type is missing, so:

create or replace function bytea_import(p_path text, p_result out bytea)
 language plpgsql as $$
declare
 l_oid oid;
 r record;
begin
 p_result := '';
 select lo_import(p_path) into l_oid;
 for r in ( select data
 from pg_largeobject
 where loid = l_oid
 order by pageno ) loop
 p_result = p_result || r.data;
 end loop;
 perform lo_unlink(l_oid);
end;$$;

then (example of a case where a picture file name is already available in another column):

 UPDATE table SET bytea_column_name = bytea_import('/path/to/photo/' || "column_with_picture_name");
Advertisements
Insert binaries into a PostgreSQL table as “bytea” data type