Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Combine FSM updates for prune and no-prune cases.

  1. psql: add \create_function command

    Steve Chavez <steve@supabase.io> — 2024-01-26T18:41:05Z

    Hello hackers,

Currently a function definition must include its body inline. Because of
this, when storing function definitions in files, linters and syntax
highlighters for non-SQL languages (python, perl, tcl, etc) won't work. An
example can be seen on:

https://github.com/postgres/postgres/blob/5eafacd2797dc0b04a0bde25fbf26bf79903e7c2/src/pl/plpython/sql/plpython_test.sql#L15-L24

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

Its design is similar to the `\copy` command, which is a frontend version
of the COPY statement.

This patch is at an initial stage but includes tests with plpython3u,
pltcl, plperl and tab completion.

Any feedback is welcomed.

Best regards,
Steve Chavez

  2. Re: psql: add \create_function command

    Pavel Stehule <pavel.stehule@gmail.com> — 2024-01-26T18:51:48Z

    Hi

pá 26. 1. 2024 v 19:41 odesílatel Steve Chavez <steve@supabase.io> napsal:

> Hello hackers,
>
> Currently a function definition must include its body inline. Because of
> this, when storing function definitions in files, linters and syntax
> highlighters for non-SQL languages (python, perl, tcl, etc) won't work. An
> example can be seen on:
>
>
> https://github.com/postgres/postgres/blob/5eafacd2797dc0b04a0bde25fbf26bf79903e7c2/src/pl/plpython/sql/plpython_test.sql#L15-L24
>
> 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
>
> Its design is similar to the `\copy` command, which is a frontend version
> of the COPY statement.
>
> This patch is at an initial stage but includes tests with plpython3u,
> pltcl, plperl and tab completion.
>
> Any feedback is welcomed.
>

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

Regards

Pavel



>
> Best regards,
> Steve Chavez
>

  3. Re: psql: add \create_function command

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-26T19:23:29Z

    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

  4. Re: psql: add \create_function command

    Wolfgang Walther <walther@technowledgy.de> — 2024-01-26T19:38:34Z

    Tom Lane:
> 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 ...;

That would indeed be very useful! I would immediately use this in a lot 
of places.

  5. Re: psql: add \create_function command

    Wolfgang Walther <walther@technowledgy.de> — 2024-01-26T19:45:16Z

    Pavel Stehule:
> looks a little bit obscure - why do you need to do it from psql? And how 
> frequently do you do it?

I store all my SQL code in git and use "psql -e" to "bundle" it into an 
extension, which is then deployed to production.

The code is spread over many files, which include other files via \ir. 
Sometimes you need to include other types of files, though - for example 
code in other languages as Steve mentioned, but I have also had cases 
for yaml files, markdown templates, even binary assets which should 
still be considered "code" and not data.

So anything in that direction would help.

  6. Re: psql: add \create_function command

    David G. Johnston <david.g.johnston@gmail.com> — 2024-01-26T19:49:29Z

    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.

David J.

  7. Re: psql: add \create_function command

    Pavel Stehule <pavel.stehule@gmail.com> — 2024-01-26T19:50:22Z

    pá 26. 1. 2024 v 20:45 odesílatel <walther@technowledgy.de> napsal:

> Pavel Stehule:
> > looks a little bit obscure - why do you need to do it from psql? And how
> > frequently do you do it?
>
> I store all my SQL code in git and use "psql -e" to "bundle" it into an
> extension, which is then deployed to production.
>

this is good way


>
> The code is spread over many files, which include other files via \ir.
> Sometimes you need to include other types of files, though - for example
> code in other languages as Steve mentioned, but I have also had cases
> for yaml files, markdown templates, even binary assets which should
> still be considered "code" and not data.
>
> So anything in that direction would help.
>

but why you need to do in psql? - you can prepare content outside and
execute just like echo "CREATE FUNCTION ...." | psql

  8. Re: psql: add \create_function command

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-26T19:52:05Z

    walther@technowledgy.de writes:
> Pavel Stehule:
>> looks a little bit obscure - why do you need to do it from psql? And how 
>> frequently do you do it?

> I store all my SQL code in git and use "psql -e" to "bundle" it into an 
> extension, which is then deployed to production.

> The code is spread over many files, which include other files via \ir. 

That reminds me: if we do either \file_read or :{file}, we should
define relative paths as working like \ir, that is it's relative
to the current script's directory when we're reading from a script.
This is almost always the behavior you want, and the principal
functional problem with the `cat ...` solution is that it doesn't
work that way.

			regards, tom lane

  9. Re: psql: add \create_function command

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-26T20:04:55Z

    Pavel Stehule <pavel.stehule@gmail.com> writes:
> but why you need to do in psql? - you can prepare content outside and
> execute just like echo "CREATE FUNCTION ...." | psql

The bit that's probably hard if you're trying to do this in a shell
script is "quote this data as a SQL string literal".  psql can get
that right even in the face of encoding considerations,
standard_conforming_strings, etc.  Not sure you can build a
fully bulletproof solution outside.

			regards, tom lane

  10. Re: psql: add \create_function command

    Pavel Stehule <pavel.stehule@gmail.com> — 2024-01-26T20:13:39Z

    pá 26. 1. 2024 v 21:04 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > but why you need to do in psql? - you can prepare content outside and
> > execute just like echo "CREATE FUNCTION ...." | psql
>
> The bit that's probably hard if you're trying to do this in a shell
> script is "quote this data as a SQL string literal".  psql can get
> that right even in the face of encoding considerations,
> standard_conforming_strings, etc.  Not sure you can build a
> fully bulletproof solution outside.
>

I don't know, maybe I have a problem with the described use case. I cannot
imagine holding the body and head of PL routines in different places and I
don't understand the necessity to join it.

On second hand, few years ago (if I remember well, I proposed some like
`:{file}`. I don't remember the syntax. But it was not finished, and then I
wrote

https://github.com/okbob/pgimportdoc

The possibility for some simple import external data can be nice




>                         regards, tom lane
>

  11. Re: psql: add \create_function command

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-26T20:17:46Z

    Pavel Stehule <pavel.stehule@gmail.com> writes:
> I don't know, maybe I have a problem with the described use case. I cannot
> imagine holding the body and head of PL routines in different places and I
> don't understand the necessity to join it.

It seems a little weird to me too, and I would vote against accepting
\create_function as described because I think too few people would
want to use it.  However, the idea of an easy way to pull in a file
and convert it to a SQL literal seems like it has many applications.

			regards, tom lane

  12. Re: psql: add \create_function command

    Pavel Stehule <pavel.stehule@gmail.com> — 2024-01-26T20:18:47Z

    pá 26. 1. 2024 v 21:17 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > I don't know, maybe I have a problem with the described use case. I
> cannot
> > imagine holding the body and head of PL routines in different places and
> I
> > don't understand the necessity to join it.
>
> It seems a little weird to me too, and I would vote against accepting
> \create_function as described because I think too few people would
> want to use it.  However, the idea of an easy way to pull in a file
> and convert it to a SQL literal seems like it has many applications.
>

+1

Pavel


>                         regards, tom lane
>

  13. Re: psql: add \create_function command

    Adam S <adam.sah@gmail.com> — 2024-01-26T20:23:43Z

    idea: what about custom functions for (each) IDE, which calls psql -c
"CREATE FUNCTION ..." when the user saves the file?  (it would easy to
prototype for emacs...)
(obviously, this isn't a core feature...)


On Fri, Jan 26, 2024 at 3:19 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

>
>
> pá 26. 1. 2024 v 21:17 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>
>> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> > I don't know, maybe I have a problem with the described use case. I
>> cannot
>> > imagine holding the body and head of PL routines in different places
>> and I
>> > don't understand the necessity to join it.
>>
>> It seems a little weird to me too, and I would vote against accepting
>> \create_function as described because I think too few people would
>> want to use it.  However, the idea of an easy way to pull in a file
>> and convert it to a SQL literal seems like it has many applications.
>>
>
> +1
>
> Pavel
>
>
>>                         regards, tom lane
>>
>

  14. Re: psql: add \create_function command

    Andrew Dunstan <andrew@dunslane.net> — 2024-01-29T13:42:02Z

    On 2024-01-26 Fr 15:17, Tom Lane wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> I don't know, maybe I have a problem with the described use case. I cannot
>> imagine holding the body and head of PL routines in different places and I
>> don't understand the necessity to join it.
> It seems a little weird to me too, and I would vote against accepting
> \create_function as described because I think too few people would
> want to use it.  However, the idea of an easy way to pull in a file
> and convert it to a SQL literal seems like it has many applications.
>
> 			


Yes, this proposal is far too narrow and would not cater for many use 
cases I have had in the past.

I like your ideas upthread about \file_read and :{filename}


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

  15. Re: psql: add \create_function command

    Steve Chavez <steve@supabase.io> — 2024-01-29T16:54:45Z

    > I like your ideas upthread about \file_read and :{filename}

Great ideas! :{filename} looks more convenient to use than \file_read just
because it's one less command to execute.

However, :{?variable_name} is already taken by psql to test whether a
variable is defined or not. It might be confusing to use the same syntax.

How about using the convention of interpreting an identifier as a file path
if it has an slash on it?

This is used in the Nix language and from experience it works very well:
https://nix.dev/manual/nix/2.18/language/values#type-path
It also makes it very clear that you're using a file path, e.g. :{filename}
vs :./filename. Examples:

select jsonb_to_recordset(:./contents.json);
create function foo() returns text AS :/absolute/path/contents.py language
plpython3u;

Any thoughts?

Best regards,
Steve Chavez

On Mon, 29 Jan 2024 at 08:42, Andrew Dunstan <andrew@dunslane.net> wrote:

>
> On 2024-01-26 Fr 15:17, Tom Lane wrote:
> > Pavel Stehule <pavel.stehule@gmail.com> writes:
> >> I don't know, maybe I have a problem with the described use case. I
> cannot
> >> imagine holding the body and head of PL routines in different places
> and I
> >> don't understand the necessity to join it.
> > It seems a little weird to me too, and I would vote against accepting
> > \create_function as described because I think too few people would
> > want to use it.  However, the idea of an easy way to pull in a file
> > and convert it to a SQL literal seems like it has many applications.
> >
> >
>
>
> Yes, this proposal is far too narrow and would not cater for many use
> cases I have had in the past.
>
> I like your ideas upthread about \file_read and :{filename}
>
>
> cheers
>
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>

  16. Re: psql: add \create_function command

    Pavel Stehule <pavel.stehule@gmail.com> — 2024-01-29T17:05:42Z

    po 29. 1. 2024 v 17:54 odesílatel Steve Chavez <steve@supabase.io> napsal:

> > I like your ideas upthread about \file_read and :{filename}
>
> Great ideas! :{filename} looks more convenient to use than \file_read just
> because it's one less command to execute.
>
> However, :{?variable_name} is already taken by psql to test whether a
> variable is defined or not. It might be confusing to use the same syntax.
>
> How about using the convention of interpreting an identifier as a file
> path if it has an slash on it?
>
> This is used in the Nix language and from experience it works very well:
> https://nix.dev/manual/nix/2.18/language/values#type-path
> It also makes it very clear that you're using a file path, e.g.
> :{filename} vs :./filename. Examples:
>
> select jsonb_to_recordset(:./contents.json);
> create function foo() returns text AS :/absolute/path/contents.py language
> plpython3u;
>
> Any thoughts?
>

has sense

Pavel


>
> Best regards,
> Steve Chavez
>
> On Mon, 29 Jan 2024 at 08:42, Andrew Dunstan <andrew@dunslane.net> wrote:
>
>>
>> On 2024-01-26 Fr 15:17, Tom Lane wrote:
>> > Pavel Stehule <pavel.stehule@gmail.com> writes:
>> >> I don't know, maybe I have a problem with the described use case. I
>> cannot
>> >> imagine holding the body and head of PL routines in different places
>> and I
>> >> don't understand the necessity to join it.
>> > It seems a little weird to me too, and I would vote against accepting
>> > \create_function as described because I think too few people would
>> > want to use it.  However, the idea of an easy way to pull in a file
>> > and convert it to a SQL literal seems like it has many applications.
>> >
>> >
>>
>>
>> Yes, this proposal is far too narrow and would not cater for many use
>> cases I have had in the past.
>>
>> I like your ideas upthread about \file_read and :{filename}
>>
>>
>> cheers
>>
>>
>> andrew
>>
>> --
>> Andrew Dunstan
>> EDB: https://www.enterprisedb.com
>>
>>

  17. Re: psql: add \create_function command

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-29T17:11:48Z

    Steve Chavez <steve@supabase.io> writes:
> However, :{?variable_name} is already taken by psql to test whether a
> variable is defined or not. It might be confusing to use the same syntax.

Hmm.  Maybe we could go with :{+...} or the like?

> How about using the convention of interpreting an identifier as a file path
> if it has an slash on it?

Sorry, that is just horrid.  foo/bar means division, and "foo/bar"
is simply an identifier per SQL standard, so you can't squeeze that
in without breaking an ocean of stuff.  Plus, there are many use-cases
where there's no reason to put a slash in a relative filename.

			regards, tom lane

  18. Re: psql: add \create_function command

    Pavel Stehule <pavel.stehule@gmail.com> — 2024-01-29T17:29:12Z

    po 29. 1. 2024 v 18:11 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> Steve Chavez <steve@supabase.io> writes:
> > However, :{?variable_name} is already taken by psql to test whether a
> > variable is defined or not. It might be confusing to use the same syntax.
>
> Hmm.  Maybe we could go with :{+...} or the like?
>
> > How about using the convention of interpreting an identifier as a file
> path
> > if it has an slash on it?
>
> Sorry, that is just horrid.  foo/bar means division, and "foo/bar"
> is simply an identifier per SQL standard, so you can't squeeze that
> in without breaking an ocean of stuff.  Plus, there are many use-cases
> where there's no reason to put a slash in a relative filename.
>

sometimes paths starts by $ or .

or maybe :{{ ... }}



>
>                         regards, tom lane
>

  19. Re: psql: add \create_function command

    Steve Chavez <steve@supabase.io> — 2024-03-10T23:01:21Z

    > Maybe we could go with :{+...} or the like?
> or maybe :{{ ... }}

Tab completion didn't work for :{?<var>} and I noted that the same problem
would arise for :{+ or :{{ (and tab completion would be more important
here). So I fixed that on:

https://www.postgresql.org/message-id/flat/CAGRrpzZU48F2oV3d8eDLr=4TU9xFH5Jt9ED+qU1+X91gMH68Sw@mail.gmail.com

Would be great to have the above fix reviewed/committed to keep making
progress here.

Besides that, since :{ is already sort of a prefix for psql functions, how
about having `:{file(<filename>)}`? That would be clearer than :{+ or :{{.

Best regards,
Steve Chavez

On Mon, 29 Jan 2024 at 12:29, Pavel Stehule <pavel.stehule@gmail.com> wrote:

>
>
> po 29. 1. 2024 v 18:11 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>
>> Steve Chavez <steve@supabase.io> writes:
>> > However, :{?variable_name} is already taken by psql to test whether a
>> > variable is defined or not. It might be confusing to use the same
>> syntax.
>>
>> Hmm.  Maybe we could go with :{+...} or the like?
>>
>> > How about using the convention of interpreting an identifier as a file
>> path
>> > if it has an slash on it?
>>
>> Sorry, that is just horrid.  foo/bar means division, and "foo/bar"
>> is simply an identifier per SQL standard, so you can't squeeze that
>> in without breaking an ocean of stuff.  Plus, there are many use-cases
>> where there's no reason to put a slash in a relative filename.
>>
>
> sometimes paths starts by $ or .
>
> or maybe :{{ ... }}
>
>
>
>>
>>                         regards, tom lane
>>
>