Re: [SQL] const cast ?
Michael Fork <[EMAIL PROTECTED]> 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
Re: [SQL] Four Odd Questions
On Fri, Feb 02, 2001 at 04:37:34PM -0800, Josh Berkus wrote: > Folks, > > 1. Has anyone had experience with trying to link Informix's 4GL as a > procedural language extension for PostgreSQL? ANyone care to > speculate? I happen to have access to a couple of former Informix > employees ... Is that open source? I doubt it. > 2. Is there any documentation on the SQL changes being incorporated into > 7.1? We've talked about some of them on this list, but I'm still not > sure what the syntax for "ALTER TABLE" will be, for example. http://www.postgresql.org/devel-corner/docs/ -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
[SQL] Four Odd Questions
Folks, 1. Has anyone had experience with trying to link Informix's 4GL as a procedural language extension for PostgreSQL? ANyone care to speculate? I happen to have access to a couple of former Informix employees ... 2. Is there any documentation on the SQL changes being incorporated into 7.1? We've talked about some of them on this list, but I'm still not sure what the syntax for "ALTER TABLE" will be, for example. 3. pg_dump and Restore is currently a bit awkward, and requires the intervention of a developer to get the database running properly again. Are there plans to improve this, or has somebody written a script that handles the steps involved? 4. I'm not trying 7.1 beta 3. I noticed that for this version, Theodescu's PGAccess lists all builtin functions along with the user-defined functions in the functions window. Anybody else notice this? -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] const cast ?
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 [EMAIL PROTECTED] 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?
>
> --
> --
> ---> [EMAIL PROTECTED]
>
Re: [SQL] binary operators
Frederic Metoz writes: > I am looking for the binary AND and OR ... SHIFT as well. > Do they exist for postgresql ? I have functions that do this on int4. Not operators, but better than nothing :) Anyone interested, please feel free to email in private. In fact, the lib is simple as a moo. -- ÌĤ¯Ç¤ÏÁͤòÊá¤é¤Ì
Re: [SQL] 7.0.2-docs: textpos -> strpos
Albert REINER writes: > in the 7.0.2-docs I find the function textpos: > However, in psql it seems one has to use strpos: textpos() was removed from the 7.1 documentation. position() is the SQL function, I think strpos() is from Oracle. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Suggestion for psql: --file -
Albert REINER writes: > wouldn't it be a good idea (and if it is, I am not sure what list to > post it to) to have psql's option -f / --file take "-" for stdin, as > many programs do? Seems reasonable. > P.S: BTW, the man page (7.0.2) of psql is not very clear: it took me > some experimentation to find out that you have to do "\set VARIABLE" > interactively or give "--set VARIABLE=" to set a variable that does > not take a value. Suggested new wording? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] binary operators
Frederic Metoz writes: > I am looking for the binary AND and OR ... SHIFT as well. > Do they exist for postgresql ? in 7.1 -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
RE: [SQL] const cast ?
Title: RE: [SQL] const cast ?
The problem is that there is no way of determining whether or not the sort order after the function has been executed will be the same as the sort order on the raw data. For example, the sort order of n (-10..10) is very different to the sort order of abs(n). So if I had an index on n, I could not use it for searching for abs(n).
So, if you always require the same function, you create a function index:
CREATE INDEX xxx ON t (date_part('year', i::date));
Just remember, if you change function, it will switch back to sequential scan, until you create a function index for the new function that you need to use.
Cheers...
MikeA
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 02 February 2001 13:22
To: PostgreSQL-SQL
Subject: [SQL] const cast ?
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?
--
--
---> [EMAIL PROTECTED]
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
[SQL] const cast ?
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?
--
--
---> [EMAIL PROTECTED]
