proposal - plpgsql: execute using into
Hello
Current EXECUTE statemtn doesn't support other way for parametrisation than
concating strings. It works well but it's little bit unreadable. Oracle's
statement EXECUTE has positional replacement feature. It works similar our
RAISE statement (when position holder is %). EXECUTE position holder has
form :xxxx. xxxx has only symbolic value and isn't used for anything. Syntax
of enhanced statements is:
EXECUTE 'format string' USING expr_list
There are some problems about replacing string values in the SQL string.
Sometimes we have to enclose value between spaces or others symbols
(apostrophe or double apostrophe), sometimes not. Possible rules:
a) if position holder is inside string or identifier we don't enclose
value;
b) else numeric values are enclosed spaces and others (non regclass)
single apostrophes
c) regclass's values are enclosed douple apostrophes.
PL/pgSQL knows three dynamic statements. All will be enhanced.
Some examples:
EXECUTE 'SELECT :name||:sp||:surname' USING 'Pavel',' ','Stehule';
EXECUTE e'SELECT \':name :surname' USING 'Pavel','Stehule';
EXECUTE 'SELECT * FROM :tabname' USING 'xb'::regclass;
EXECUTE 'SELECT * FROM ":base:num" USING 'mytab',1;
You can test it. I sent patch to pg_patches.
I invite any comments
Pavel Stehule
_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/
"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
Current EXECUTE statemtn doesn't support other way for parametrisation than
concating strings. It works well but it's little bit unreadable. Oracle's
statement EXECUTE has positional replacement feature.
...
There are some problems about replacing string values in the SQL string.
Doesn't the Oracle implementation already imply a solution to that?
The examples you give look to me like they are escaping problems waiting
to happen, especially in view of the upcoming change in default
backslash behavior, so this whole thing makes me feel pretty nervous.
I think we'd be best off to leave EXECUTE alone, at least until we've
converged to the point where almost nobody is using non-standard-compliant
strings.
regards, tom lane
There are some problems about replacing string values in the SQL string.
Doesn't the Oracle implementation already imply a solution to that?
I don't know. I didn't find any detail documentation about it. I don't know
what Oracle exactly do.
I think we'd be best off to leave EXECUTE alone, at least until we've
converged to the point where almost nobody is using non-standard-compliant
strings.
Maybe, but patch have to solve SQL string and non SQL strings too
Regards
Pavel Stehule
_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
Pavel Stehule wrote:
There are some problems about replacing string values in the SQL string.
Doesn't the Oracle implementation already imply a solution to that?
I don't know. I didn't find any detail documentation about it. I don't know
what Oracle exactly do.I think we'd be best off to leave EXECUTE alone, at least until we've
converged to the point where almost nobody is using non-standard-compliant
strings.Maybe, but patch have to solve SQL string and non SQL strings too
Pavel, I am still confused what the USING clause is for, and you need to
research how Oracle handles it before we can add this. Would you
provide an example of its usage?
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Pavel Stehule wrote:
There are some problems about replacing string values in the SQL string.
Doesn't the Oracle implementation already imply a solution to that?
I don't know. I didn't find any detail documentation about it. I don't know
what Oracle exactly do.
Oracle does use USING:
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];
so I think we are OK there.
I think we'd be best off to leave EXECUTE alone, at least until we've
converged to the point where almost nobody is using non-standard-compliant
strings.Maybe, but patch have to solve SQL string and non SQL strings too
The only case I see you using it is for \:. What is the purpose of
that? Can't we use :: for a literal :?
I have attached the patch from March.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachments:
execute_using.diftext/x-diffDownload+422-191
Hello,
This task can be better solved. There are some problems with strings, but
bigger problem is impossibility to pass nonscalar variables. What is
questions? Does Oracle allow variables on nonparam positions? If not, then I
see more elegant solution via preprocessed statements.
Best regards
Pavel Stehule
Pavel Stehule wrote:
There are some problems about replacing string values in the SQL
string.
Doesn't the Oracle implementation already imply a solution to that?
I don't know. I didn't find any detail documentation about it. I don't
know
what Oracle exactly do.
Oracle does use USING:
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];so I think we are OK there.
I think we'd be best off to leave EXECUTE alone, at least until we've
converged to the point where almost nobody is usingnon-standard-compliant
strings.
Maybe, but patch have to solve SQL string and non SQL strings too
The only case I see you using it is for \:. What is the purpose of
that? Can't we use :: for a literal :?I have attached the patch from March.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
<< execute_using.dif >>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
So the patch is being withdrawn by the author? OK.
---------------------------------------------------------------------------
Pavel Stehule wrote:
Hello,
This task can be better solved. There are some problems with strings, but
bigger problem is impossibility to pass nonscalar variables. What is
questions? Does Oracle allow variables on nonparam positions? If not, then I
see more elegant solution via preprocessed statements.Best regards
Pavel StehulePavel Stehule wrote:
There are some problems about replacing string values in the SQL
string.
Doesn't the Oracle implementation already imply a solution to that?
I don't know. I didn't find any detail documentation about it. I don't
know
what Oracle exactly do.
Oracle does use USING:
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];so I think we are OK there.
I think we'd be best off to leave EXECUTE alone, at least until we've
converged to the point where almost nobody is usingnon-standard-compliant
strings.
Maybe, but patch have to solve SQL string and non SQL strings too
The only case I see you using it is for \:. What is the purpose of
that? Can't we use :: for a literal :?I have attached the patch from March.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
<< execute_using.dif >>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +