Re: [BUGS] Feature request: include script file into function body

[Tom Lane]

"Kevin Grittner"  writes:
> 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';
 
> Clever, but not pretty.

Yeah.  So the question is whether this is useful enough to justify
providing a shortcut.

regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Kevin Grittner]

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';
 
OK, that works.  It sure ain't pretty, though.
 
Clever, but not pretty.
 
-Kevin

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Robert Haas]

On Tue, Feb 1, 2011 at 3:09 PM, Kevin Grittner
 wrote:
> Steve White  wrote:
>> On  1.02.11, Tom Lane wrote:
>>> "Kevin Grittner"  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Kevin Grittner]

Steve White  wrote:
> On  1.02.11, Tom Lane wrote:
>> "Kevin Grittner"  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

[Kevin Grittner]

Steve White  wrote:
 
> I said that the script interpreter might do this...
 
The script interpreter runs on the server.
 
-Kevin

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

[Steve White]

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

Re: [BUGS] Feature request: include script file into function body

[Steve White]

Hi guys,


On  1.02.11, Tom Lane wrote:
> "Kevin Grittner"  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-ScienceZi. 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

Re: [BUGS] Feature request: include script file into function body

[Tom Lane]

"Kevin Grittner"  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

[Tom Lane]

Steve White  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Tom Lane]

"Kevin Grittner"  writes:
> Tom Lane  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Kevin Grittner]

Tom Lane  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Tom Lane]

"Kevin Grittner"  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Gary Doades]

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  wrote:

On  1.02.11, Tom Lane wrote:

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


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Kevin Grittner]

Steve White  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

[Steve White]

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  wrote: 
> > > On  1.02.11, Tom Lane wrote:
> > >> Steve White  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-ScienceZi. 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

Re: [BUGS] Feature request: include script file into function body

[Kevin Grittner]

Steve White  wrote:
> On  1.02.11, Kevin Grittner wrote:
>> Steve White  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Steve White]

Hi again, all,

OK I think I now know what the misunderstanding is.

> [Please don't top-post.  Rearranged for clarity.]
> 
> Steve White  wrote: 
> > On  1.02.11, Tom Lane wrote:
> >> Steve White  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-ScienceZi. 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

Re: [BUGS] BUG #5859: XML result in line and column

[Tom Lane]

"Alexandre"  writes:
> SELECT * FROM
> xpath_table('sequencia',
> 'registro',
> 'auditoria_anterior',
> 'name(/ROOT/CLIENTES/*)|/ROOT/CLIENTES/*',
> 'sequencia = 1')
> AS t(sequencia integer, coluna text, valor text)

> --Incorrect result---
> sequencia   colunavalor
> integer text  text
> --
> 1   CGC_CPF_CLIENTE   12345678901
> 1   CGC_CPF_CLIENTE   1
> 1   CGC_CPF_CLIENTE   F
> 1   CGC_CPF_CLIENTE   JOAO DA SILVA


> --Result that would be right to be presented--
> sequencia   colunavalor
> integer text  text
> --
> 1   CGC_CPF_CLIENTE   12345678901
> 1   TIPO  1
> 1   PESSOAF
> 1   NOME  JOAO DA SILVA

I can reproduce this on Fedora 13 (with libxml2-2.7.7-1.fc13.x86_64).
I traced through it and find that libxml returns a simple XPATH_STRING
result for the name() query, whereas the second query returns an
XPATH_NODESET containing the four values shown.  So either this behavior
is correct, or it's a libxml bug not ours.  I don't know XPath well
enough to know whether name() ought to return more than one value here.

Note that there are some other pretty serious known issues in this
same area, see
http://archives.postgresql.org/pgsql-hackers/2010-02/msg02424.php
Overall it seems like the libxml API is not designed to handle multiple
parallel queries, or maybe we just don't know how to use it for that.

regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Bruce Momjian]

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  wrote: 
> > > On  1.02.11, Tom Lane wrote:
> > >> Steve White  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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Steve White]

Hi Kevin,

On  1.02.11, Kevin Grittner wrote:
> [Please don't top-post.  Rearranged for clarity.]
> 
As you like.

> Steve White  wrote: 
> > On  1.02.11, Tom Lane wrote:
> >> Steve White  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-ScienceZi. 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

Re: [BUGS] Feature request: include script file into function body

[Steve White]

Hi Pavel,

On  1.02.11, Pavel Stehule wrote:
> Hello
> 
> 2011/2/1 Steve White :
> > 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  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-ScienceZi. 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

Re: [BUGS] Feature request: include script file into function body

[Kevin Grittner]

[Please don't top-post.  Rearranged for clarity.]

Steve White  wrote: 
> On  1.02.11, Tom Lane wrote:
>> Steve White  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Pavel Stehule]

Hello

2011/2/1 Steve White :
> 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  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
>

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Steve White]

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

Re: [BUGS] pg_dump doesn't save altered column information for inherited columns

[Tom Lane]

 writes:
> I noticed that when pg_dump saves SQL code for a table with inheritance, it 
> does not save any information about inherited columns. This is fine when 
> inherited columns do not undergo any modification, but when they do, that 
> information is lost.

> Example:

> create table parent (id integer not null);

> create table child (value integer) inherits (parent);

> alter table child alter column id drop not null;

Actually, the bug here is that ALTER TABLE lets you do that.  Dropping
an inherited constraint should be disallowed, and is disallowed for the
case of CHECK constraints.  We haven't gotten around to enhancing the
NOT NULL infrastructure to detect that, but it's on the TODO list.
In the meantime, there's no point in modifying pg_dump to worry about
such cases.

regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #5859: XML result in line and column

[Alexandre]

The following bug has been logged online:

Bug reference:  5859
Logged by:  Alexandre
Email address:  ultr...@gmail.com
PostgreSQL version: 8.4.7
Operating system:   Windows 7
Description:XML result in line and column
Details: 

The name of the xml tag is not being updated, becoming the first name
registry for all. Here is example:

Create table AUDITORIA_ANTERIOR
(
SEQUENCIA Serial NOT NULL,
SEQUENCIA_AUDITORIA Integer NOT NULL,
REGISTRO Text NOT NULL,
primary key (SEQUENCIA,SEQUENCIA_AUDITORIA)
);
 
insert into auditoria_anterior (sequencia_auditoria, registro)
values (1, '
123456789011FJOAO DA
SILVA ')
 
SELECT * FROM
xpath_table('sequencia',
'registro',
'auditoria_anterior',
'name(/ROOT/CLIENTES/*)|/ROOT/CLIENTES/*',
'sequencia = 1')
AS t(sequencia integer, coluna text, valor text)

--Incorrect result---
sequencia   colunavalor
integer text  text
--
1   CGC_CPF_CLIENTE   12345678901
1   CGC_CPF_CLIENTE   1
1   CGC_CPF_CLIENTE   F
1   CGC_CPF_CLIENTE   JOAO DA SILVA


--Result that would be right to be presented--
sequencia   colunavalor
integer text  text
--
1   CGC_CPF_CLIENTE   12345678901
1   TIPO  1
1   PESSOAF
1   NOME  JOAO DA SILVA

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Feature request: include script file into function body

[Tom Lane]

Steve White  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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] Portable, simple means of determining distribution directory

[Steve White]

Hi,

This was discussed on pgsql-general 28 Jan 2011.

It would be advantageous to have a portable, simple means of including
a module from the distribution contrib/ directory.

One idea would be some kind of variable expansion with the \i command:
\i $libdir/contrib/module.sql
Another would be a special command for loading modules, maybe
loadmod( modname )
There should of course be some reasonable means of informing a user 
that these have failed to find the desired module.

Note that several options were discussed.  One could work, but is quite
messy, requiring special installations and code.  

Also, this proposal is not an alternative to a full module system as 
discussed in
http://wiki.postgresql.org/wiki/Module_Manager
(although it might facilitate development of such a thing.)

Cheers!


-- 
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
| Steve White +49(331)7499-202
| E-ScienceZi. 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

[BUGS] Feature request: include script file into function body

[Steve White]

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

[BUGS] pg_dump doesn't save altered column information for inherited columns

[depstein]

Hello,

I noticed that when pg_dump saves SQL code for a table with inheritance, it 
does not save any information about inherited columns. This is fine when 
inherited columns do not undergo any modification, but when they do, that 
information is lost.

Example:

create table parent (id integer not null);

create table child (value integer) inherits (parent);

alter table child alter column id drop not null;

insert into child values (null, 1);

Here is the code of the child table as generated by pg_dump:

CREATE TABLE child (
value integer
)
INHERITS (parent);

When the table is recreated using this code, the id column has the NOT NULL 
restriction, which was not present in the original.

I don't think this is right. Either inherited columns should be immune to 
changes, or else pg_dump should reflect those changes in its SQL code.

Dmitry

Dmitry Epstein | Developer
Allied Testing
T + 7 495 544 48 69 Ext 417
M + 7 926 215 73 36

www.alliedtesting.com
We Deliver Quality.