On Fri, Jan 26, 2024 at 12:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
\set fbody `cat source_file.txt` CREATE FUNCTION foo() RETURNS whatever AS :'fbody' LANGUAGE ...;
and maybe we should say that that's sufficient.
I really don't have a problem, and kinda prefer, using psql variables this way but feel much more comfortable not having to invoke a shell.
It's a bit klugy though. One level of improvement could be to get rid of the dependency on "cat" by inventing a backslash command to read a file into a variable:
\file_read fbody source_file.txt
This I would use to reliably read external json text files into a psql variable so that I could use jsonb_to_recordset(:var) on the contents.
(\file_write to go the other way seems potentially useful too.)
The nearby discussions regarding trying to produce json into files would support this claim.
Or we could cut out the intermediate variable altogether by inventing something that works like :'...' but reads from a file not a variable. That might be too specialized though, and I'm not sure about good syntax for it either. Maybe like
CREATE FUNCTION foo() RETURNS whatever AS :{source_file.txt} LANGUAGE ...;
IMO, not enough improvement to be had over letting psql variables act as the intermediary to justify the effort.
