Skip site navigation (1) Skip section navigation (2)

Re: Patch: Allow SQL-language functions to reference parameters by parameter name

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matthew Draper <matthew(at)trebex(dot)net>
Cc: Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Patch: Allow SQL-language functions to reference parameters by parameter name
Date: 2012-02-02 23:19:36
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
[ working on this patch now ... ]

Matthew Draper <matthew(at)trebex(dot)net> writes:
> On 25/01/12 18:37, Hitoshi Harada wrote:
>> Should we throw an error in such ambiguity? Or did you make it happen
>> intentionally? If latter, we should also mention the rule in the
>> manual.

> I did consider it, and felt it was the most consistent:

I believe the issue here is that a two-part name A.B has two possible
interpretations (once we have eliminated table references supplied by
the current SQL command inside the function): per the comment,

     * A.B        A = record-typed parameter name, B = field name
     *            OR: A = function name, B = parameter name

If both interpretations are feasible, what should we do?  The patch
tries them in the above order, but I think the other order would be
better.  My argument is this: the current behavior doesn't provide any
"out" other than changing the function or parameter name.  Now
presumably, if someone is silly enough to use a parameter name the same
as the function's name, he's got a good reason to do so and would not
like to be forced to change it.  If we prefer the function.parameter
interpretation, then he still has a way to get to a field name: he just
has to use a three-part name function.parameter.field.  If we prefer the
parameter.field interpretation, but he needs to refer to a scalar
parameter, the only way to do it is to use an unqualified reference,
which might be infeasible (eg, if it also matches a column name exposed
in the SQL command).

Another problem with the current implementation is that if A matches a
parameter name, but ParseFuncOrColumn fails (ie, the parameter is not of
composite type or doesn't contain a field named B), then the hook just
fails to resolve anything; it doesn't fall back to consider the
function-name-first alternative.  So that's another usability black
mark.  We could probably complicate the code enough so it did consider
the function.parameter case at that point, but I don't think that there
is a strong enough argument for this precedence order to justify such

In short, I propose swapping the order in which these cases are tried.

(BTW, my reading of the SQL spec is that it thinks we should throw
an error for such ambiguity.  So that would be another possible answer,
but I'm not sure it's greatly helpful.)

			regards, tom lane

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2012-02-02 23:27:44
Subject: Hot standby fails if any backend crashes
Previous:From: Magnus HaganderDate: 2012-02-02 23:06:16
Subject: Re: Patch pg_is_in_backup()

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group