Re: Out parameters handling - Mailing list pgsql-hackers

On Fri, Mar 6, 2009 at 4:29 PM, Asko Oja <ascoja@gmail.com> wrote:
> It was one of my worst Friday's finding out that this brain dead
> implementation of out parameters had been part of fuck up again.
> This time we did notice it two days too late.
> I wish for a way to use out parameters in functions only through some
> predefined prefix like in triggers new and old. Means i  would like to limit
> referencing to out parameters to one prefix only defined in the beginning of
> declare section of stored procedure.
> It really sucks what kind of mistakes you can pass to production
> unknowingly. I would much prefer a way to prevent such nonsense.
> Here was the case where out parameters were with same names with select into
> field names resulting in null outcome. Just yesterday we had similar case
> with update statement.

This is indeed sucky, but sadly it goes well beyond out parameters.
For example:

rhaas=# CREATE FUNCTION test(v integer) RETURNS integer AS $$
BEGIN   RETURN (SELECT v.id FROM foo v WHERE v.id = v);
END
$$ LANGUAGE plpgsql;
ERROR:  syntax error at or near "$1"
LINE 1: SELECT  (SELECT v.id FROM foo  $1  WHERE v.id =  $1 )                                      ^
QUERY:  SELECT  (SELECT v.id FROM foo  $1  WHERE v.id =  $1 )
CONTEXT:  SQL statement in PL/PgSQL function "test" near line 2

It's obviously quite impossible for "foo v" to mean "foo $1", but that
doesn't stop the compiler from substituting it.  (The error message
isn't great either).  And then of course you can select an
in-parameter when you meant to select a column:

CREATE FUNCTION test(id integer) RETURNS integer AS $$
BEGIN   RETURN (SELECT id FROM foo WHERE v.id < id);
END
$$ LANGUAGE plpgsql;

Of course in a simple example like this you might be lucky enough to
notice the problem, but in a more complicated function with several
large queries and a few loops it's very easy to miss.  I usually
manage to catch them before I roll them out, but I've definitely
wasted a lot of time being confused about why the results didn't make
any sense.

As someone pointed out downthread, what we really need is a
distinction between host variables and guest variables.

http://www.postgresql.org/docs/8.3/static/ecpg-variables.html

I wonder whether it would be possible to make PL/pgsql take :foo to
mean the parameter named foo, and then provide an option to make that
THE ONLY WAY to refer to the parameter foo.  For
backward-compatibility, and compatibility with (ahem) other database
products, we probably don't want to remove the option to have foo
mean... any damn thing named foo you can put your hands on.  But it
would be nice to at least have the option of disabling that behavior
when compatibility is not an issue, and correctness is.

...Robert