Re: Proposal TODO Item: SQL-language reference parameters by name

Hi all,

I am working a lot with custom procedures/functions which are implemented in "language sql". At the moment function parameter refs cannot work with parameter names. I would like to try to implement this. The actual TODO item is:

Allow SQL-language functions to reference parameters by parameter name

Currently SQL-language functions can only refer to dollar parameters, e.g. $1

After a quick look at how ref parameters in plpgsql and sql function are handled, I would like to start a discussion about the following implementation plan.

Implementation of the name parameter parsing in scan.l/gram.y can be achieved by adopting the same mechanism as plpgsql does. If I am not mistaking plpgsql parser creates a stack to store parameter identifiers.

Correct me if this would be wrong or not possible, but I am thinking of mapping the name references to the parameter numbers (par2 => $2) this way I hope to keep the implementation small and perhaps extend "struct ParamRef" to hold a possible parameter name.

Then there is the issue of error reporting for ambiguous parameter names (non existing parameter names and names which conflict with actual column names). I guess this can be handeled in "fmgr_sql_validator"

Please let me know your opinion.

Regards,

Gevik.

"Gevik Babakhani" <pgdev(at)xs4all(dot)nl> writes:
> Implementation of the name parameter parsing in scan.l/gram.y can be
> achieved by adopting the same mechanism as plpgsql does.

If you do that it will likely be rejected outright, because there's
considerable agreement that plpgsql is wrong/broken on this point.
Check the archives, eg these two threads:
http://archives.postgresql.org/pgsql-hackers/2007-07/msg00294.php
http://archives.postgresql.org/pgsql-hackers/2007-07/msg00408.php

Parameter and variable names really need to be in an outer scope
such that they bind less tightly than names available within a SQL
query. I'm not sure if we'll ever risk breaking existing applications
by switching around the priority in plpgsql functions, but that's
not a reason not to get it right in sql functions.

I think the most likely implementation would involve adding hooks
in the parser at places where "unknown column" errors are about to
be thrown, so that a function language could check for a match to
one of its variable names only after the query-exposed names are
checked.

regards, tom lane

On Tue, Oct 30, 2007 at 02:31:52PM +0100, Gevik Babakhani wrote:
> Hi all,
>
> I am working a lot with custom procedures/functions which are
> implemented in "language sql". At the moment function parameter refs
> cannot work with parameter names. I would like to try to implement
> this. The actual TODO item is:
>
> Allow SQL-language functions to reference parameters by parameter
> name

I'm thinking we should make named parameters available to all the PLs,
if possible, as re-implementing named parameters for each one seems
like a lot of wasted effort.

How much infrastructure and/or re-jiggering of existing PLs would be
required?

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

Thank you Tom.

> I think the most likely implementation would involve adding hooks
> in the parser at places where "unknown column" errors are about to
> be thrown, so that a function language could check for a match to
> one of its variable names only after the query-exposed names are
> checked.
Would this be the right path to follow?

Assuming we are allowed to include parameter names into ParseState, we can
match the "non existing" colnames against the list of parameter
names when transformColumnRef (or someware safe in that path). I Think this
way
we at least can parse a function when CreateFunction is called.

If the above is correct to implement then the check should have low
precedence in order to not break
the current functionality (first colnames, then $n params and then parameter
names)

Regards,
Gevik.

"Gevik Babakhani" <pgdev(at)xs4all(dot)nl> writes:
>> I think the most likely implementation would involve adding hooks
>> in the parser at places where "unknown column" errors are about to
>> be thrown, so that a function language could check for a match to
>> one of its variable names only after the query-exposed names are
>> checked.

> Would this be the right path to follow?

> Assuming we are allowed to include parameter names into ParseState, we can
> match the "non existing" colnames against the list of parameter
> names when transformColumnRef (or someware safe in that path).

You are assuming that the function language isn't interested in taking
some extra action when a reference to a parameter is recognized. This
is demonstrably false for plpgsql, for one --- it wants to build a list
of just which variables it will have to pass into each SQL command.
A hook function can take care of that, a passive data structure can't.

regards, tom lane

Ühel kenal päeval, T, 2007-10-30 kell 09:35, kirjutas David Fetter:
> On Tue, Oct 30, 2007 at 02:31:52PM +0100, Gevik Babakhani wrote:
> > Hi all,
> >
> > I am working a lot with custom procedures/functions which are
> > implemented in "language sql". At the moment function parameter refs
> > cannot work with parameter names. I would like to try to implement
> > this. The actual TODO item is:
> >
> > Allow SQL-language functions to reference parameters by parameter
> > name
>
> I'm thinking we should make named parameters available to all the PLs,
> if possible, as re-implementing named parameters for each one seems
> like a lot of wasted effort.

They are "available" you just have to make use of that availability in
your PL handler

> How much infrastructure and/or re-jiggering of existing PLs would be
> required?

The patch that added this (among other things) to PL/python is
referenced from
http://archives.postgresql.org/pgsql-committers/2006-04/msg00319.php

-----------
Hannu

Thank you Tom.

After running a create function statement (language sql), the final check
for a column is done in
parse_expr.c:transformColumnRef in case 1. Would this be the correct place
to implement
functionality for a final match?

Regards,
Gevik.

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Gevik Babakhani" <pgdev(at)xs4all(dot)nl>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Wednesday, October 31, 2007 4:36 AM
Subject: Re: [HACKERS] Proposal TODO Item: SQL-language reference parameters
by name

> "Gevik Babakhani" <pgdev(at)xs4all(dot)nl> writes:
>>> I think the most likely implementation would involve adding hooks
>>> in the parser at places where "unknown column" errors are about to
>>> be thrown, so that a function language could check for a match to
>>> one of its variable names only after the query-exposed names are
>>> checked.
>
>> Would this be the right path to follow?
>
>> Assuming we are allowed to include parameter names into ParseState, we
>> can
>> match the "non existing" colnames against the list of parameter
>> names when transformColumnRef (or someware safe in that path).
>
> You are assuming that the function language isn't interested in taking
> some extra action when a reference to a parameter is recognized. This
> is demonstrably false for plpgsql, for one --- it wants to build a list
> of just which variables it will have to pass into each SQL command.
> A hook function can take care of that, a passive data structure can't.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>