Re: psql: add \create_function command - Mailing list pgsql-hackers

From Tom Lane
Subject Re: psql: add \create_function command
Date
Msg-id 1023141.1706297009@sss.pgh.pa.us
Whole thread Raw
In response to Re: psql: add \create_function command  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: psql: add \create_function command
Re: psql: add \create_function command
List pgsql-hackers
Pavel Stehule <pavel.stehule@gmail.com> writes:
> pá 26. 1. 2024 v 19:41 odesílatel Steve Chavez <steve@supabase.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



pgsql-hackers by date:

Previous
From: "David E. Wheeler"
Date:
Subject: Re: Patch: Improve Boolean Predicate JSON Path Docs
Next
From: Jeff Davis
Date:
Subject: Re: Improve WALRead() to suck data directly from WAL buffers when possible