Thread: V0.2 patch for TODO Item: SQL-language reference parameters by name.

V0.2 patch for TODO Item: SQL-language reference parameters by name.

From
"Gevik Babakhani"
Date:
Hello All,

This patch implements a (generic) callback functionality in the parser.
The mechanism can be used to send callback messages from within the parser
to external functions.

I would like to know your opinion about the following:

In previous discussion Tom referred to:

>One point here is that it would be good to be able to qualify the argument
names with the function name, for example
>    create function myfunc(x int) ...
>        select ... from t where t.x = myfunc.x

The above is possible but I think qualifying the argument names with the
function name
can be cumbersome when one has to provide the function name multiple times.
For example: (where clause)

create or replace function sp_item_get_by_type_or_category(p_type
integer,p_category integer)
    returns setof item_view as
$$
    select ..... from item_view i
        inner join tblcategory c on i.catid = c.catid
        inner join tbltype t on i.typeid = t.typeid
    where
        c.catid = sp_item_get_by_type_or_category.p_category or
        t.typeid = sp_item_get_by_type_or_categor.p_type;
$$
language sql;

Perhaps we could use the word "this"  instead of the entire function name

For example:
....
    where
        c.catid = this.p_category or
        t.typeid = this.p_type;
....


Any thoughts?

Regards,
Gevik

************************************************************************
PLEASE NOTE:
    - This patch in created with MSVC++
    - Resolving the argnames is not yet implemented correctly
      due above.
    - Two files have been added parse_callback.h and .c

How does it work:

>>> To setup callback;

ParserCallbackContext sqlcallbackcontext;

/* attaching parser callback handler*/
sqlcallbackcontext.context = T_ParsingFunctionBody;
sqlcallbackcontext.ctxarg = tuple;
sqlcallbackcontext.callback = sql_parser_callback_handler;
sqlcallbackcontext.previous = parser_callback_context_stack;
parser_callback_context_stack = &sqlcallbackcontext;
....
....
parser_callback_context_stack = sqlcallbackcontext.previous;

>>> To call the callback handler from within the parser:

ParserCallbackContextArgs args;
args.pstate = pstate;
args.input = (Node *)cref;
args.action = A_ResolveAmbigColumnRef;
parser_do_callback(&args);

To handle the callback:

if(context == T_ParsingFunctionBody)
{
    switch(action)
    {
        case  A_ResolveAmbigColumnRef:
            ....
    }
}






Attachment

Re: V0.2 patch for TODO Item: SQL-language reference parameters by name.

From
David Fetter
Date:
On Sat, Nov 03, 2007 at 12:36:45PM +0100, Gevik Babakhani wrote:
> Hello All,
>
> This patch implements a (generic) callback functionality in the parser.
> The mechanism can be used to send callback messages from within the parser
> to external functions.
>
> I would like to know your opinion about the following:
>
> In previous discussion Tom referred to:
>
> >One point here is that it would be good to be able to qualify the argument
> > names with the function name, for example
> >    create function myfunc(x int) ...
> >        select ... from t where t.x = myfunc.x
>
> The above is possible but I think qualifying the argument names with the
> function name
> can be cumbersome when one has to provide the function name multiple times.
> For example: (where clause)
>
> create or replace function sp_item_get_by_type_or_category(p_type
> integer,p_category integer)
>     returns setof item_view as
> $$
>     select ..... from item_view i
>         inner join tblcategory c on i.catid = c.catid
>         inner join tbltype t on i.typeid = t.typeid
>     where
>         c.catid = sp_item_get_by_type_or_category.p_category or
>         t.typeid = sp_item_get_by_type_or_categor.p_type;
> $$
> language sql;
>
> Perhaps we could use the word "this"  instead of the entire function name
>
> For example:
> ....
>     where
>         c.catid = this.p_category or
>         t.typeid = this.p_type;
> ....
>
>
> Any thoughts?

I think a prefix of ':' would be good, as it's already a standard,
kinda.  Anybody who names a database object :foo deserves whatever
happens to them :P

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: V0.2 patch for TODO Item: SQL-language reference parameters by name.

From
"Pavel Stehule"
Date:
> >
> > Any thoughts?
>
> I think a prefix of ':' would be good, as it's already a standard,
> kinda.  Anybody who names a database object :foo deserves whatever
> happens to them :P
>
> Cheers,
> David.

+1

':' is shorter than 'this'. And ':' is well known in SQL area.

Pavel

Re: V0.2 patch for TODO Item: SQL-language reference parameters by name.

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> I think a prefix of ':' would be good, as it's already a standard,
> kinda.  Anybody who names a database object :foo deserves whatever
> happens to them :P

The important word there is "kinda".  We do not need a prefix and
I'll resist introducing one.

            regards, tom lane

Re: V0.2 patch for TODO Item: SQL-language referenceparameters by name.

From
"Gevik Babakhani"
Date:
> I think a prefix of ':' would be good, as it's already a
> standard, kinda.  Anybody who names a database object :foo
> deserves whatever happens to them :P
>

I for one like something less cryptic than ':'
besids going with ':' means extra hack in gram.y

(Ones we get to implement packages I prefer to have "this.arg" and
"global.arg" than ':' and '::'
but I guess that's another discussion.)

Regards,
Gevik.


Re: V0.2 patch for TODO Item: SQL-language reference parameters by name.

From
David Fetter
Date:
On Sat, Nov 03, 2007 at 12:44:07PM -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > I think a prefix of ':' would be good, as it's already a standard,
> > kinda.  Anybody who names a database object :foo deserves whatever
> > happens to them :P
>
> The important word there is "kinda".  We do not need a prefix and
> I'll resist introducing one.

What I mean by "kinda" is that it's a standard way of handling
parameters in Oracle and in DBI.  I think it would be a very bad idea
to require that people use the function name in parameters, as such
names can be quite long.  People using names like :foo for database
objects could just quote them :)

Another possibility would be to introduce another parameter type in
addition to IN, OUT and INOUT called PREFIX (required to be of type
text) which would enable people to change from the default prefix.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: V0.2 patch for TODO Item: SQL-language referenceparameters by name.

From
Gregory Stark
Date:
"David Fetter" <david@fetter.org> writes:

> What I mean by "kinda" is that it's a standard way of handling
> parameters in Oracle and in DBI.

That's a good reason *not* to use them for other purposes. Users trying to
create procedures through DBI or other interfaces like it will run into
problems when the driver misinterprets the parameters.

> I think it would be a very bad idea
> to require that people use the function name in parameters,

I think were talking about only allowing it to disambiguate if the name is
shadowed by a variable in an inner scope.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

Re: V0.2 patch for TODO Item: SQL-language referenceparameters by name.

From
"Gevik Babakhani"
Date:
So where do we go from here?

a. <function name>.<arg name>
b. <this>.<arg name>
c. ':'<argname>
d. just <argname>


option a,b and d are easy to implement.
option d would be least clear and readable considering
sql functions can be long and have multiple arguments.

option c is more difficult because gram.y has to be modified
to understand ':'<identifier> as parameter but not a target_list item.

option a and b would make the source more readable
but extra documentation has to be provided to describe
how to refer arguments by name.

Regards,
Gevik

------------------------------------------------
Gevik Babakhani

PostgreSQL NL       http://www.postgresql.nl
TrueSoftware BV     http://www.truesoftware.nl
------------------------------------------------


> -----Original Message-----
> From: pgsql-patches-owner@postgresql.org
> [mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Gregory Stark
> Sent: Saturday, November 03, 2007 6:22 PM
> To: David Fetter
> Cc: Tom Lane; Gevik Babakhani; pgsql-patches@postgresql.org
> Subject: Re: [PATCHES] V0.2 patch for TODO Item: SQL-language
> referenceparameters by name.
>
>
> "David Fetter" <david@fetter.org> writes:
>
> > What I mean by "kinda" is that it's a standard way of handling
> > parameters in Oracle and in DBI.
>
> That's a good reason *not* to use them for other purposes.
> Users trying to create procedures through DBI or other
> interfaces like it will run into problems when the driver
> misinterprets the parameters.
>
> > I think it would be a very bad idea
> > to require that people use the function name in parameters,
>
> I think were talking about only allowing it to disambiguate
> if the name is shadowed by a variable in an inner scope.
>
> --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's RemoteDBA services!
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org
> so that your
>        message can get through to the mailing list cleanly
>


Re: V0.2 patch for TODO Item: SQL-language reference parameters by name.

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> What I mean by "kinda" is that it's a standard way of handling
> parameters in Oracle and in DBI.  I think it would be a very bad idea
> to require that people use the function name in parameters, as such
> names can be quite long.  People using names like :foo for database
> objects could just quote them :)

At no point did I suggest *requiring* parameter names to be prefixed
with the function name.  I just pointed to that as an established way
(which we borrowed from Oracle remember) of disambiguating if you insist
on using the same names for parameters as columns in the query.

The problem with trying to introduce :foo into the SQL grammar is that
we *already have* a meaning for :, and I do not wish to either break
array subscripting or put in the sorts of kluges that would be needed to
make them coexist (or should I say "kinda coexist").

            regards, tom lane

Re: V0.2 patch for TODO Item: SQL-language referenceparameters by name.

From
Tom Lane
Date:
"Gevik Babakhani" <pgdev@xs4all.nl> writes:
> So where do we go from here?
> a. <function name>.<arg name>
> b. <this>.<arg name>
> c. ':'<argname>
> d. just <argname>

We must support both a and d.

            regards, tom lane

Re: V0.2 patch for TODO Item: SQL-language referenceparameters by name.

From
"Gevik Babakhani"
Date:
> "Gevik Babakhani" <pgdev@xs4all.nl> writes:
> > So where do we go from here?
> > a. <function name>.<arg name>
> > b. <this>.<arg name>
> > c. ':'<argname>
> > d. just <argname>
>
> We must support both a and d.

Then a and d it is :)

Regards,
Gevik

------------------------------------------------
Gevik Babakhani

PostgreSQL NL       http://www.postgresql.nl
TrueSoftware BV     http://www.truesoftware.nl
------------------------------------------------