Thread: Feature request: include script file into function body
Hi
I asked on pgsql-general 31 Jan 2011 if there were a way to do this, and got
no response, so let's make it a feature request.
It would be really nice to have a way to load script (especially Python
and Perl) from a separate file into a function body. Some advantages would
be: to run a code checker outside of Postgresql, and to make things easier
for source code colorizers.
I have in mind syntax something like
================================================
CREATE OR REPLACE FUNCTION
myfunc( ... )
RETURNS VOID FROM 'ScriptFile.py' LANGUAGE PLPYTHONU;
================================================
I think the FROM keyword fits here, and serves to distinguish behavior
from AS. This is just a suggestion though.
The file name ought to work in the usual way: without a leading slash
to be interpreted as a path relative to the current directory (in case the
command is inside an .sql file, that would be the directory containing the
.sql file). With a leading slash, it would be taken as an absolute path.
For distribution purposes, it would also be nice to have some portable means
of indicating the installation directory of the running PostgreSQL, perhaps
with an environment variable replacement (e.g. $LIBDIR).
Cheers!
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -
Steve White <swhite@aip.de> writes:
> It would be really nice to have a way to load script (especially Python
> and Perl) from a separate file into a function body.
This seems like a security hole, ie, you could use it to read any file
the backend has access to.
regards, tom lane
Hi Tom,
This seems like a detail that is beside the point I'm making.
But security is important, so let's think about it.
PostgreSQL has an \i command, which loads the text from any readable file
interpretes and executes it as further PostgreSQL commands. I'm proposing
a similar mechanism that would load a file containing script language, and
process it as though it were in the current funcition body.
Isn't the \i command a similar security hole?
If somehow loading script text for a function is substantially different
from loading it by \i, and if there is some problem, it seems to me that
some simple restriction could solve it, such as restricting the directories
from which such files can be read. But I'm just guessing here.
I'll leave it to the security experts explicitly by amending my original
proposal with this:
" -- without doing anything stupid that would open a security hole."
Cheers again!
On 1.02.11, Tom Lane wrote:
> Steve White <swhite@aip.de> writes:
> > It would be really nice to have a way to load script (especially Python
> > and Perl) from a separate file into a function body.
>
> This seems like a security hole, ie, you could use it to read any file
> the backend has access to.
>
> regards, tom lane
>
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -
Hello 2011/2/1 Steve White <swhite@aip.de>: > Hi Tom, > > This seems like a detail that is beside the point I'm making. > But security is important, so let's think about it. > > PostgreSQL has an \i command, which loads the text from any readable file > interpretes and executes it as further PostgreSQL commands. =C2=A0I'm pro= posing > a similar mechanism that would load a file containing script language, and > process it as though it were in the current funcition body. > > Isn't the \i command a similar security hole? if you ran psql under "postgres" account, then it is. I don't think, so your idea is good too. What about caching? Code of stored procedures stays in session cache. Who will ensure, so your cache is fresh? Why you need a direct link to source files? Regards Pavel Stehule > > If somehow loading script text for a function is substantially different > from loading it by \i, and if there is some problem, it seems to me that > some simple restriction could solve it, such as restricting the directori= es > from which such files can be read. =C2=A0But I'm just guessing here. > > I'll leave it to the security experts explicitly by amending my original > proposal with this: > > =C2=A0 =C2=A0 =C2=A0 =C2=A0" -- without doing anything stupid that would = open a security hole." > > Cheers again! > > > On =C2=A01.02.11, Tom Lane wrote: >> Steve White <swhite@aip.de> writes: >> > It would be really nice to have a way to load script (especially Python >> > and Perl) from a separate file into a function body. >> >> This seems like a security hole, ie, you could use it to read any file >> the backend has access to. >> >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 regards, tom lane >> > > -- > | - =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2= =A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0= - =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- > | Steve White =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 +49(331)7499-202 > | E-Science =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Zi. 27 =C2=A0Villa Turbulenz > | - =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2= =A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0= - =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- > | Astrophysikalisches Institut Potsdam (AIP) > | An der Sternwarte 16, D-14482 Potsdam > | > | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz > | > | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 > | - =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2= =A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0= - =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- =C2=A0- > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
[Please don't top-post. Rearranged for clarity.] Steve White <swhite@aip.de> wrote: > On 1.02.11, Tom Lane wrote: >> Steve White <swhite@aip.de> writes: >>> It would be really nice to have a way to load script (especially >>> Python and Perl) from a separate file into a function body. >> >> This seems like a security hole, ie, you could use it to read any >> file the backend has access to. > Isn't the \i command a similar security hole? That is run by a client program on a client machine. If that is what you had in mind, a modification to the CREATE FUNCTION syntax is probably not the way to go. Just to throw a hypothetical out there, were you looking to effectively do a \i inside the string literal which is the function body, picking up a *client-side* file? That has its own problems, of course, but I'm just trying to get us onto the same page. -Kevin
Hi Pavel, On 1.02.11, Pavel Stehule wrote: > Hello > > 2011/2/1 Steve White <swhite@aip.de>: > > Hi Tom, > > > > This seems like a detail that is beside the point I'm making. > > But security is important, so let's think about it. > > > > PostgreSQL has an \i command, which loads the text from any readable file > > interpretes and executes it as further PostgreSQL commands. I'm proposing > > a similar mechanism that would load a file containing script language, and > > process it as though it were in the current funcition body. > > > > Isn't the \i command a similar security hole? > > if you ran psql under "postgres" account, then it is. > > I don't think, so your idea is good too. What about caching? Code of > stored procedures stays in session cache. Who will ensure, so your > cache is fresh? > Another good point that is beside the point I was making. But OK we can discuss that too. I would think, it should work exactly as if the text had been textually included, the first time the function is compiled, exactly as the inline text is handled now. > Why you need a direct link to source files? > There are several reasons, a couple of which are mentioned in the discussion in the pgsql-general list. http://archives.postgresql.org/pgsql-general/2011-01/msg00870.php Cheers! > Regards > > Pavel Stehule > > > > > If somehow loading script text for a function is substantially different > > from loading it by \i, and if there is some problem, it seems to me that > > some simple restriction could solve it, such as restricting the directories > > from which such files can be read. But I'm just guessing here. > > > > I'll leave it to the security experts explicitly by amending my original > > proposal with this: > > > > " -- without doing anything stupid that would open a security hole." > > > > Cheers again! > > > > > > On 1.02.11, Tom Lane wrote: > >> Steve White <swhite@aip.de> writes: > >> > It would be really nice to have a way to load script (especially Python > >> > and Perl) from a separate file into a function body. > >> > >> This seems like a security hole, ie, you could use it to read any file > >> the backend has access to. > >> > >> regards, tom lane > >> > > > > -- > > | - - - - - - - - - - - - - - - - - - - - - - - - - > > | Steve White +49(331)7499-202 > > | E-Science Zi. 27 Villa Turbulenz > > | - - - - - - - - - - - - - - - - - - - - - - - - - > > | Astrophysikalisches Institut Potsdam (AIP) > > | An der Sternwarte 16, D-14482 Potsdam > > | > > | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz > > | > > | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 > > | - - - - - - - - - - - - - - - - - - - - - - - - - > > > > -- > > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-bugs > > > -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-Science Zi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - -
Hi Kevin,
On 1.02.11, Kevin Grittner wrote:
> [Please don't top-post. Rearranged for clarity.]
>
As you like.
> Steve White <swhite@aip.de> wrote:
> > On 1.02.11, Tom Lane wrote:
> >> Steve White <swhite@aip.de> writes:
> >>> It would be really nice to have a way to load script (especially
> >>> Python and Perl) from a separate file into a function body.
> >>
> >> This seems like a security hole, ie, you could use it to read any
> >> file the backend has access to.
>
> > Isn't the \i command a similar security hole?
>
> That is run by a client program on a client machine.
Sorry I don't understand this remark.
Are you saying that \i is disabled to user postgres?
Just tried: it isn't.
Are you saying that as a normal user I can use \i to load a file that I
don't normally have access to?
Just tried: nope -- permission denied.
What scenario do you have in mind?
> If that is what you had in mind, a modification to the CREATE FUNCTION syntax
> is probably not the way to go. Just to throw a hypothetical out
> there, were you looking to effectively do a \i inside the string
> literal which is the function body, picking up a *client-side* file?
>
> That has its own problems, of course,
If I understand what you're proposing: write a PostgreSQL function
with LANGUAGE PLPYTHONU, and inside the function body load the file
with \i. Is that it?
I already tried it, and it doesn't work for the obvious reason:
The function body is interpreted as being of the other language, in
which "\i" is a syntax error. Some other attempts are also mentioned
in the pgsql-general posting
http://archives.postgresql.org/pgsql-general/2011-01/msg00870.php
I'm not married to the syntax I suggested. The functionality I want is
to separate the function body code from the SQL code.
> ... but I'm just trying to get us onto the same page.
>
By all means.
It is clear we aren't on the same page: I'm not grasping the objections,
and you probably haven't been doing the coding that makes this mixing of
languages in one file such a nuisance.
Let's keep bashing it around.
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -
Steve White wrote: > Hi Kevin, > > On 1.02.11, Kevin Grittner wrote: > > [Please don't top-post. Rearranged for clarity.] > > > As you like. > > > Steve White <swhite@aip.de> wrote: > > > On 1.02.11, Tom Lane wrote: > > >> Steve White <swhite@aip.de> writes: > > >>> It would be really nice to have a way to load script (especially > > >>> Python and Perl) from a separate file into a function body. > > >> > > >> This seems like a security hole, ie, you could use it to read any > > >> file the backend has access to. > > > > > Isn't the \i command a similar security hole? > > > > That is run by a client program on a client machine. > > Sorry I don't understand this remark. > > Are you saying that \i is disabled to user postgres? > Just tried: it isn't. > Are you saying that as a normal user I can use \i to load a file that I > don't normally have access to? > Just tried: nope -- permission denied. > > What scenario do you have in mind? \i is a psql client command, not something the backend runs. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Hi again, all, OK I think I now know what the misunderstanding is. > [Please don't top-post. Rearranged for clarity.] > > Steve White <swhite@aip.de> wrote: > > On 1.02.11, Tom Lane wrote: > >> Steve White <swhite@aip.de> writes: > >>> It would be really nice to have a way to load script (especially > >>> Python and Perl) from a separate file into a function body. > >> > >> This seems like a security hole, ie, you could use it to read any > >> file the backend has access to. > > > Isn't the \i command a similar security hole? > > That is run by a client program on a client machine. If that is > what you had in mind, a modification to the CREATE FUNCTION syntax > is probably not the way to go. Just to throw a hypothetical out > there, were you looking to effectively do a \i inside the string > literal which is the function body, picking up a *client-side* file? > > That has its own problems, of course, but I'm just trying to get us > onto the same page. > > -Kevin > I guess the "FROM filename" syntax wasn't a great choice, as it suggests something completely different from what I was otherwise describing. (In my own defense: I repeatedly qualified the syntax as a suggestion.) I *DO NOT MEAN* that a query should run about grabbing files off the server, or wherever. I meant something like the replacement that happens with the \i command in loading SQL, and under similar circumstances, except that somehow non-SQL code is loadad in a function body. Again, this would greatly facilitate programming mixed-language programming. Thanks! -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-Science Zi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - -
Steve White <swhite@aip.de> wrote: > On 1.02.11, Kevin Grittner wrote: >> Steve White <swhite@aip.de> wrote: >>> Isn't the \i command a similar security hole? >> >> That is run by a client program on a client machine. > > Sorry I don't understand this remark. The CREATE FUNCTION statement is parsed and executed on the *server*, so implementing the feature as you describe it would involved reading files on the database server machine. With the security of the OS user which owns the database's data directory. Outside of development, the client is generally (in my experience, anyway) on another machine, or at least not running under the user ID with rights to the PostgreSQL data directory. It seems to me that something which let you pull the body of a script file into the statement on the *client* side of the connection, before sending the CREATE FUNCTION statement to the server would not only be a lot more secure (you could only read files that the OS would let your login read anyway), but would be more *useful*. > Are you saying that \i is disabled to user postgres? I'm saying it runs on the client side of the connection to the database, running with the rights of whatever user executed psql. > If I understand what you're proposing: write a PostgreSQL function > with LANGUAGE PLPYTHONU, and inside the function body load the > file with \i. Is that it? Well, \i inside the string literal obviously won't work. I'm saying some logical equivalent with new syntax. Something which pulls the file into the client software. I don't have any particularly clever suggestions to offer for syntax. -Kevin
Hi again,
Now that I know what got you all riled, I can propose something that
might be more satisfactory. See below.
On 1.02.11, Steve White wrote:
> Hi again, all,
>
> OK I think I now know what the misunderstanding is.
>
> > [Please don't top-post. Rearranged for clarity.]
> >
> > Steve White <swhite@aip.de> wrote:
> > > On 1.02.11, Tom Lane wrote:
> > >> Steve White <swhite@aip.de> writes:
> > >>> It would be really nice to have a way to load script (especially
> > >>> Python and Perl) from a separate file into a function body.
> > >>
> > >> This seems like a security hole, ie, you could use it to read any
> > >> file the backend has access to.
> >
> > > Isn't the \i command a similar security hole?
> >
> > That is run by a client program on a client machine. If that is
> > what you had in mind, a modification to the CREATE FUNCTION syntax
> > is probably not the way to go. Just to throw a hypothetical out
> > there, were you looking to effectively do a \i inside the string
> > literal which is the function body, picking up a *client-side* file?
> >
> > That has its own problems, of course, but I'm just trying to get us
> > onto the same page.
> >
> > -Kevin
> >
> I guess the "FROM filename" syntax wasn't a great choice, as it suggests
> something completely different from what I was otherwise describing.
> (In my own defense: I repeatedly qualified the syntax as a suggestion.)
>
> I *DO NOT MEAN* that a query should run about grabbing files off the
> server, or wherever.
>
> I meant something like the replacement that happens with the \i command
> in loading SQL, and under similar circumstances, except that somehow
> non-SQL code is loadad in a function body.
>
> Again, this would greatly facilitate programming mixed-language
> programming.
>
Try this instead:
================================================
CREATE OR REPLACE FUNCTION
myfunc( ... )
RETURNS VOID AS '#PGSQL_IMPORT filename' LANGUAGE PLPYTHONU;
================================================
This would work something like this:
The script interpeter would scan the body code for comments that
start exactly with PGSQL_IMPORT. Whereever they are found, it would
attempt to open and include the text (failing appropriately if the
file can't be read).
Of course, this is language-dependent, but for any given lanugage,
something like that will work.
What do you think?
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -
Steve White <swhite@aip.de> wrote: > I *DO NOT MEAN* that a query should run about grabbing files off > the server, or wherever. > > I meant something like the replacement that happens with the \i > command in loading SQL, and under similar circumstances, except > that somehow non-SQL code is loadad in a function body. Maybe some option for the \i command? \iq (for input quoted) with automatic $$ quoting around what is read? That way you could do something like: CREATE FUNCTION yadda_yadda() returns text language plpythonu as \iq yadda_yadda.py ; -Kevin
On 01/02/2011 6:50 PM, Steve White wrote: > Hi again, all, > > OK I think I now know what the misunderstanding is. > >> [Please don't top-post. Rearranged for clarity.] >> >> Steve White<swhite@aip.de> wrote: >>> On 1.02.11, Tom Lane wrote: >>>> Steve White<swhite@aip.de> writes: >>>>> It would be really nice to have a way to load script (especially >>>>> Python and Perl) from a separate file into a function body. >>>> This seems like a security hole, ie, you could use it to read any >>>> file the backend has access to. >> >>> Isn't the \i command a similar security hole? >> >> That is run by a client program on a client machine. If that is >> what you had in mind, a modification to the CREATE FUNCTION syntax >> is probably not the way to go. Just to throw a hypothetical out >> there, were you looking to effectively do a \i inside the string >> literal which is the function body, picking up a *client-side* file? >> >> That has its own problems, of course, but I'm just trying to get us >> onto the same page. >> >> -Kevin >> > I guess the "FROM filename" syntax wasn't a great choice, as it suggests > something completely different from what I was otherwise describing. > (In my own defense: I repeatedly qualified the syntax as a suggestion.) > > I *DO NOT MEAN* that a query should run about grabbing files off the > server, or wherever. > > I meant something like the replacement that happens with the \i command > in loading SQL, and under similar circumstances, except that somehow > non-SQL code is loadad in a function body. But functions *run* on the server, in the postgres server backend, so it would have to grab files from the server, which is where the security issue comes in. The \i command *runs* on the client under your own account and reads text into the *client*, not the server. The two things are completely different and run in completely different places. Cheers, Gary.
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> It seems to me that something which let you pull the body of a
> script file into the statement on the *client* side of the
> connection, before sending the CREATE FUNCTION statement to the
> server would not only be a lot more secure (you could only read
> files that the OS would let your login read anyway), but would be
> more *useful*.
The usual procedure is to put the whole CREATE FUNCTION statement
into a file, which you can then send with \i. I'm not quite seeing
the point of having just the body in a file? It's not like the body
is typically useful to run as standalone code.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > The usual procedure is to put the whole CREATE FUNCTION statement > into a file, which you can then send with \i. I'm not quite > seeing the point of having just the body in a file? It's not like > the body is typically useful to run as standalone code. The OP mentioned text highlighting, which has bothered me at times -- the whole body of the function is highlighted as a string literal, at least in Kate, which I use heavily. (Don't laugh *too* hard.) I haven't ever wanted to test script functions outside of PostgreSQL before bringing them in, but it seems at least within the realm of possibility that someone might have functional code with doesn't directly access the database they would like to share with outside processes or test outside before bringing in. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The usual procedure is to put the whole CREATE FUNCTION statement
>> into a file, which you can then send with \i. I'm not quite
>> seeing the point of having just the body in a file? It's not like
>> the body is typically useful to run as standalone code.
> The OP mentioned text highlighting, which has bothered me at times
Ah, that's a fair point, particular with smarter editors.
Seems like what you want here is a variant of \i that pulls in the
file, escapes it as a string literal, and appends that to the
query buffer. Then you write something like
create function ... as
\istring myfunction.pl
;
A backslash command defined that way might have other applications than
CREATE FUNCTION, too.
regards, tom lane
Steve White <swhite@aip.de> writes:
> Try this instead:
> ================================================
> CREATE OR REPLACE FUNCTION
> myfunc( ... )
> RETURNS VOID AS '#PGSQL_IMPORT filename' LANGUAGE PLPYTHONU;
> ================================================
I think having psql decide that string literals mean something other
than their face value is Right Out --- it would bite you on the rear
just when you least expect it. And the notion of the behavior being
language-dependent is right out to the fourth power. But see my
alternative proposal to Kevin just now.
regards, tom lane
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Maybe some option for the \i command? \iq (for input quoted) with
> automatic $$ quoting around what is read?
> That way you could do something like:
> CREATE FUNCTION yadda_yadda() returns text language plpythonu as
> \iq yadda_yadda.py
> ;
Just got this --- looks like we independently arrived at the same
conclusion.
regards, tom lane
Hi guys, On 1.02.11, Tom Lane wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > > Maybe some option for the \i command? \iq (for input quoted) with > > automatic $$ quoting around what is read? > > > That way you could do something like: > > > CREATE FUNCTION yadda_yadda() returns text language plpythonu as > > \iq yadda_yadda.py > > ; Yes this will work for me. > > Just got this --- looks like we independently arrived at the same > conclusion. > > regards, tom lane > -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-Science Zi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - -
Hi Tom, I already agreed to Kevin's proposed syntax, and it is better than my suggestion, but mine isn't quite as crazy as you make out. On 1.02.11, Tom Lane wrote: > Steve White <swhite@aip.de> writes: > > Try this instead: > > > ================================================ > > CREATE OR REPLACE FUNCTION > > myfunc( ... ) > > RETURNS VOID AS '#PGSQL_IMPORT filename' LANGUAGE PLPYTHONU; > > ================================================ > > I think having psql decide that string literals mean something other > than their face value is Right Out --- it would bite you on the rear > just when you least expect it. Interesting idea... but why would psql make this decision? Did somebody suggest that off-line? I said that the script interpreter might do this... But the other syntax is better anyway. > And the notion of the behavior being > language-dependent is right out to the fourth power. But see my > alternative proposal to Kevin just now. > -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-Science Zi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - -
Re: Feature request: include script file into function body (better syntax)
From
"Kevin Grittner"
Date:
Steve White <swhite@aip.de> wrote: > I said that the script interpreter might do this... The script interpreter runs on the server. -Kevin
Steve White <swhite@aip.de> wrote: > On 1.02.11, Tom Lane wrote: >> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >>> Maybe some option for the \i command? \iq (for input quoted) >>> with automatic $$ quoting around what is read? >> >>> That way you could do something like: >> >>> CREATE FUNCTION yadda_yadda() returns text language plpythonu as >>> \iq yadda_yadda.py >>> ; > > Yes this will work for me. > >> >> Just got this --- looks like we independently arrived at the same >> conclusion. I'll add it to the TODO list. It looks like we might finally have a good one for those looking for an easy item from that list. Those have been scarce lately. -Kevin
On Tue, Feb 1, 2011 at 3:09 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Steve White <swhite@aip.de> wrote: >> On =A01.02.11, Tom Lane wrote: >>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >>>> Maybe some option for the \i command? =A0\iq (for input quoted) >>>> with automatic $$ quoting around what is read? >>> >>>> That way you could do something like: >>> >>>> CREATE FUNCTION yadda_yadda() returns text language plpythonu as >>>> \iq yadda_yadda.py >>>> ; >> >> Yes this will work for me. >> >>> >>> Just got this --- looks like we independently arrived at the same >>> conclusion. > > I'll add it to the TODO list. =A0It looks like we might finally have a > good one for those looking for an easy item from that list. =A0Those > have been scarce lately. Can't you already do it this way: \set yadda `cat yadda_yadda.py` CREATE FUNCTION yadda_yadda() returns text language plpythonu AS :'yadda'; I guess it probably won't work on Windows... --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> wrote: > Can't you already do it this way: > > \set yadda `cat yadda_yadda.py` > CREATE FUNCTION yadda_yadda() returns text language plpythonu AS > :'yadda'; OK, that works. It sure ain't pretty, though. Clever, but not pretty. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Robert Haas <robertmhaas@gmail.com> wrote:
>> Can't you already do it this way:
>>
>> \set yadda `cat yadda_yadda.py`
>> CREATE FUNCTION yadda_yadda() returns text language plpythonu AS
>> :'yadda';
> Clever, but not pretty.
Yeah. So the question is whether this is useful enough to justify
providing a shortcut.
regards, tom lane
Hi, Robert,
On 1.02.11, Robert Haas wrote:
>
> Can't you already do it this way:
>
> \set yadda `cat yadda_yadda.py`
> CREATE FUNCTION yadda_yadda() returns text language plpythonu AS
> :'yadda';
>
> I guess it probably won't work on Windows...
>
This would also satisfy my immediate needs...
if only I could get it to work.
I made a file 'yadda_yadda.py' containing only the line:
print 'hello world'
====================================================================
d=# \set yadda `cat yadda_yadda.py`
d=# \echo :yadda
print 'hello world'
====================================================================
So far, so good.
But the :'yadda'; produces an error--it seems the variable yadda isn't
expanded in the presence of the quotes.
====================================================================
d=# CREATE FUNCTION yadda_yadda() returns text language plpythonu AS
:'yadda';
ERROR: syntax error at or near ":"
LINE 2: :'yadda';
====================================================================
Without the quotes, the colon expands the variable, but not into a
string function body:
====================================================================
d=# CREATE FUNCTION yadda_yadda() returns text language plpythonu AS
:yadda;
ERROR: syntax error at or near "print"
LINE 2: print 'hello world';
====================================================================
Just for completeness and blind optimism let's try putting string
delimiters on the outside. The command succeeds but with the wrong
effect.
====================================================================
d=# CREATE OR REPLACE FUNCTION yadda_yadda() returns text language plpythonu AS
$$:yadda$$;
CREATE FUNCTION
cepheids=# \df+ yadda_yadda
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source
code| Description
-----------+-------------+------------------+---------------------+--------+------------+----------+-----------+-------------+-------------
astronomy | yadda_yadda | text | | normal | volatile | cepheids | plpythonu | :yadda
|
====================================================================
Please explain.
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -
SGVsbG8KCnByb2JhYmx5IHlvdSBuZWVkIGEgdGhpcmQgZm9ybSBvZiBleHBh bnNpb24gLSBub3QgaW1wbGVtZW50ZWQgeWV0CgoiOiQkdmFyJCQKCmVzY2Fw aW5nIDoneHh4JyBpcyBkZXNpZ25lZCBmb3IgU1FMIGxhbmd1YWdlLCBub3Qg Zm9yIFB5dGhvbiA6KAoKUmVnYXJkcwoKUGF2ZWwKCjIwMTEvMi8yIFN0ZXZl IFdoaXRlIDxzd2hpdGVAYWlwLmRlPjoKPiBIaSwgUm9iZXJ0LAo+Cj4gT24g wqAxLjAyLjExLCBSb2JlcnQgSGFhcyB3cm90ZToKPj4KPj4gQ2FuJ3QgeW91 IGFscmVhZHkgZG8gaXQgdGhpcyB3YXk6Cj4+Cj4+IFxzZXQgeWFkZGEgYGNh dCB5YWRkYV95YWRkYS5weWAKPj4gQ1JFQVRFIEZVTkNUSU9OIHlhZGRhX3lh ZGRhKCkgcmV0dXJucyB0ZXh0IGxhbmd1YWdlIHBscHl0aG9udSBBUwo+PiA6 J3lhZGRhJzsKPj4KPj4gSSBndWVzcyBpdCBwcm9iYWJseSB3b24ndCB3b3Jr IG9uIFdpbmRvd3MuLi4KPj4KPiBUaGlzIHdvdWxkIGFsc28gc2F0aXNmeSBt eSBpbW1lZGlhdGUgbmVlZHMuLi4KPiBpZiBvbmx5IEkgY291bGQgZ2V0IGl0 IHRvIHdvcmsuCj4KPiBJIG1hZGUgYSBmaWxlICd5YWRkYV95YWRkYS5weScg Y29udGFpbmluZyBvbmx5IHRoZSBsaW5lOgo+IHByaW50ICdoZWxsbyB3b3Js ZCcKPgo+ID09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09Cj4gZD0jIFxzZXQgeWFk ZGEgYGNhdCB5YWRkYV95YWRkYS5weWAKPiBkPSMgXGVjaG8gOnlhZGRhCj4g cHJpbnQgJ2hlbGxvIHdvcmxkJwo+ID09PT09PT09PT09PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09 Cj4KPiBTbyBmYXIsIHNvIGdvb2QuCj4KPiBCdXQgdGhlIDoneWFkZGEnOyBw cm9kdWNlcyBhbiBlcnJvci0taXQgc2VlbXMgdGhlIHZhcmlhYmxlIHlhZGRh IGlzbid0Cj4gZXhwYW5kZWQgaW4gdGhlIHByZXNlbmNlIG9mIHRoZSBxdW90 ZXMuCj4KPiA9PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PQo+IGQ9IyBDUkVBVEUg RlVOQ1RJT04geWFkZGFfeWFkZGEoKSByZXR1cm5zIHRleHQgbGFuZ3VhZ2Ug cGxweXRob251IEFTCj4gOid5YWRkYSc7Cj4gRVJST1I6IMKgc3ludGF4IGVy cm9yIGF0IG9yIG5lYXIgIjoiCj4gTElORSAyOiA6J3lhZGRhJzsKPiA9PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PQo+Cj4gV2l0aG91dCB0aGUgcXVvdGVzLCB0 aGUgY29sb24gZXhwYW5kcyB0aGUgdmFyaWFibGUsIGJ1dCBub3QgaW50byBh Cj4gc3RyaW5nIGZ1bmN0aW9uIGJvZHk6Cj4KPiA9PT09PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09 PT09PT09PQo+IGQ9IyBDUkVBVEUgRlVOQ1RJT04geWFkZGFfeWFkZGEoKSBy ZXR1cm5zIHRleHQgbGFuZ3VhZ2UgcGxweXRob251IEFTCj4gOnlhZGRhOwo+ IEVSUk9SOiDCoHN5bnRheCBlcnJvciBhdCBvciBuZWFyICJwcmludCIKPiBM SU5FIDI6IHByaW50ICdoZWxsbyB3b3JsZCc7Cj4gPT09PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09 PT09PT09PT0KPgo+IEp1c3QgZm9yIGNvbXBsZXRlbmVzcyBhbmQgYmxpbmQg b3B0aW1pc20gbGV0J3MgdHJ5IHB1dHRpbmcgc3RyaW5nCj4gZGVsaW1pdGVy cyBvbiB0aGUgb3V0c2lkZS4gwqBUaGUgY29tbWFuZCBzdWNjZWVkcyBidXQg d2l0aCB0aGUgd3JvbmcKPiBlZmZlY3QuCj4KPiA9PT09PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09 PT09PT09PQo+IGQ9IyBDUkVBVEUgT1IgUkVQTEFDRSBGVU5DVElPTiB5YWRk YV95YWRkYSgpIHJldHVybnMgdGV4dCBsYW5ndWFnZSBwbHB5dGhvbnUgQVMK PiAkJDp5YWRkYSQkOwo+IENSRUFURSBGVU5DVElPTgo+IGNlcGhlaWRzPSMg XGRmKyB5YWRkYV95YWRkYQo+IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg IMKgIMKgIMKgIMKgIMKgIMKgIExpc3Qgb2YgZnVuY3Rpb25zCj4gwqBTY2hl bWEgwqAgfCDCoCDCoE5hbWUgwqAgwqAgfCBSZXN1bHQgZGF0YSB0eXBlIHwg QXJndW1lbnQgZGF0YSB0eXBlcyB8IMKgVHlwZSDCoHwgVm9sYXRpbGl0eSB8 IMKgT3duZXIgwqAgfCBMYW5ndWFnZSDCoHwgU291cmNlIGNvZGUgfCBEZXNj cmlwdGlvbgo+IC0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0rLS0tLS0tLS0t LS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLSstLS0t LS0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0t Ky0tLS0tLS0tLS0tLS0KPiDCoGFzdHJvbm9teSB8IHlhZGRhX3lhZGRhIHwg dGV4dCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIMKgIMKg IMKgIMKgIMKgIHwgbm9ybWFsIHwgdm9sYXRpbGUgwqAgfCBjZXBoZWlkcyB8 IHBscHl0aG9udSB8IDp5YWRkYSDCoCDCoCDCoHwKPiA9PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09 PT09PT09PT09PQo+Cj4KPgo+IFBsZWFzZSBleHBsYWluLgo+Cj4gLS0KPiB8 IC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKg LSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAt IMKgLSDCoC0KPiB8IFN0ZXZlIFdoaXRlIMKgIMKgIMKgIMKgIMKgIMKgIMKg IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg ICs0OSgzMzEpNzQ5OS0yMDIKPiB8IEUtU2NpZW5jZSDCoCDCoCDCoCDCoCDC oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC oFppLiAyNyDCoFZpbGxhIFR1cmJ1bGVuego+IHwgLSDCoC0gwqAtIMKgLSDC oC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKg LSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKgLQo+IHwgQXN0 cm9waHlzaWthbGlzY2hlcyBJbnN0aXR1dCBQb3RzZGFtIChBSVApCj4gfCBB biBkZXIgU3Rlcm53YXJ0ZSAxNiwgRC0xNDQ4MiBQb3RzZGFtCj4gfAo+IHwg Vm9yc3RhbmQ6IFByb2YuIERyLiBNYXR0aGlhcyBTdGVpbm1ldHosIFBldGVy IEEuIFN0b2x6Cj4gfAo+IHwgU3RpZnR1bmcgcHJpdmF0ZW4gUmVjaHRzLCBT dGlmdHVuZ3N2ZXJ6ZWljaG5pcyBCcmFuZGVuYnVyZzogSUlJLzctNzEtMDI2 Cj4gfCAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKg LSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAtIMKgLSDCoC0gwqAt IMKgLSDCoC0gwqAtCj4KPiAtLQo+IFNlbnQgdmlhIHBnc3FsLWJ1Z3MgbWFp bGluZyBsaXN0IChwZ3NxbC1idWdzQHBvc3RncmVzcWwub3JnKQo+IFRvIG1h a2UgY2hhbmdlcyB0byB5b3VyIHN1YnNjcmlwdGlvbjoKPiBodHRwOi8vd3d3 LnBvc3RncmVzcWwub3JnL21haWxwcmVmL3Bnc3FsLWJ1Z3MKPgo=
Steve White <swhite@aip.de> writes:
> But the :'yadda'; produces an error--it seems the variable yadda isn't
> expanded in the presence of the quotes.
Apparently you're using a pre-9.0 psql.
regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> writes:
> probably you need a third form of expansion - not implemented yet
> ":$$var$$
Seems quite useless. A string literal is a string literal.
regards, tom lane
2011/2/2 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> probably you need a third form of expansion - not implemented yet >> ":$$var$$ > > Seems quite useless. =C2=A0A string literal is a string literal. > I don't propose this form now. I saying so this form can be usefull for Ste= ve. It appends a started and ending tags, but it does not a double or single quotes escaping. I still don't think so Steve's idea is good. It does very unstable mix Pavel > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0regards, tom lane >
On 2011-02-01, Steve White <swhite@aip.de> wrote: > Hi > > I asked on pgsql-general 31 Jan 2011 if there were a way to do this, and got > no response, so let's make it a feature request. > > It would be really nice to have a way to load script (especially Python > and Perl) from a separate file into a function body. Some advantages would > be: to run a code checker outside of Postgresql, and to make things easier > for source code colorizers. > > I have in mind syntax something like> >================================================ > CREATE OR REPLACE FUNCTION > myfunc( ... ) > RETURNS VOID FROM 'ScriptFile.py' LANGUAGE PLPYTHONU; >================================================ others have complained about the backend reading files maybe this coould be implemented in PSQL instead. (like \i and \copy are...) something like: \CREATE OR REPLACE FUNCTION myfunc( ... ) RETURNS VOID FROM 'ScriptFile.py' LANGUAGE PLPYTHONU psql would then need to slurp the file and quote the function body, but pq_escape_string_conn is presumably upto that task. on the other hand binary fuunctions (eg C) are read from files, but not directly by the backend, dlopen (or equivalent) is used instead. on the other hand, for symmetry I guess a form that matches yours is needed too, but, if using named files that will probably need database superuser permission (like the other named file functions do). Then ther could be an unpriviledged "from stdin" variant that psql could use to send the content (instead of quoting it and sending it in-line). ISTR C functions need superuser too.
On 2011-02-02, Steve White <swhite@aip.de> wrote: > Hi, Robert, > > I made a file 'yadda_yadda.py' containing only the line: > print 'hello world' > >==================================================================== > d=# \set yadda `cat yadda_yadda.py` > d=# \echo :yadda > print 'hello world' >==================================================================== > But the :'yadda'; produces an error--it seems the variable yadda isn't > expanded in the presence of the quotes. yes, \set doesn't quote content \set yadda '''' `sed -e "s/'/''/g" -e 's/\\/\\\\/g' < yadda_yadda.py` '''' CREATE FUNCTION yadda_yadda() returns text language plpythonu AS E:yadda; this is not much help for windows users though. BTW I found that sed command on the psql man page. -- ââ 100% natural
On 2011-02-01, Steve White <swhite@aip.de> wrote: > Hi Tom, > > I already agreed to Kevin's proposed syntax, and it is better than > my suggestion, but mine isn't quite as crazy as you make out. > > On 1.02.11, Tom Lane wrote: >> Steve White <swhite@aip.de> writes: >> > Try this instead: >> >> > ================================================ >> > CREATE OR REPLACE FUNCTION >> > myfunc( ... ) >> > RETURNS VOID AS '#PGSQL_IMPORT filename' LANGUAGE PLPYTHONU; >> > ================================================ >> >> I think having psql decide that string literals mean something other >> than their face value is Right Out --- it would bite you on the rear >> just when you least expect it. > > Interesting idea... but why would psql make this decision? > Did somebody suggest that off-line? > > I said that the script interpreter might do this... if the interpreter does it you stop ordinary users from using it for security reasons, -- ââ 100% natural