Re: 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(at)aip(dot)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(at)aip(dot)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(at)aip(dot)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?

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 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(at)aip(dot)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(at)postgresql(dot)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(at)aip(dot)de> wrote:
> On 1.02.11, Tom Lane wrote:
>> Steve White <swhite(at)aip(dot)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(at)aip(dot)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(at)aip(dot)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(at)postgresql(dot)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(at)aip(dot)de> wrote:
> > On 1.02.11, Tom Lane wrote:
> >> Steve White <swhite(at)aip(dot)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(at)aip(dot)de> wrote:
> > > On 1.02.11, Tom Lane wrote:
> > >> Steve White <swhite(at)aip(dot)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(at)momjian(dot)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(at)aip(dot)de> wrote:
> > On 1.02.11, Tom Lane wrote:
> >> Steve White <swhite(at)aip(dot)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(at)aip(dot)de> wrote:
> On 1.02.11, Kevin Grittner wrote:
>> Steve White <swhite(at)aip(dot)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

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(at)aip(dot)de> wrote:
>>> On 1.02.11, Tom Lane wrote:
>>>> Steve White<swhite(at)aip(dot)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(dot)Grittner(at)wicourts(dot)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

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(at)aip(dot)de> wrote:
> > > On 1.02.11, Tom Lane wrote:
> > >> Steve White <swhite(at)aip(dot)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(at)aip(dot)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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)aip(dot)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(dot)Grittner(at)wicourts(dot)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(dot)Grittner(at)wicourts(dot)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(at)aip(dot)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
| - - - - - - - - - - - - - - - - - - - - - - - - -

Steve White <swhite(at)aip(dot)de> wrote:

> I said that the script interpreter might do this...

The script interpreter runs on the server.

-Kevin

Steve White <swhite(at)aip(dot)de> wrote:
> On 1.02.11, Tom Lane wrote:
>> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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(dot)Grittner(at)wicourts(dot)gov> wrote:
> Steve White <swhite(at)aip(dot)de> wrote:
>> On  1.02.11, Tom Lane wrote:
>>> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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.

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

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)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(dot)Grittner(at)wicourts(dot)gov> writes:
> Robert Haas <robertmhaas(at)gmail(dot)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
| - - - - - - - - - - - - - - - - - - - - - - - - -

Hello

probably you need a third form of expansion - not implemented yet

":$$var$$

escaping :'xxx' is designed for SQL language, not for Python :(

Regards

Pavel

2011/2/2 Steve White <swhite(at)aip(dot)de>:
> 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
> | -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Steve White <swhite(at)aip(dot)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(dot)stehule(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> probably you need a third form of expansion - not implemented yet
>> ":$$var$$
>
> Seems quite useless.  A string literal is a string literal.
>

I don't propose this form now. I saying so this form can be usefull for Steve.

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

>                        regards, tom lane
>

On 2011-02-01, Steve White <swhite(at)aip(dot)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(at)aip(dot)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(at)aip(dot)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(at)aip(dot)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