massive quotes?

Andrew Dunstan <andrew@dunslane.net> writes:

What is the state of things regarding having to use massive strings of
quotes like this (taken from the 7.3 docs)?:

How much of that is already replaceable by the tactic of using
quote_literal() wherever possible? It's safest to be using
quote_literal anyway wherever you're interpolating a string value
into a query.

regards, tom lane

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

What is the state of things regarding having to use massive strings of
quotes like this (taken from the 7.3 docs)?:

How much of that is already replaceable by the tactic of using
quote_literal() wherever possible? It's safest to be using
quote_literal anyway wherever you're interpolating a string value
into a query.

I suspect to get something nice it would have to operate at the
scanner/parser layer.

cheers

andrew

On Thu, 28 Aug 2003, Andrew Dunstan wrote:

What is the state of things regarding having to use massive strings of
quotes like this (taken from the 7.3 docs)?:

a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;'';

This is truly ugly, IMNSHO. Perl has its q() construct - any chance of
us doing something here?

I'm prepared to put in effort to implement a solution if there is
agreement on what the solution should be. Maybe some sort of magical
operator/function?

I was thinking the most natural thing would be to use something similar to
COPY's stdin quoting:

CREATE FUNCTION bob() RETURNS INTEGER AS stdin LANGUAGE 'plpgsql';
BEGIN
...
END;
\.

Another possibility would be shell/Perl-style here documents, like this:

CREATE FUNCTION bob() RETURNS INTEGER AS <<EOF
BEGIN
...
END;
EOF
LANGUAGE 'plpgsql';

The former seems preferable since it uses a syntax PostgreSQL already
supports in another context.

Jon

Jon Jensen wrote:

On Thu, 28 Aug 2003, Andrew Dunstan wrote:

What is the state of things regarding having to use massive strings of
quotes like this (taken from the 7.3 docs)?:

a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;'';

This is truly ugly, IMNSHO. Perl has its q() construct - any chance of
us doing something here?

I'm prepared to put in effort to implement a solution if there is
agreement on what the solution should be. Maybe some sort of magical
operator/function?

I was thinking the most natural thing would be to use something similar to
COPY's stdin quoting:

CREATE FUNCTION bob() RETURNS INTEGER AS stdin LANGUAGE 'plpgsql';
BEGIN
...
END;
\.

Another possibility would be shell/Perl-style here documents, like this:

CREATE FUNCTION bob() RETURNS INTEGER AS <<EOF
BEGIN
...
END;
EOF
LANGUAGE 'plpgsql';

The former seems preferable since it uses a syntax PostgreSQL already
supports in another context.

Jon

Nice idea. I would probably never have thought of it :-) Makes function
text almost first class, in the way that Oracle's is AFAICS,

I also prefer the first version, not least because it hoists the
LANGUAGE clause to the top where it seems to me it belongs.

I have no idea how hard this would be.

cheers

andrew

Is there a TODO here? The only problem I see is that it introduces the
idea of special column-1 handling, which we don't have right now, except
in COPY.

---------------------------------------------------------------------------

Andrew Dunstan wrote:

Jon Jensen wrote:

On Thu, 28 Aug 2003, Andrew Dunstan wrote:

What is the state of things regarding having to use massive strings of
quotes like this (taken from the 7.3 docs)?:

a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;'';

This is truly ugly, IMNSHO. Perl has its q() construct - any chance of
us doing something here?

I'm prepared to put in effort to implement a solution if there is
agreement on what the solution should be. Maybe some sort of magical
operator/function?

I was thinking the most natural thing would be to use something similar to
COPY's stdin quoting:

CREATE FUNCTION bob() RETURNS INTEGER AS stdin LANGUAGE 'plpgsql';
BEGIN
...
END;
\.

Another possibility would be shell/Perl-style here documents, like this:

CREATE FUNCTION bob() RETURNS INTEGER AS <<EOF
BEGIN
...
END;
EOF
LANGUAGE 'plpgsql';

The former seems preferable since it uses a syntax PostgreSQL already
supports in another context.

Jon

Nice idea. I would probably never have thought of it :-) Makes function
text almost first class, in the way that Oracle's is AFAICS,

I also prefer the first version, not least because it hoists the
LANGUAGE clause to the top where it seems to me it belongs.

I have no idea how hard this would be.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is there a TODO here? The only problem I see is that it introduces the
idea of special column-1 handling, which we don't have right now, except
in COPY.

I think the idea would be to execute what's effectively a COPY IN during
the CREATE FUNCTION command, and then use the data so collected as the
function body. It seems doable offhand, though we'd have to think about
whether this breaks any aspect of the FE/BE protocol, or whether it's
truly a user-friendly interface.

regards, tom lane

On Sat, 30 Aug 2003, Bruce Momjian wrote:

I was thinking the most natural thing would be to use something similar to
COPY's stdin quoting:

CREATE FUNCTION bob() RETURNS INTEGER AS stdin LANGUAGE 'plpgsql';
BEGIN
...
END;
\.

Is there a TODO here? The only problem I see is that it introduces the
idea of special column-1 handling, which we don't have right now, except
in COPY.

I wouldn't mind taking on this job for 7.5. Yes, I think it's worth adding
to the TODO list.

Jon

There's certainly a TODO, but I'm not yet 100% sure that the solution
Jon proposed is best. I'm looking at it as we write, and making it work
looks to be somewhat complex. If we go that road we would probably need
to generalise somewhat the COPY code.

The alternative in my mind is some function foo such that
foo(e) = 'e'

Comments are welcome - this is virgin ground for me :-)

andrew

Bruce Momjian wrote:

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Is there a TODO here? The only problem I see is that it introduces the
idea of special column-1 handling, which we don't have right now, except
in COPY.

I think the idea would be to execute what's effectively a COPY IN during
the CREATE FUNCTION command, and then use the data so collected as the
function body. It seems doable offhand, though we'd have to think about
whether this breaks any aspect of the FE/BE protocol, or whether it's
truly a user-friendly interface.

Wow, imagine libpq creating a function. Doesn't it just pass the
entire string to the server? How would COPY IN handle that?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

I think the idea would be to execute what's effectively a COPY IN during
the CREATE FUNCTION command, and then use the data so collected as the
function body. It seems doable offhand, though we'd have to think about
whether this breaks any aspect of the FE/BE protocol, or whether it's
truly a user-friendly interface.

Wow, imagine libpq creating a function. Doesn't it just pass the
entire string to the server? How would COPY IN handle that?

I don't think libpq would know the difference. It certainly has no idea
that a command that you send to the backend is a COPY --- it only finds
that out when the Start Copy In message comes back from the backend.
I believe the same is true of psql. So if a CREATE FUNCTION command
were to issue Start Copy In, both those layers would happily cooperate
in passing through everything you type up to "\.". Then we take that
pile of bits and use it as the function body string in the rest of
CREATE FUNCTION. (None of this need have anything to do with the
existing commands/copy.c backend code, either, though some small part
of the code might be worth sharing.)

Other client libraries such as jdbc might need more surgery, though,
if they have more smarts about COPY than libpq does.

I'm also wondering why the backend need have anything at all to do with
an improved function-definition mode. If you look in the archives you
will see speculation about inventing psql backslash commands that would
assist in entering raw function definitions by taking something you type
and suitably quotifying it. This seems to me to be a cleaner and more
localized approach to getting much the same functionality.

regards, tom lane

Tom Lane wrote:

I'm also wondering why the backend need have anything at all to do with
an improved function-definition mode. If you look in the archives you
will see speculation about inventing psql backslash commands that would
assist in entering raw function definitions by taking something you type
and suitably quotifying it. This seems to me to be a cleaner and more
localized approach to getting much the same functionality.

This would suit my needs, as I use psql to do my db setup. But it would
introduce a possible disconnect between psql and other interfaces, e.g.
pgadmin, wouldn't it? Might it not be better to do something that was at
least available to all clients, rather than make them all have to do
their own quote escaping?

Another thought: "create function foo() as stdin ..." reads a bit
strangely, to me. Perhaps "create function foo() from stdin ..." or
"create function foo() inline ..." might be better.

The thing that set me off on this track was that I was trying to analyse
a pure sql function's performance, so I was C&P'ing to and from my
definition file , and constantly having to escape/unescape quotes, and
it got damned annoying. I'm not dogmatic about how it is done, though,
merely keen to ensure *something* is done. If we are trying to impress
people with useability, this is one area where there's a gain to be
made, with luck at not much cost.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

I'm also wondering why the backend need have anything at all to do with
an improved function-definition mode. If you look in the archives you
will see speculation about inventing psql backslash commands that would
assist in entering raw function definitions by taking something you type
and suitably quotifying it.

This would suit my needs, as I use psql to do my db setup. But it would
introduce a possible disconnect between psql and other interfaces, e.g.
pgadmin, wouldn't it? Might it not be better to do something that was at
least available to all clients, rather than make them all have to do
their own quote escaping?

What makes you think that a COPY-based interface would be especially
convenient for other frontends? In my mind this is entirely a
user-interface matter, and as such is best solved at the user interface.
psql has one set of needs, but a GUI app has totally different ones.
I believe phpPgAdmin and so forth already have their own solutions to
the quoting problem, anyway.

regards, tom lane

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

I'm also wondering why the backend need have anything at all to do with
an improved function-definition mode. If you look in the archives you
will see speculation about inventing psql backslash commands that would
assist in entering raw function definitions by taking something you type
and suitably quotifying it.

This would suit my needs, as I use psql to do my db setup. But it would
introduce a possible disconnect between psql and other interfaces, e.g.
pgadmin, wouldn't it? Might it not be better to do something that was at
least available to all clients, rather than make them all have to do
their own quote escaping?

What makes you think that a COPY-based interface would be especially
convenient for other frontends? In my mind this is entirely a
user-interface matter, and as such is best solved at the user interface.
psql has one set of needs, but a GUI app has totally different ones.
I believe phpPgAdmin and so forth already have their own solutions to
the quoting problem, anyway.

I was speculating, that it might. But I'm quite prepared to accept that
it wouldn't, and go for a purely psql solution, preferably one that
pg_dump can understand and use.

In that case, though, the solution will presumably look at least a bit
different from those discussed so far in this thread. Or would you have
psql detect that in place of a string there was "stdin" or whatever and
then replace it with the inline string before passing it to the backend?

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

In that case, though, the solution will presumably look at least a bit
different from those discussed so far in this thread. Or would you have
psql detect that in place of a string there was "stdin" or whatever and
then replace it with the inline string before passing it to the backend?

Please see the archives. I think that what was being discussed was
something along the lines of

foo=> CREATE FUNCTION myfunc(...) RETURNS ... AS
foo=> \beginliteral
foo'> type my function definition here
foo'> and here
foo'> \endliteral
foo-> LANGUAGE plpgsql;

and psql would proceed to quotify whatever you entered between
the two backslash commands. (Notice this could be used for any
string-literal entry problem, not only CREATE FUNCTION.) I'm fuzzy on
the details though; this may not have been the best idea presented.

regards, tom lane

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

In that case, though, the solution will presumably look at least a bit
different from those discussed so far in this thread. Or would you have
psql detect that in place of a string there was "stdin" or whatever and
then replace it with the inline string before passing it to the backend?

Please see the archives. I think that what was being discussed was
something along the lines of

foo=> CREATE FUNCTION myfunc(...) RETURNS ... AS
foo=> \beginliteral
foo'> type my function definition here
foo'> and here
foo'> \endliteral
foo-> LANGUAGE plpgsql;

and psql would proceed to quotify whatever you entered between
the two backslash commands. (Notice this could be used for any
string-literal entry problem, not only CREATE FUNCTION.) I'm fuzzy on
the details though; this may not have been the best idea presented.

Let me jump in --- there is the issue of how to prevent the backend from
running the query through the lexer/parser. The cleanest idea presented
was:

CREATE FUNCTION bob() RETURNS INTEGER AS stdin LANGUAGE 'plpgsql';
BEGIN
...
END;
\.

The interesting thing I missed at first viewing was that there is a
semicolon after the first line. This allows the backend to go into a
COPY-like mode where the client can pass lines to the backend bypassing
the lexer/parser.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian said:

Let me jump in --- there is the issue of how to prevent the backend
from running the query through the lexer/parser. The cleanest idea
presented was:

CREATE FUNCTION bob() RETURNS INTEGER AS stdin LANGUAGE 'plpgsql';
BEGIN
...
END;
\.

The interesting thing I missed at first viewing was that there is a
semicolon after the first line. This allows the backend to go into a
COPY-like mode where the client can pass lines to the backend bypassing
the lexer/parser.

If it is all processed in the front end as Tom is suggesting, the backend
wouldn't see anything different from what it does now.

That said, I rather like this syntax.

cheers

andrew

Andrew Dunstan wrote:

Bruce Momjian said:

Let me jump in --- there is the issue of how to prevent the backend
from running the query through the lexer/parser. The cleanest idea
presented was:

CREATE FUNCTION bob() RETURNS INTEGER AS stdin LANGUAGE 'plpgsql';
BEGIN
...
END;
\.

The interesting thing I missed at first viewing was that there is a
semicolon after the first line. This allows the backend to go into a
COPY-like mode where the client can pass lines to the backend bypassing
the lexer/parser.

If it is all processed in the front end as Tom is suggesting, the backend
wouldn't see anything different from what it does now.

That said, I rather like this syntax.

I don't think we want to push this into each client unless there is no
other solution. Seems the libpq copy API could be utilized to handle
this cleanly.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Let me jump in --- there is the issue of how to prevent the backend from
running the query through the lexer/parser. The cleanest idea presented
was:
[snip]

That's the same idea that was being discussed in this thread. I was
trying to remind people of the existence of prior discussion with
different (and IMHO better) ideas.

The interesting thing I missed at first viewing was that there is a
semicolon after the first line. This allows the backend to go into a
COPY-like mode where the client can pass lines to the backend bypassing
the lexer/parser.

What I don't like about this scheme is that it requires mods on both the
backend and client sides, to solve a problem that could be solved as
well or better (and definitely more simply) on the client side alone.
People are being misled by considering only psql, which is so stupid
that it might actually not need any change to be used with a COPY-based
function definition mode. I doubt that is true of any other client.
For a client that actually understands it's doing a function definition,
this is *not* simpler nor better. Instead of running a
string-literal-quotify transformation on the function text (which you
must admit is trivial), the client has to support switching into the
COPY protocol. That's not simpler. Add in any meaningful error
recovery (what do you do if the backend doesn't switch into COPY mode?)
and it's substantially more complex.

regards, tom lane

These approaches are to some extent orthogonal, ISTM. Maybe the best
solution would be to do as Tom suggests and just change psql (and, I
would add, pg_dump) to act nicely here, and if other interface
users/authors need/want something along these lines re-examine the
issue. I use JDBC for my web clients in my current project, but I
wouldn't create a function in the client. I probably wouldn't using
DBI/DBD:Pg either. So taking myself as a sample of one perhaps it is
true that it's only needed in psql :-)

I'm all for minimal solutions as long as they look nice.

cheers

andrew

Tom Lane wrote: