Thread: PL/pgSQL: possible parsing or documentation bug?
Saluton,
plpgsql seems to get confused when using variable names that coincide
with, e.g., column names:
create Function IdOfPossiblyNewAuthor(text) returns int4 as ' declare id int4; begin select id into id from author
wherename = $1; raise notice ''ID found: %'', id; if id is null then insert into author (name) values ($1);
selectcurrval(''author_id_seq'') into id; raise debug ''Author inserted. ID: %'', id; end if; return id; end;
' language 'plpgsql' with (IsCachable);
Logically it is clear which "id" should be parsed as the variable,
which as author.id, but I have to use a different name to make this
work. I do not see any mention on this restriction of variable names
(the full extent of which I do not know) in the documentation (7.0.2).
Albert.
--
--------------------------------------------------------------------------
Albert Reiner <areiner@tph.tuwien.ac.at>
Deutsch * English * Esperanto * Latine
--------------------------------------------------------------------------
Hello Postgres Users and Developers,
I have the following scenario:
create table t (i int);
create index ti on t(i);
Now this table is filled with some values and the table is vacuum analyzed.
Now I would like to run queries on this table which should use the index
whenever possible, so they execute fast.
If I try a simple query like: "select * from t where i=4" the index is used.
A query like: "select * from t where i=abs(4)" is using the index too.
But if I use more complex functions like the following:
"select * from t where i=date_part('year', '2001-01-01'::date)"
a sequential scan on the table is performed.
Now I conclude that the planner/optimizer does not recognize that the
date_part() function returns the same value upon each execution.
What I would like to know: Could we use some const-cast, so the optimzer gets
a hint in optimizing the query ?
I think of something like:
"select * from t where i=date_part('year', '2001-01-01'::date)::const"
Would this be hard to implement, or are there any theoretical issues which
permit this. My thoughts are, that if the user declares something as const,
although it might not always be const, the database should not worry about
the complete truth and just assume the statement as const.
Or Is this feature available already, and I have just missed the correct
keyword?
--
--
---> doj@redaex.de
You can create a function with the IsCacheable attribute...
CREATE FUNCTION my_date_part(date) RETURNS int4 AS 'SELECT date_part('year', $1);
' LANGUAGE 'sql' WITH iscachable();
(This can be found in the create function docs, or in Bruce's book -- both
available on the website)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Fri, 2 Feb 2001 doj@wwws2.redaex.de wrote:
> Hello Postgres Users and Developers,
>
> I have the following scenario:
> create table t (i int);
> create index ti on t(i);
>
> Now this table is filled with some values and the table is vacuum analyzed.
>
> Now I would like to run queries on this table which should use the index
> whenever possible, so they execute fast.
>
> If I try a simple query like: "select * from t where i=4" the index is used.
> A query like: "select * from t where i=abs(4)" is using the index too.
> But if I use more complex functions like the following:
> "select * from t where i=date_part('year', '2001-01-01'::date)"
> a sequential scan on the table is performed.
>
> Now I conclude that the planner/optimizer does not recognize that the
> date_part() function returns the same value upon each execution.
>
> What I would like to know: Could we use some const-cast, so the optimzer gets
> a hint in optimizing the query ?
> I think of something like:
> "select * from t where i=date_part('year', '2001-01-01'::date)::const"
>
> Would this be hard to implement, or are there any theoretical issues which
> permit this. My thoughts are, that if the user declares something as const,
> although it might not always be const, the database should not worry about
> the complete truth and just assume the statement as const.
>
> Or Is this feature available already, and I have just missed the correct
> keyword?
>
> --
> --
> ---> doj@redaex.de
>
Michael Fork <mfork@toledolink.com> writes:
> You can create a function with the IsCacheable attribute...
> CREATE FUNCTION my_date_part(date) RETURNS int4 AS '
> SELECT date_part('year', $1);
> ' LANGUAGE 'sql' WITH iscachable();
The reason date_part --- and most other datetime-related functions ---
is not marked iscachable already is the existence of the special value
CURRENT in some of the datetime datatypes, which makes the result of
any datetime function potentially dependent on when you execute it.
I believe we have agreed that CURRENT is evil and should be eliminated,
but it hasn't gotten done yet. See past discussions in the pghackers
archives.
regards, tom lane
"Albert REINER" <areiner@tph.tuwien.ac.at> writes:
> create Function IdOfPossiblyNewAuthor(text) returns int4 as '
> declare
> id int4;
> begin
> select id into id from author where name = $1;
> raise notice ''ID found: %'', id;
> if id is null then
> insert into author (name) values ($1);
> select currval(''author_id_seq'') into id;
> raise debug ''Author inserted. ID: %'', id;
> end if;
> return id;
> end;
> ' language 'plpgsql' with (IsCachable);
> Logically it is clear which "id" should be parsed as the variable,
> which as author.id,
No, it is not so clear. Consider the following:
declare x int4; y int4;begin x := ...; select x + f1 into y from tab1 where ...;
The intent here is clearly to find a value tab1.f1 in tab1 and then
add the local variable x to form a value for the local variable y.
In general plpgsql will try to match an unqualified name to a variable
before it will consider whether it might be a field name. If you don't
want that, qualify the field name:
select author.id into id from author where name = $1;
Feel free to submit documentation updates to make this clearer...
regards, tom lane