Thread: 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@xs4all.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@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
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@xs4all.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@sss.pgh.pa.us> To: "Gevik Babakhani" <pgdev@xs4all.nl> Cc: <pgsql-hackers@postgresql.org> Sent: Wednesday, October 31, 2007 4:36 AM Subject: Re: [HACKERS] Proposal TODO Item: SQL-language reference parameters by name > "Gevik Babakhani" <pgdev@xs4all.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 >