Getting the contents of a json file as a variable in plpgsql? 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?
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