Thread: V0.1 patch for TODO Item: SQL-language reference parameters by name.
Hello all,
Hereby an alpha version regarding the:
TODO Item: SQL-language reference parameters by name.
I am sending this patch to check if I am on the right track.
So please take a look at this if possible.
What does this patch do?
As discussed in thread: http://archives.postgresql.org/pgsql-hackers/2007-10/msg01490.php,
this patch adds an additional parameter (char **argnames) to pg_parse_and_rewrite and
pg_analyze_and_rewrite and ParseState.
When transformColumnRef is about to report an error for a non existing column,a final match is
performed to see if the non existing column is a parameter name. (argnames)
If true, then a new node is created by transformParamRef
NOTE:
- This patch is created using MSVC++ !
- Nothing is done yet for polymorphic arguments.
My test where:
create table tbl1(id serial,field1 integer,field2 varchar);
insert into tbl1 (field1,field2) values(11,'zzzz');
insert into tbl1 (field1,field2) values(22,'yyyy');
create or replace function func1(par1 integer,par2 integer,par3 varchar) returns setof record as
$$
select
par1::text,
par2,
par1+par2,
par2+par1,
par1+field1,
(field1+par2)::varchar,
par3,
field2 || ' ' || par3
from
tbl1;
$$ language sql;
select func1(2,4,'aaaa');
select * from func1(5,16,'bbbb') as (a text ,b int ,c int, e int, f int,g varchar,h varchar,i text);
results:
"(2,4,6,6,13,15,aaaa,"zzzz aaaa")"
"(2,4,6,6,24,26,aaaa,"yyyy aaaa")"
And
"5";16;21;21;16;"27";"bbbb";"zzzz bbbb"
"5";16;21;21;27;"38";"bbbb";"yyyy bbbb"
Regards,
Gevik
Attachment
Gevik Babakhani wrote: > > Hello all, > > > > Hereby an alpha version regarding the: > > TODO Item: SQL-language reference parameters by name. > > > > I am sending this patch to check if I am on the right track. > > So please take a look at this if possible. > Step 1: don't use c++ style comments like this: + //TODO: Check here C89 is basically our standard. gcc -std=c89 will check that it complies. cheers andrew
> Hello all, > > > > Hereby an alpha version regarding the: > > TODO Item: SQL-language reference parameters by name. > > 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. It can be more wide used: * named params in prepared statements * named params in SPI * .. Regards Pavel Stehule
Re: V0.1 patch for TODO Item: SQL-language reference parameters by name.
Noted. Thank you. -----Original Message----- From: pgsql-patches-owner@postgresql.org [mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Andrew Dunstan Sent: Friday, November 02, 2007 4:19 PM To: Gevik Babakhani Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name. Gevik Babakhani wrote: > > Hello all, > > > > Hereby an alpha version regarding the: > > TODO Item: SQL-language reference parameters by name. > > > > I am sending this patch to check if I am on the right track. > > So please take a look at this if possible. > Step 1: don't use c++ style comments like this: + //TODO: Check here C89 is basically our standard. gcc -std=c89 will check that it complies. cheers andrew ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Re: V0.1 patch for TODO Item: SQL-language reference parameters by name.
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.
"Gevik Babakhani" <pgdev@xs4all.nl> writes:
> I am sending this patch to check if I am on the right track.
> So please take a look at this if possible.
You seem not to have understood my recommendation to use a callback
function. This patch might work nicely for SQL functions but there
will be no good way to use it for plpgsql, or probably any other
PL function language. If we're going to change the parser API
then I'd like to have a more general solution.
regards, tom lane
"Gevik Babakhani" <pgdev@xs4all.nl> writes:
>> 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.
Which is actually the Wrong Thing to do: really the parameters should be
seen as being in a name scope that's outside that of the query (and thus
ambiguous names should be resolved first as column names of the query).
The proposed patch does this in the right order and so I think that
Pavel's concern is without foundation.
One point here is that it would be good to be able to qualify the
argument names with the function name, for example
create function myfunc(x int) ...
select ... from t where t.x = myfunc.x
If t has a column named x then this will be the only way that the
function parameter x can be referenced within that query. We are
partway to that point with plpgsql but haven't bitten the bullet
of changing the lookup order.
Note that this consideration is another reason for having a callback
function that's responsible for trying to resolve unresolved names.
I certainly wouldn't like to have a notion of "function name" wired
into the parser API, and if we did do that it still wouldn't be
sufficient for plpgsql which can have multiple block-label namespaces
accessible at once.
regards, tom lane
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