Thread: Feature request: include script file into function body

Feature request: include script file into function body

From
Steve White
Date:
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
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

Re: Feature request: include script file into function body

From
Tom Lane
Date:
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

Re: Feature request: include script file into function body

From
Steve White
Date:
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
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

Re: Feature request: include script file into function body

From
Pavel Stehule
Date:
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
>

Re: Feature request: include script file into function body

From
"Kevin Grittner"
Date:
[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

Re: Feature request: include script file into function body

From
Steve White
Date:
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
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

Re: Feature request: include script file into function body

From
Steve White
Date:
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
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

Re: Feature request: include script file into function body

From
Bruce Momjian
Date:
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. +

Re: Feature request: include script file into function body

From
Steve White
Date:
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
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

Re: Feature request: include script file into function body

From
"Kevin Grittner"
Date:
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

Re: Feature request: include script file into function body (better syntax)

From
Steve White
Date:
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
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

Re: Feature request: include script file into function body

From
"Kevin Grittner"
Date:
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

Re: Feature request: include script file into function body

From
Gary Doades
Date:
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.

Re: Feature request: include script file into function body

From
Tom Lane
Date:
"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

Re: Feature request: include script file into function body

From
"Kevin Grittner"
Date:
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

Re: Feature request: include script file into function body

From
Tom Lane
Date:
"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

Re: Feature request: include script file into function body (better syntax)

From
Tom Lane
Date:
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

Re: Feature request: include script file into function body

From
Tom Lane
Date:
"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

Re: Feature request: include script file into function body

From
Steve White
Date:
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
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

Re: Feature request: include script file into function body (better syntax)

From
Steve White
Date:
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

Re: Feature request: include script file into function body

From
"Kevin Grittner"
Date:
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

Re: Feature request: include script file into function body

From
Robert Haas
Date:
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

Re: Feature request: include script file into function body

From
"Kevin Grittner"
Date:
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

Re: Feature request: include script file into function body

From
Tom Lane
Date:
"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

Re: Feature request: include script file into function body

From
Steve White
Date:
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
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

Re: Feature request: include script file into function body

From
Pavel Stehule
Date:
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=

Re: Feature request: include script file into function body

From
Tom Lane
Date:
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

Re: Feature request: include script file into function body

From
Tom Lane
Date:
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

Re: Feature request: include script file into function body

From
Pavel Stehule
Date:
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
>

Re: Feature request: include script file into function body

From
Jasen Betts
Date:
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.

Re: Feature request: include script file into function body

From
Jasen Betts
Date:
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

Re: Feature request: include script file into function body (better syntax)

From
Jasen Betts
Date:
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