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

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:
....
}
}

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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

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

David Fetter <david(at)fetter(dot)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

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

On Sat, Nov 03, 2007 at 12:44:07PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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

"David Fetter" <david(at)fetter(dot)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!

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(at)postgresql(dot)org
> [mailto:pgsql-patches-owner(at)postgresql(dot)org] On Behalf Of Gregory Stark
> Sent: Saturday, November 03, 2007 6:22 PM
> To: David Fetter
> Cc: Tom Lane; Gevik Babakhani; pgsql-patches(at)postgresql(dot)org
> Subject: Re: [PATCHES] V0.2 patch for TODO Item: SQL-language
> referenceparameters by name.
>
>
> "David Fetter" <david(at)fetter(dot)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(at)postgresql(dot)org
> so that your
> message can get through to the mailing list cleanly
>

David Fetter <david(at)fetter(dot)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

"Gevik Babakhani" <pgdev(at)xs4all(dot)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

> "Gevik Babakhani" <pgdev(at)xs4all(dot)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
------------------------------------------------