Re: [SQL] Search and Replace
On Thu, Jan 09, 2003 at 11:00:32AM +0530, Rajesh Kumar Mallah. wrote: > > any anyone explain whats wrong with the replace based solution to this problem > which i posted earlier? > > did i misunderstood anything? Probably just overkill - I'm sure it would work, but, based on how the question was asked, I guessed that the original questioner was looking for a quick, onetime fix sort of thing, and wasn't real comfortable with SQL, let alone adding contrib extension products to the installation. Ross ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] PostgreSQL X Resin EE
I have sucessfuly used resin (standard, not EE, with datasource, pooling) and postgresql. =Radu-Adrian PopescuCSA, DBA, DeveloperAldratech Ltd. - Original Message - From: Pedro Igor To: [EMAIL PROTECTED] Sent: Thursday, January 02, 2003 3:16 PM Subject: [SQL] PostgreSQL X Resin EE Have someone used Resin EE with PostgreSQL or actually use both for building applications ? About PostgreSQL i know that is one of the best options for back-end, but what about Resin EE ? Thanks ... Pedro Igor
[SQL] SQL function parse error ?
Hello ! There is a strange behaviour in the SQL function parser (haven't tested this with the plpgsql lang) when using standard operators and function parameters. Take the following for example: create or replace function testfunc1(int) returns setof test as' select * from test where age>$1;' language sql; This yealds the following error, which frankly is kind of too much : ERROR: Unable to identify an operator '>$' for types 'integer' and 'integer' I strongly belive the parser is a bit keen on interpreting the >$ sequence as an operator. Take for instance php, where all variables start with $; " 100>$foo " is quite valid ! The error goes away of course when rewriting it as " age > $1". I'm afraid that taking whitespace into consideration when parsing a language is not the way to do it, except when it's python, of course :) Hope this, helps, Regards, =Radu-Adrian PopescuCSA, DBA, DeveloperAldratech Ltd.
Re: [SQL] insert rule doesn't see id field
On Thu, Jan 09, 2003 at 07:54:04PM -, [EMAIL PROTECTED] wrote:
> Sounds similar to the plpgsql example at:
>
> http://www.archonet.com/pgdocs/lock-field.html
>
> which silently discards changes. It's trivial (apart from quoting issues)
> to write a trigger generator to customise the above in plpgsql (see the
> Cookbook on techdocs.postgresql.org for examples)
Ah - I wish I saw that earlier. Thanks for pointer. OTOH, I learned
something...
FWIW (probably just a good laugh):
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* and triggers */
#include /* tolower () */
#include
extern Datum noupcols (PG_FUNCTION_ARGS);
/*
noupcols () -- revoke permission on column(s)
e.g.
CREATE FUNCTION noupcols ()
RETURNS opaque
AS '/usr/lib/postgresql/lib/noupcols.so'
LANGUAGE 'C';
CREATE TRIGGER person_noupcols
BEFORE UPDATE ON person
FOR EACH ROW
EXECUTE PROCEDURE noupcols( 'name_last', 'id' );
Based on code from contrib/noup.c
The approach adopted here is to set the values of all of the columns
specified by noupcols to their old values.
*/
PG_FUNCTION_INFO_V1 (noupcols);
Datum
noupcols (PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; /* to get trigger name */
Relationrel;/* triggered relation */
char**args; /* arguments: column names */
int ncols; /* # of args specified in CREATE TRIGGER */
int *colindices;/* array of column indices to modify */
Datum *oldcolvals;/* old column values */
HeapTuple oldtuple = NULL;/* tuple before being modified */
HeapTuple newtuple = NULL;/* new tuple after user-specified update */
HeapTuple newnewtuple = NULL; /* tuple to return, after restoring newtuple's
protected columns to their old values */
TupleDesc tupdesc;/* tuple description */
boolisnull; /* to know is some column NULL or not */
int ret;
int i;
if (!CALLED_AS_TRIGGER (fcinfo))
elog(ERROR, "noup: not fired by trigger manager");
if (TRIGGER_FIRED_FOR_STATEMENT (trigdata->tg_event))
elog (ERROR, "noup: can't process STATEMENT events");
if (TRIGGER_FIRED_BY_INSERT (trigdata->tg_event))
elog (ERROR, "noup: can't process INSERT events");
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
elog (ERROR, "noup: can't process DELETE events");
oldtuple = trigdata->tg_trigtuple;
newtuple = trigdata->tg_newtuple;
trigger = trigdata->tg_trigger;
rel = trigdata->tg_relation;
tupdesc = rel->rd_att;
ncols = trigger->tgnargs;
args = trigger->tgargs;
colindices = (int *) malloc (ncols * sizeof (int));
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog (ERROR, "noupcol: SPI_connect returned %d", ret);
/* Allocate space to place column values */
oldcolvals = (Datum*) palloc (ncols * sizeof (Datum));
/* For each column ... */
for (i = 0; i < ncols; i++)
{
/* get index of column in tuple */
colindices[i] = SPI_fnumber (tupdesc, args[i]);
/* Bad guys may give us un-existing column in CREATE TRIGGER */
if (colindices < 0)
{
elog (ERROR, "noupcols: there is no attribute %s in relation %s",
args[i],
SPI_getrelname (rel));
pfree (oldcolvals);
free (colindices);
SPI_finish ();
return PointerGetDatum (NULL);
}
/* Get previous value of column */
oldcolvals[i] = SPI_getbinval (oldtuple, tupdesc, colindices[i], &isnull);
}
/* Restore protected columns to their old values */
newnewtuple = SPI_modifytuple (rel, newtuple, ncols, colindices, oldcolvals, NULL);
pfree (oldcolvals);
free (colindices);
SPI_finish ();
if (SPI_result == SPI_ERROR_ARGUMENT) {
elog (ERROR, "noupcols: bad argument to SPI_modifytuple\n");
return PointerGetDatum (NULL);
}
if (SPI_result == SPI_ERROR_NOATTRIBUTE) {
elog (ERROR, "noupcols: bad attribute value passed to SPI_modifytuple\n");
return PointerGetDatum (NULL);
}
return PointerGetDatum (newnewtuple);
}
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College_\_v
http://www.mtholyoke.edu/~rpeterso
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] http://www.postgresql.org site problem
Warning: pg_connect() unable to connect to PostgreSQL server: FATAL 1: No pg_hba.conf entry for host 64.49.215.82, user portal, database 186_portal in /usr/local/www/www.postgresql.org/globals.php on line 130 == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] switching default integer datatype to int8 and "IN (...)" clause
Hi,
Short: postrgesql-7.2.3 doesn't figure to convert int4 to int8
implicitly / by context (as in a query). How do I help it ?
Long:
I have index on a table, a select like this takes a split second:
# select * from file where id = fileIDseq.last_value-1;
id | name
-+
1921777 | icons
but a select like this takes ages (long time):
# select * from file where id = 1921773;
id | name
-+
1921777 | icons
but a select like this is quick again:
# select * from file where id = int8(1921773);
id | name
-+
1921777 | icons
the secret seems to be that
# explain select * from file where id = fileIDseq.last_value-1;
Nested Loop (cost=0.00..6.04 rows=1 width=1359)
-> Seq Scan on fileidseq (cost=0.00..1.01 rows=1 width=8)
-> Index Scan using file_pkey on file (cost=0.00..5.02 rows=1 width=1351)
whereas
# explain select * from file where id = 1921773;
Seq Scan on file (cost=0.00..58905.95 rows=1 width=1351)
The reason seems to be that fileIDseq.last_value-1 is type bigint
(int8), whereas "1921773" is of type integer (int4).
Now
# explain select * from file where id in
(fileIDseq.last_value-1,fileIDseq.last_value-1);
Nested Loop (cost=0.00..6.04 rows=1 width=1359)
-> Seq Scan on fileidseq (cost=0.00..1.01 rows=1 width=8)
-> Index Scan using file_pkey on file (cost=0.00..5.02 rows=1 width=1351)
BUT
# explain select * from file where id in
(fileIDseq.last_value-1,fileIDseq.last_value-333); -- "-333" instead of same "-1"
Nested Loop (cost=0.00..92278.69 rows=2 width=1359)
-> Seq Scan on fileidseq (cost=0.00..1.01 rows=1 width=8)
-> Seq Scan on file (cost=0.00..54138.56 rows=1906956 width=1351)
Why ?
Also, how do I tell postgresql that it should by default interpret
integers as "int8"s, and not as "int4"s ? (So that I don't have to
keep saying "int8(XYZ)" as in "select * from file where id = int8(1);"
Thanks,
John
mydb# \d file
Table "file"
Column |Type | Modifiers
--+-+-
id | bigint | not null default nextval('fileIDseq'::text)
name | character varying(255) |
Primary key: file_pkey
mydb=# \d file_pkey
Index "file_pkey"
Column | Type
+
id | bigint
unique btree (primary key)
mydb==# \d fileidseq
Sequence "fileidseq"
Column | Type
---+-
sequence_name | name
last_value| bigint
increment_by | bigint
max_value | bigint
min_value | bigint
cache_value | bigint
log_cnt | bigint
is_cycled | boolean
is_called | boolean
--
-- Gospel of Jesus' kingdom = saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Honza.Vicherek.com/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] switching default integer datatype to int8 and "IN (...)" clause
> but a select like this takes ages (long time): > # select * from file where id = 1921773; >id | name >-+ > 1921777 | icons > I believe the reason is this : the numeric literal is first considered an int4 becuase it falls within the range of int4 (-2147483648 to +2147483647). try quoting the literal like this: # select * from file where id = '1921773'; This forces the literal to be evaluated. If you do an explain on that query ... you should see that the query planner uses the index as expected and that the condition used on the index is using the literal value cast to a big int. That's just my understanding anyway. Andy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL function parse error ?
Radu-Adrian Popescu wrote: Take the following for example: create or replace function testfunc1(int) returns setof test as ' select * from test where age>$1; '^^ language sql; I didn't find it in documentation, but sql functions are like bash (you forgot about space character): create or replace function testfunc1(int) returns setof test as ' select * from test where age > $1; ' works fine. Postgresql thinks that >$ is an operator instead of > Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL function parse error ?
I'm perfectly aware of the fact that a space solves the issue here. What I'm saying is that it is not natural nor common to take some whitespace into account when parsing, since this is not bash language, nor python, as it shouldn't be ! This is SQL, and people who are using PostgreSql write SQL, not whitespace-sensitive SQL, bash or whatever. And besides, like I have already pointed out, look at php's language parser (behavior, not source) : the statement if(100>$a) is perfectly legal, as it should be. Is there any operator named >$ ? Anyone who has used anything from Mysql to Oracle will get quite annoyed on this one. Hope the people in charge of the parser will get to the bottom of this... ... and please forgive my caustic tone. = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. - Original Message - From: "Tomasz Myrta" <[EMAIL PROTECTED]> To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 1:56 PM Subject: Re: [SQL] SQL function parse error ? Radu-Adrian Popescu wrote: > > Take the following for example: > create or replace function testfunc1(int) returns setof test as > ' > select * from test where age>$1; > '^^ > language sql; I didn't find it in documentation, but sql functions are like bash (you forgot about space character): create or replace function testfunc1(int) returns setof test as ' select * from test where age > $1; ' works fine. Postgresql thinks that >$ is an operator instead of > Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL function parse error ?
Radu-Adrian Popescu wrote: I'm perfectly aware of the fact that a space solves the issue here. What I'm saying is that it is not natural nor common to take some whitespace into account when parsing, since this is not bash language, nor python, as it shouldn't be ! This is SQL, and people who are using PostgreSql write SQL, not whitespace-sensitive SQL, bash or whatever. And besides, like I have already pointed out, look at php's language parser (behavior, not source) : the statement if(100>$a) is perfectly legal, as it should be. Is there any operator named >$ ? Anyone who has used anything from Mysql to Oracle will get quite annoyed on this one. Hope the people in charge of the parser will get to the bottom of this... ... and please forgive my caustic tone. = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. I think you are absolutely wrong. It is possible in Postgresql to overload operators and if you want, you can create operator named ">$". There is a lot of useful things in postgresql documentation. If you read it, you could find this: >CREATE OPERATOR defines a new operator, name. The user who defines an >operator becomes its owner. >The operator name is a sequence of up to NAMEDATALEN-1 (31 by default) >characters from the following list: >+ - * / < > = ~ ! @ # % ^ & | ` ? $ Regards, Tomasz Myrta ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Postgresql Bug List?
One could subscribe to pgsql-bugs if you wanted to look into any new bugs that come down the pipe. Robert Treat On Wed, 2003-01-08 at 23:30, Bruce Momjian wrote: > > No bugzilla, but do have a TODO list. See the developers FAQ. > > --- > > Wei Weng wrote: > > Is there a bugzilla kind of thing for Postgresql? > > > > I would like to help out on the development, but have no idea where to > > start... > > > > Thanks > > > > > > Wei > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL function parse error ?
"Radu-Adrian Popescu" <[EMAIL PROTECTED]> writes: > This is SQL, and people who are using PostgreSql write SQL, not > whitespace-sensitive SQL, bash or whatever. Nonsense. SQL syntax is space-sensitive. Or have you successfully written SELECTXFROMY; lately? There has occasionally been talk of disallowing '$' as a valid character in operator names, which would eliminate the syntactic ambiguity in this example. But undoubtedly it would also break a few applications that use '$' in user-defined operator names, so the proposal hasn't passed to date. > Is there any operator named >$ ? Whether there is one in the standard distribution is quite irrelevant. It's a valid operator name according to the current rules, and so open to definition by anyone who wants to. The most recent discussion I can find about this is the pgsql-hackers thread "Dollar in identifiers" from Aug 2001, eg http://archives.postgresql.org/pgsql-hackers/2001-08/msg00629.php There didn't seem to be a consensus to change things, so the old behavior stands, for the moment. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL function parse error ?
Thomas, this line of conversation is not quite what one would expect... I do read the postgresql docs when in run into trouble. And yes, i do know there is such a thing as operator overloading. What i'm saying here, and i belive to be right, is that writing a piece of code such as table.columnName>$3 should not yeald an error. Why is that ? Because the >$ does not exist, not in the default operator list (also there is no operator defined using $ anywhere within). And because whitespacing the code solves the problem, which is rather thin, i must say. Consider you would create operator >&. How should the parser interpret the above piece of code ? Operator or parameter ? If such a distinction is defined on strong grounds, i have failed to see it stated anywhere in the docs. Until then, based on experience with SQL code from alot of dbms, c++ (operators, overloading, etc) and php (where $name is a variable), i do belive the error reported is bogus and unjustified. As i was able to see from 7.3.1 docs, part of the operator documentation is written by Tom Lane. Perhaps some insight from the people in charge with SQL functions and/or operators might help clear this issue. Cheers, = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. - Original Message - From: "Tomasz Myrta" <[EMAIL PROTECTED]> To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 3:22 PM Subject: Re: [SQL] SQL function parse error ? Radu-Adrian Popescu wrote: > I'm perfectly aware of the fact that a space solves the issue here. > > What I'm saying is that it is not natural nor common to take some > whitespace > into > account when parsing, since this is not bash language, nor python, as it > shouldn't be ! > This is SQL, and people who are using PostgreSql write SQL, not > whitespace-sensitive SQL, bash or whatever. > > And besides, like I have already pointed out, look at php's language > parser > (behavior, not source) : > the statement if(100>$a) is perfectly legal, as it should be. > Is there any operator named >$ ? > > Anyone who has used anything from Mysql to Oracle will get quite > annoyed on > this one. > > Hope the people in charge of the parser will get to the bottom of this... > ... and please forgive my caustic tone. > > = > Radu-Adrian Popescu > CSA, DBA, Developer > Aldratech Ltd. I think you are absolutely wrong. It is possible in Postgresql to overload operators and if you want, you can create operator named ">$". There is a lot of useful things in postgresql documentation. If you read it, you could find this: >CREATE OPERATOR defines a new operator, name. The user who defines an >operator becomes its owner. >The operator name is a sequence of up to NAMEDATALEN-1 (31 by default) >characters from the following list: >+ - * / < > = ~ ! @ # % ^ & | ` ? $ Regards, Tomasz Myrta ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL function parse error ?
On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote: > > > Why is that ? Because the >$ does not exist, not in the default operator > list (also there is no operator defined > using $ anywhere within). And because whitespacing the code solves the > problem, which is rather thin, i must say. > Radu-Adrian, i think the parser is built with yacc, (not "from scratch code") so maybe finding if ">$" is in the specific DB's operators would require code that whould slower the whole parsing process (imagine what it means for performance). == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL function parse error ?
Hello, nice to hear from you. I sent my message just before this one
arrived, sorry... :(
About the
>Nonsense. SQL syntax is space-sensitive. Or have you successfully
>written
>SELECTXFROMY;
>lately?
I do hope this is a joke. If not, it's an insult.
If it's not even that, then the joke's not on me, as it is quite obvious
that the "selectxfromy" counter-example
is bogus and childish.
Look at the following C code:
intmain(){return-10;}// no good
int main(){return-10;}// quite valid
Or the the following SQL code :
SELECT * from test where age>23;
SELECT * from test where age
>
23;
Now that will work on any given sql database, both queries. And the C
program will compile with most C compilers.
The fact that >$1 gets interpreted as an operator when there is no operator
>$ is nonsense.
I belive there is an issue here, and it's quite frustrating to see it
dismissed with such childish replies.
I have been working with PostgreSql for two years now, and i like it very
much. The one reason i sent out the first email
was to report something which striked me as odd in the first place (even if
it took under 10 seconds to get it fixed), something
that will make people just starting out with postgresql (like some of my
colleagues here) turn to me and laugh their heads off,
people having years of experience with, say, MSSql.
This is supposed to be constructive, not slaping eachother.
For that reason, i appologize for whatever harsh remarks i've made, and
simply hope to get a straight answer or even better a
conversation.
Regards,
=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 4:48 PM
Subject: Re: [SQL] SQL function parse error ?
"Radu-Adrian Popescu" <[EMAIL PROTECTED]> writes:
> This is SQL, and people who are using PostgreSql write SQL, not
> whitespace-sensitive SQL, bash or whatever.
Nonsense. SQL syntax is space-sensitive. Or have you successfully
written
SELECTXFROMY;
lately?
There has occasionally been talk of disallowing '$' as a valid character
in operator names, which would eliminate the syntactic ambiguity in this
example. But undoubtedly it would also break a few applications that
use '$' in user-defined operator names, so the proposal hasn't passed
to date.
> Is there any operator named >$ ?
Whether there is one in the standard distribution is quite irrelevant.
It's a valid operator name according to the current rules, and so open
to definition by anyone who wants to.
The most recent discussion I can find about this is the pgsql-hackers
thread "Dollar in identifiers" from Aug 2001, eg
http://archives.postgresql.org/pgsql-hackers/2001-08/msg00629.php
There didn't seem to be a consensus to change things, so the old
behavior stands, for the moment.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] SQL function parse error ?
Since you can overload and define new operators, the parser must - at some point in time - lookup the operator definition. It seems to me (but this is just an ideea), that the rules should go like this : ... check >$ is a defined operator if true, it is applied to left side and 1, in my example. if not, check that $1 is a valid expression (which it is) ...go on... This should do the trick. And you would be able to write leftSide>$$1 and would get the >$ operator applied to leftSide and $1. And leftSide>&1 would apply the >& operator to 1, and my code would then have a [very suble] bug. Simply put, operator precedence over local identifiers/parameters, which i belive is a de facto standard in most languages (no flames please !) :) Cheers, = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. - Original Message - From: "Achilleus Mantzios" <[EMAIL PROTECTED]> To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 9:29 PM Subject: Re: [SQL] SQL function parse error ? On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote: > > > Why is that ? Because the >$ does not exist, not in the default operator > list (also there is no operator defined > using $ anywhere within). And because whitespacing the code solves the > problem, which is rather thin, i must say. > Radu-Adrian, i think the parser is built with yacc, (not "from scratch code") so maybe finding if ">$" is in the specific DB's operators would require code that whould slower the whole parsing process (imagine what it means for performance). == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL function parse error ?
On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote: > Since you can overload and define new operators, the parser must - at some > point in time - lookup the operator definition. > It seems to me (but this is just an ideea), that the rules should go like > this : > ... > check >$ is a defined operator > if true, > it is applied to left side and 1, in my example. > if not, > check that $1 is a valid expression (which it is) > ...go on... > This should do the trick. And you would be able to write leftSide>$$1 and > would get the >$ operator applied to leftSide and $1. > And leftSide>&1 would apply the >& operator to 1, and my code would then > have a [very suble] bug. > Simply put, operator precedence over local identifiers/parameters, which i > belive is a de facto standard in most languages (no flames please !) [It's only an issue when you can define IIRC there are issues with allowing table lookups in parse phase which the above would require (otherwise you couldn't find out if >$ is defined). I believe it currently just makes an operator out of the longest sequence of valid operator characters. Also, I'm not sure how you'd get the above to work with bison which is a fairly big deal. If you can write a solution that meets all of the arguments that were made the last time there was a discussion about it, it'd probably be accepted. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL function parse error ?
Achilleus Mantzios <[EMAIL PROTECTED]> writes: > On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote: >> Why is that ? Because the >$ does not exist, not in the default operator >> list > i think the parser is built with yacc, (not "from scratch code") so > maybe finding if ">$" is in the specific DB's operators > would require code that whould slower the whole parsing > process (imagine what it means for performance). There are a couple of good reasons why parsing strings into tokens does not depend on looking to see which operators actually exist (as opposed to which ones *could* exist per the defined rules for operator names): 1. It'd be impractical to detect whether the effective parsing rules are complete or consistent, if they depend on the contents of database tables that will vary from one installation to another. 2. The lexer and grammar stages of parsing cannot look into the database state, because they have to be executable outside a transaction. Otherwise we'd have problems with detecting/processing BEGIN, COMMIT, ROLLBACK statements. (Speed would probably be a significant issue too, though I don't have any hard facts to back up that feeling. We'd definitely have to abandon the use of lex/flex tools to generate the lexing code.) Because of these issues, the question of whether ">$" actually is defined as an operator in a particular installation is irrelevant to how we split character strings into tokens. The only way we have to adjust this behavior is by changing the rules about what an operator name could be, for everyone. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL function parse error ?
Please let me state again where i stand regarding this issue, apart from tech stuff. >From the viewpoint of someone who has worked with databases for quite some time, including postgresql (2yrs), and is making a living out of it, -- that would be me :) -- it is a very odd and unpleasant behaviour. That's a simple fact. However, the feeling slips away in about 5 minutes or so, even if i'm writing all db scripts by hand and have to pay attention to this quite often. That's because i like pgsql, i enjoy compiling, testing, tweaking configuration, trying to push the load thru the roof and stuff like that. But that's me. I am not at all bothered by this issue anymore. What i'm saying is that i know that some of my colleagues, nice guys for that matter, and good programmers, will come screaming to me "what's with the b.s. error ?!?", and when i'll tell them that the sql parser belives that's an inexisting operator, they'll start cursing at it, just like i did. For what it's worth, some policy should be enforced, because it shouldn't matter how many spaces you put between the operator and the operand, as writing SELECT * is the same as SELECT *. I rest my case. Cheers, = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Achilleus Mantzios" <[EMAIL PROTECTED]> Cc: "Radu-Adrian Popescu" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 5:57 PM Subject: Re: [SQL] SQL function parse error ? Achilleus Mantzios <[EMAIL PROTECTED]> writes: > On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote: >> Why is that ? Because the >$ does not exist, not in the default operator >> list > i think the parser is built with yacc, (not "from scratch code") so > maybe finding if ">$" is in the specific DB's operators > would require code that whould slower the whole parsing > process (imagine what it means for performance). There are a couple of good reasons why parsing strings into tokens does not depend on looking to see which operators actually exist (as opposed to which ones *could* exist per the defined rules for operator names): 1. It'd be impractical to detect whether the effective parsing rules are complete or consistent, if they depend on the contents of database tables that will vary from one installation to another. 2. The lexer and grammar stages of parsing cannot look into the database state, because they have to be executable outside a transaction. Otherwise we'd have problems with detecting/processing BEGIN, COMMIT, ROLLBACK statements. (Speed would probably be a significant issue too, though I don't have any hard facts to back up that feeling. We'd definitely have to abandon the use of lex/flex tools to generate the lexing code.) Because of these issues, the question of whether ">$" actually is defined as an operator in a particular installation is irrelevant to how we split character strings into tokens. The only way we have to adjust this behavior is by changing the rules about what an operator name could be, for everyone. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL function parse error ?
On Thu, 9 Jan 2003, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote: > >> Why is that ? Because the >$ does not exist, not in the default operator > >> list > > > i think the parser is built with yacc, (not "from scratch code") so > > maybe finding if ">$" is in the specific DB's operators > > would require code that whould slower the whole parsing > > process (imagine what it means for performance). > Because of these issues, the question of whether ">$" actually is > defined as an operator in a particular installation is irrelevant to > how we split character strings into tokens. The only way we have to > adjust this behavior is by changing the rules about what an operator > name could be, for everyone. Although the rules could be similar to those for + and - at the end of operator strings (no $ at the end of an operator unless it contains characters not normally in SQL92 operators). I'm not sure that behavior is sensible either, but if someone wanted to do it for their own installation it's about a 2 line patch. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL function parse error ?
Stephan Szabo <[EMAIL PROTECTED]> writes: > Although the rules could be similar to those for + and - at the end of > operator strings (no $ at the end of an operator unless it contains > characters not normally in SQL92 operators). I'm not sure that > behavior is sensible either, but if someone wanted to > do it for their own installation it's about a 2 line patch. It could be done that way. But given that "$" already has one weird special case in the operator name rules (ie, it can't be the only character of an operator name), I feel that we'd be making things overly complicated. The proposal back in Aug 2001 was to remove "$" from the set of operator name characters altogether (which would allow us to use it in identifiers instead, improving Oracle compatibility). I originally objected to that idea on backwards-compatibility grounds, but I'm leaning more and more to the view that it's the right thing to do. I've re-opened the thread on pgsql-hackers about this, and we'll see whether any consensus emerges this time. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL function parse error ?
Nice to see that things are starting to move. I was wandering however whether I've succeeded in making a point. Regards, = Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> Subject: Re: [SQL] SQL function parse error ? Stephan Szabo <[EMAIL PROTECTED]> writes: > Although the rules could be similar to those for + and - at the end of > operator strings (no $ at the end of an operator unless it contains > characters not normally in SQL92 operators). I'm not sure that > behavior is sensible either, but if someone wanted to > do it for their own installation it's about a 2 line patch. It could be done that way. But given that "$" already has one weird special case in the operator name rules (ie, it can't be the only character of an operator name), I feel that we'd be making things overly complicated. The proposal back in Aug 2001 was to remove "$" from the set of operator name characters altogether (which would allow us to use it in identifiers instead, improving Oracle compatibility). I originally objected to that idea on backwards-compatibility grounds, but I'm leaning more and more to the view that it's the right thing to do. I've re-opened the thread on pgsql-hackers about this, and we'll see whether any consensus emerges this time. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] insert rule doesn't see id field
On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote: > On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: > > > > I thought that the idea behind noup was to protect single columns from > > > update. However, when I apply the noup trigger as above, I can't > > > update /any/ column. Is this the intended behaviour? > > > > Idly looking at the source code for contrib/noupdate/noup.c, I don't > > believe that it has ever worked as advertised: it seems to reject any > > non-null value for the target column, independently of whether the > > value is the same as before (which is what I'd have thought it should > > do). > > > > Is anyone interested in fixing it? Or should we just remove it? > > If it's been there since 6.4 and you're the first person to try to use > > it, as seems to be the case, then I'd have to say that it's a waste of > > space in the distribution. > > I'm going to see if I can create this function. Well, I think I've thunk something up. Of course I'm happy to submit my modification for distribution or ridicule, as the case may be. Where should I submit this? I made a function noupcols() which takes one or more column names as arguments. The function then creates a new tuple by getting the old values for those columns, and then doing an SPI_modifytuple on the new tuple using the old values for those columns. I'm kind of flying by the seat of my pants here, so if anyone would care to critically review my code, by all means... -- Ron Peterson -o) Network & Systems Manager /\\ Mount Holyoke College_\_v http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] insert rule doesn't see id field
> On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote: >> On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: >> >> > > I thought that the idea behind noup was to protect single columns >> from >> > > update. However, when I apply the noup trigger as above, I can't >> > > update /any/ column. Is this the intended behaviour? >> > >> I'm going to see if I can create this function. > > Well, I think I've thunk something up. Of course I'm happy to submit > my modification for distribution or ridicule, as the case may be. > Where should I submit this? > > I made a function noupcols() which takes one or more column names as > arguments. The function then creates a new tuple by getting the old > values for those columns, and then doing an SPI_modifytuple on the new > tuple using the old values for those columns. > > I'm kind of flying by the seat of my pants here, so if anyone would > care to critically review my code, by all means... Sounds similar to the plpgsql example at: http://www.archonet.com/pgdocs/lock-field.html which silently discards changes. It's trivial (apart from quoting issues) to write a trigger generator to customise the above in plpgsql (see the Cookbook on techdocs.postgresql.org for examples) - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL function parse error ?
On Thu, 2003-01-09 at 11:29, Radu-Adrian Popescu wrote: > What i'm saying is that i know that some of my colleagues, nice guys for > that matter, and good programmers, will come screaming > to me "what's with the b.s. error ?!?", and when i'll tell them that the sql > parser belives that's an inexisting operator, they'll start > cursing at it, just like i did. > Does oracle or mysql or whichever db you like allow the use of $ in user defined operators? If so, how do they know the difference? For what it's worth, some policy should be enforced, because it shouldn't > matter how many spaces you put between the operator > and the operand, as writing SELECT * is the same as SELECT > *. > I rest my case. > Thats an invalid comparison. The problem is not that foo > $1 doesn't work, as your example put forth. The problem is that foo>$1 doesn't work, which by comparison would be SELECT* which would also not work. Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Search and Replace
On Thu, 9 Jan 2003, Ross J. Reedstrom wrote: > On Thu, Jan 09, 2003 at 11:00:32AM +0530, Rajesh Kumar Mallah. wrote: > > > > any anyone explain whats wrong with the replace based solution to this problem > > which i posted earlier? > > > > did i misunderstood anything? > > Probably just overkill - I'm sure it would work, but, based on how the > question was asked, I guessed that the original questioner was looking > for a quick, onetime fix sort of thing, and wasn't real comfortable with > SQL, let alone adding contrib extension products to the installation. > > Ross > > Hi Rajesh, I don't know why but the example you gave me did not work. Here is what happened when I tried: == pubs_test=# UPDATE publications SET url = replace( 'www.srs.fs.fed.us' , 'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%'; ERROR: Function 'replace(unknown, unknown, text)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts == Obviously I am a newbie at PostgreSQL (I have also joined the Novice mailing list to learn more) so I can not tell you why your example did not work, perhaps someone else with more experience can add some information. Best, --rdm ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [NOVICE] [SQL] Search and Replace
On Thu, Jan 09, 2003 at 19:15:51 -0500, "Randy D. McCracken" <[EMAIL PROTECTED]> wrote: > > Hi Rajesh, > > I don't know why but the example you gave me did not work. Here is what > happened when I tried: > > == > > pubs_test=# UPDATE publications SET url = replace( 'www.srs.fs.fed.us' , > 'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%'; > > ERROR: Function 'replace(unknown, unknown, text)' does not exist > Unable to identify a function that satisfies the given argument types > You may need to add explicit typecasts The arguments to replace were in the wrong order. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Getting sequence value after inserting many rows at a time
Hi, I have a PHP script that do something like this: INSERT INTO table_with_sequence (field1, field2, fieldn) SELECT field1, field2, ..., fieldn FROM table,table WHERE condition, condition My doubt: Will the rows just inserted in "table_with_sequence" always be sequencial? (won't it happen to have X rows from this INSERT, 1 rows from another INSERT in the middle and the rest rows of the first INSERT?) How can I safely get the first value the INSERT "generated" for the sequence? []'s Mauricio
Re: [SQL] Getting sequence value after inserting many rows at a time
On Thu, Jan 09, 2003 at 22:48:11 -0200, Maurício Sessue Otta <[EMAIL PROTECTED]> wrote: > Hi, > > I have a PHP script that do something like this: > > INSERT INTO table_with_sequence (field1, field2, fieldn) > SELECT field1, field2, ..., fieldn FROM table,table > WHERE condition, condition > > My doubt: > Will the rows just inserted in "table_with_sequence" always be sequencial? > (won't it happen to have X rows from this INSERT, 1 rows from another INSERT > in the middle and the rest rows of the first INSERT?) > > How can I safely get the first value the INSERT "generated" for > the sequence? You might want to reconsider the design. You might want to use another serial column to indicate the grouping. This will allow you to pick out records inserted as a group. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] insert rule doesn't see id field
On Thu, Jan 09, 2003 at 04:50:56PM -0500, Ron Peterson wrote:
> colindices = (int *) malloc (ncols * sizeof (int));
Of course we should verify that malloc succeeded...
if (colindices == NULL) {
elog (ERROR, "noupcol: malloc failed\n");
SPI_finish();
return PointerGetDatum (NULL);
}
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College_\_v
http://www.mtholyoke.edu/~rpeterso
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] insert rule doesn't see id field
Ron Peterson <[EMAIL PROTECTED]> writes:
> On Thu, Jan 09, 2003 at 04:50:56PM -0500, Ron Peterson wrote:
>> colindices = (int *) malloc (ncols * sizeof (int));
> Of course we should verify that malloc succeeded...
Actually, the correct answer is "you should not be using malloc() in
backend functions". You should be using palloc, or possibly
MemoryContextAlloc, either of which will elog if it can't get space.
> if (colindices == NULL) {
> elog (ERROR, "noupcol: malloc failed\n");
> SPI_finish();
> return PointerGetDatum (NULL);
> }
This is even more pointless. Control does not return from elog(ERROR),
so the two following lines are dead code.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
