Re: psql: add \create_function command

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> pá 26. 1. 2024 v 19:41 odesílatel Steve Chavez <steve(at)supabase(dot)io> napsal:
>> To solve the above issue, this patch adds a psql command to create a
>> function and obtain its body from another file. It is used as:
>> \create_function from ./data/max.py max(int,int) returns int LANGUAGE
>> plpython3u

> looks a little bit obscure - why do you need to do it from psql? And how
> frequently do you do it?
> I think so this is fix on wrong place - you should to fix linters, not psql
> - more without header you cannot do correct linting

It feels wrong to me too. I'm not sure where is a better place to
implement something like this though. We can't support it server-side
because of permissions issues, so if there's to be any merging of
files it has to happen on the client side.

It strikes me though that thinking about this in terms of CREATE
FUNCTION is thinking too small. ISTM that the requirement of
"grab the content of a file, quote it as a string literal, and
embed it into a SQL command" exists elsewhere. For one thing
there's CREATE PROCEDURE, but I've needed this occasionally
just as a way of feeding data into SELECT, INSERT, etc.

Now, you can do it today:

\set fbody `cat source_file.txt`
CREATE FUNCTION foo() RETURNS whatever AS :'fbody' LANGUAGE ...;

and maybe we should say that that's sufficient. 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
CREATE FUNCTION foo() RETURNS whatever AS :'fbody' LANGUAGE ...;

(\file_write to go the other way seems potentially useful too.)

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 ...;

regards, tom lane