Re: [HACKERS] Proposal TODO Item: SQL-language reference parameters by name
Ü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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal TODO Item: SQL-language reference parameters by name
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 [EMAIL PROTECTED] To: Gevik Babakhani [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] 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.
Re: [HACKERS] Proposal TODO Item: SQL-language reference parameters by name
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 [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal TODO Item: SQL-language reference parameters by name
Gevik Babakhani [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal TODO Item: SQL-language reference parameters by name
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal TODO Item: SQL-language reference parameters by name
Gevik Babakhani [EMAIL PROTECTED] 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
