Getting the contents of a json file as a variable in plpgsql?

Question

I have a function based this post by Sean Huber in order to get the content of a file:

CREATE FUNCTION file_read(file text)     RETURNS void AS $$
DECLARE
  content text;
  tmp text;
BEGIN
  file := quote_literal(file);
  tmp := 'tmp_table';
EXECUTE 'CREATE TEMP TABLE ' || tmp || ' (content text)';
  EXECUTE 'COPY ' || tmp || ' FROM ' || file;
  EXECUTE 'SELECT content FROM ' || tmp INTO content;
    Do some more stuff here
  EXECUTE 'DROP TABLE ' || tmp;
END;   $$ LANGUAGE plpgsql VOLATILE;

I'm not really happy with this as it is doing so much more work than necessary. I'd prefer not to create/drop relations cause all I really want to do is run Postgres' JSON functions against the content of some .json file. Does anyone know of a better way to do this without using psql?

Erwin Brandstetter · Accepted Answer · 2019-04-05T03:27:18.640

If the file contains a valid JSON literal, you could read it in with pg_read_file() and assign to a json variable directly:

CREATE OR REPLACE FUNCTION file_read(file text)
  RETURNS void AS
$func$
DECLARE
   content json := pg_read_file(file, 0, 10000000);  -- arbitrary max. 10 MB 
BEGIN
   -- do some more stuff here
END
$func$  LANGUAGE plpgsql;

But that requires superuser privileges for reasons explained in the manual where I linked.

You could make that a SECURITY DEFINER function, owned by a superuser, but be very careful who to grant the EXECUTE privilege then. Example:

Creating UUID extension as the non super privileged user

Getting the contents of a json file as a variable in plpgsql?

1 Answers1