Re: [PATCHES] [HACKERS] proposal - plpgsql: execute using
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 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 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 [EMAIL PROTECTED]
> > EnterpriseDBhttp://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?
> >
> >http://www.postgresql.org/docs/faq
>
> _
> Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] proposal - plpgsql: execute using into
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 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 [EMAIL PROTECTED]
EnterpriseDBhttp://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?
http://www.postgresql.org/docs/faq
_
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] proposal - plpgsql: execute using into
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 [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
*** ./src/pl/plpgsql/src/gram.y.orig 2006-03-26 09:53:27.0 +0200
--- ./src/pl/plpgsql/src/gram.y 2006-03-27 20:51:50.0 +0200
***
*** 20,25
--- 20,26
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***
*** 187,192
--- 188,194
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_USING
%token K_WARNING
%token K_WHEN
%token K_WHILE
***
*** 858,869
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
! expr = plpgsql_read_expression(K_LOOP, "LOOP");
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new->cmd_type = PLPGSQL_STMT_DYNFORS;
new->lineno = $1;
if ($2.rec)
{
new->rec = $2.rec;
--- 860,874
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
+ int term;
! expr = read_sql_construct(K_LOOP, K_USING, 0, "LOOP|USING", "SELECT ", true, true, &term);
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new->cmd_type = PLPGSQL_STMT_DYNFORS;
new->lineno = $1;
+ new->params = NULL;
+
if ($2.rec)
{
new->rec = $2.rec;
***
*** 886,891
--- 891,909
yyerror("loop variable of loop over rows must be a record or row variable or list of scalar variables");
}
new->query = expr;
+
+ if (term == K_USING)
+ {
+ for(;;)
+ {
+ expr = read_sql_construct(',', K_LOOP, 0, ", or LOOP",
+ "SELECT ",
+ true, true, &term);
+ new->params = lappend(new->params, expr);
+ if (term == K_LOOP)
+ break;
+ }
+ }
$$ = (PLpgSQL_stmt *) new;
}
***
*** 920,925
--- 938,944
*/
expr1 = read_sql_construct(K_DOTDOT,
K_LOOP,
+ 0,
"LOOP",
"SELECT ",
true,
***
*** 1262,1268
for (;;)
{
! expr = read_sql_construct(',', ';', ", or ;",
"SELECT ",
true, true, &term);
new->params = lappend(new->params, expr);
--- 1281,1287
for (;;)
{
! expr = read_sql_construct(',', ';', 0, ", or ;",
"SELECT ",
true, true, &term);
new->params = lappend(new->params, expr);
***
*** 1332,1339
PLpgSQL_stmt_dynexecute *new;
PLpgSQL_expr *expr;
int endtoken;
! expr = read_sql_construct(K_INTO, ';', "INTO|;", "SELECT ",
true, true, &endtoken);
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
--- 1351,1360
PLpgSQL_stmt_dynexecute *new;
PLpgSQL_expr *expr;
int endtoken;
+ bool have_into;
+ bool have_using;
! expr = read_sql_construct(K_INTO, K_USING, ';', "INTO|USING|;", "SELECT ",
true, true, &endtoken);
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
***
*** 1342,1380
new->query= expr;
new->rec = NULL;
new->row = NULL;
/*
* If we saw "INTO", look for a following row
* var, record var, or list of scalars.
*/
! if (endtoken == K_INTO)
{
! switch (yylex())
{
! case T_ROW:
! new->row = yylval.row;
! check_assignable((PLpgSQL_datum *) new->row);
! break;
!
! case T_RECORD:
! new->rec = yylval.rec;
!
