Re: V0.1 patch for TODO Item: SQL-language reference parameters by name. - Mailing list pgsql-patches

On 02/11/2007, Gevik Babakhani <pgdev@xs4all.nl> wrote:
>
> Hi,
>
> > what about name's collision? Maybe is better use some prefix,
> > like $ or :. Without it we only propagate one problem from
> > plpgsql to others languages.
> >
> Please explain.
>
> Perhaps I am wrong, but plpgsql handles arsgument names before it
> passes the query to be executed. Please see:
>
> plpgsql/pl_comp.c/do_compile(...)/line: 393
>
> Regards,
> Gevik.
>

it's one from mystic bugs:

create table t(a integer, b integer);
insert into t values(10,20);

create function foo(a integer)
returns integer
as $$
  select a from t
    where a <> b and a = 10;
$$ languge sql;

select foo(20);
output? expected 10, but you will get NULL!

Regards
Pavel Stehule

so some prefixes can help

create function foo(a integer)
returns integer
as $$
  select a from t
    where :a <> b and a = 10;
$$ languge sql;

Oracle use symbol ':'

I don't know what others databases has.

Regards
Pavel Stehule