Re: [SQL] MINUS & ROWNUM in PostGres
On Mon, 2003-09-08 at 09:44, Jomon Skariah wrote: > Hi, > > Do we have any replacement for REPLACE() of Oracle in PostGres? What does replace() do? String replacement? http://www.postgresql.org/docs/7.3/interactive/functions-string.html replace(string text, from text, to text) signature.asc Description: This is a digitally signed message part
[SQL] undefine currval()
I'm using sequences and currval() to retrieve the last inserted row in a table. If currval() is undefined, as it is when a connection is made, then I know no rows were inserted in that table and can take a different action. This is problematic when using a connection pooling library, as the value of currval() for any given sequence could possibly be set from a previous "connection". One (theoretical) workaround would be to issue some sort of command to the back-end database to wipe all values of currval() when a "new" connection is made. I've done some digging in the system tables and source code, and can't find an obvious solution. Perhaps one you you gurus can suggest a SQL statement to do such a thing. Alternately, if there is a better way to retrieve the last inserted row for any given table, I'd be very grateful for the tip. It would need to be independent of the connection history, and undefined if there has not been a row inserted to the table during a definable interval of time (drop anchor when the "connection" begins, raise anchor when the "connection" ends), and be independant of the other connections inserting rows to the same table. Any idaeas? CG __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] undefine currval()
Chris Gamache <[EMAIL PROTECTED]> writes: > One (theoretical) workaround would be to issue some sort of command to the > back-end database to wipe all values of currval() when a "new" connection is > made. I've done some digging in the system tables and source code, and can't > find an obvious solution. The state involved is in a linked list kept by commands/sequence.c. Such a command would not be difficult to implement, if you could get agreement on the syntax to use. 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] undefine currval()
Chris Gamache wrote: > I'm using sequences and currval() to retrieve the last inserted row in a table. > > > If currval() is undefined, as it is when a connection is made, then I know no > rows were inserted in that table and can take a different action. This is > problematic when using a connection pooling library, as the value of currval() > for any given sequence could possibly be set from a previous "connection". > > One (theoretical) workaround would be to issue some sort of command to the > back-end database to wipe all values of currval() when a "new" connection is > made. I've done some digging in the system tables and source code, and can't > find an obvious solution. Perhaps one you you gurus can suggest a SQL statement > to do such a thing. > > Alternately, if there is a better way to retrieve the last inserted row for any > given table, I'd be very grateful for the tip. It would need to be independent > of the connection history, and undefined if there has not been a row inserted > to the table during a definable interval of time (drop anchor when the > "connection" begins, raise anchor when the "connection" ends), and be > independant of the other connections inserting rows to the same table. I don't know how you could have an application that doesn't know if it has issued a nextval() in the current connection. Unless you can explain that, we have no intention of playing tricks with currval() for connection pooling. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] undefine currval()
On Mon, 8 Sep 2003, Bruce Momjian wrote: > I don't know how you could have an application that doesn't know if it > has issued a nextval() in the current connection. Unless you can explain > that, we have no intention of playing tricks with currval() for > connection pooling. Actually, I would think the very act of using connection pooling would ensure that applications may well not know whether or not a nextval had been called. In other words, how is an application supposed to know if the previous bit of code that used this connection issued a nextval() when you're connection pooling and any piece of code could have run before you. On the other hand, using currval as a test to see if a value has been used is probably not the best way of doing things either. I'd imagine some kind of static or session var would be better suited to that task. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] undefine currval()
"scott.marlowe" <[EMAIL PROTECTED]> writes: > On Mon, 8 Sep 2003, Bruce Momjian wrote: >> I don't know how you could have an application that doesn't know if it >> has issued a nextval() in the current connection. Unless you can explain >> that, we have no intention of playing tricks with currval() for >> connection pooling. > Actually, I would think the very act of using connection pooling would > ensure that applications may well not know whether or not a nextval had > been called. The point is that it's not very sensible to be using currval except immediately after a nextval --- usually in the same transaction, I would think. Certainly, not resetting currval implies that there is *potential* coupling between different transactions that happen to share a connection. But ISTM that such coupling would represent a bug in the application. Chris said he was using currval being undefined to know that no rows were inserted, but this seems less than compelling to me (why not look at the results of the insert commands you used?). I'd support adding a currval-reset feature if someone can make a more compelling argument why a connection-pooling application would need it. There are big chunks of other state in the backend that are not resettable --- prepared statements being one that I think will have much more visibility in 7.4. Should we offer something to let all prepared statements be dropped? Would connection poolers actually find it useful? (I'd think it much more likely they want to re-use prepared statements.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] undefine currval()
On Mon, 8 Sep 2003, Chris Gamache wrote: > I'm using sequences and currval() to retrieve the last inserted row in a table. > > > If currval() is undefined, as it is when a connection is made, then I know no > rows were inserted in that table and can take a different action. This is > problematic when using a connection pooling library, as the value of currval() > for any given sequence could possibly be set from a previous "connection". > > One (theoretical) workaround would be to issue some sort of command to the > back-end database to wipe all values of currval() when a "new" connection is > made. I've done some digging in the system tables and source code, and can't > find an obvious solution. Perhaps one you you gurus can suggest a SQL statement > to do such a thing. > > Alternately, if there is a better way to retrieve the last inserted row for any > given table, I'd be very grateful for the tip. It would need to be independent > of the connection history, and undefined if there has not been a row inserted > to the table during a definable interval of time (drop anchor when the > "connection" begins, raise anchor when the "connection" ends), and be > independant of the other connections inserting rows to the same table. > > Any idaeas? Are you writing in java? If yes, then implementing a wrapper around Connection would be a way. > > CG > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] plpgsql doesn't coerce boolean expressions to boolean
Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test expression of an IF, WHILE, or EXIT statement is a boolean expression. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the expression doesn't return boolean. 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? 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] [GENERAL] plPGSQL bug in function creation
On Monday 08 September 2003 09:32, Marek Lewczuk wrote:
> Hello,
> I think that there is a bug in plPGSQL - or maybe I don't know something
> about this language. Try to create this function
[snip]
> And when i execute: SELECT test(NULL, 'buuu'); -> it returns me NULL
> value, when it should return "buuu". Well I think that something is
> wrong here.
>
> If I will modify this function again to this:
>
> CREATE FUNCTION "public"."test" (varchar, varchar) RETURNS text AS'
[snip]
> Everything is working OK.. So the problem is in TEXT type definition.
>
> I'm using PG 7.3.1 on Win/Cyg
Can't reproduce here:
CREATE OR REPLACE FUNCTION functest1(varchar, varchar) RETURNS varchar AS '
BEGIN
IF $1 THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE OR REPLACE FUNCTION functest2(text, text) RETURNS varchar AS '
BEGIN
IF $1 THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
SELECT functest1('A','B'), functest1(null,'B'), functest2('A','B'),
functest2(null,'B');
CREATE FUNCTION
CREATE FUNCTION
functest1 | functest1 | functest2 | functest2
---+---+---+---
A | B | A | B
(1 row)
richardh=# select version();
version
-
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
Not sure whether this is because of the change 7.3.1->7.3.2 or cygwin vs
linux. Don't have a copy of 7.3.1 to check against, sorry. Perhaps check the
changes list to see if it mentions something like this.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] [GENERAL] plPGSQL bug in function creation
I had the same success using 7.3.2 with Cygwin:
e=# SELECT functest1('A','B'), functest1(null,'B'), functest2('A','B'),
functest2(null,'B');
functest1 | functest1 | functest2 | functest2
---+---+---+---
A | B | A | B
(1 row)
e=# select version();
version
--
PostgreSQL 7.3.2 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2 20020927
(prerelease)
(1 row)
George
- snip -
> Not sure whether this is because of the change 7.3.1->7.3.2 or cygwin vs
> linux. Don't have a copy of 7.3.1 to check against, sorry. Perhaps check
the
> changes list to see if it mentions something like this.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] plPGSQL bug in function creation
"Marek Lewczuk" <[EMAIL PROTECTED]> writes: > CREATE FUNCTION "public"."test" (text, text) RETURNS text AS' > BEGIN > IF $1 THEN > RETURN $1; > ELSE > RETURN $2; > END IF; > END; > 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; If there's a bug here at all, it's that this function doesn't report a type violation. What in the world do you think the semantics of that IF-test are? text is not boolean. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] plPGSQL bug in function creation
Hello,
I think that there is a bug in plPGSQL - or maybe I don't know something
about this language. Try to create this function
Ok., this is the function created in plPGSQL:
CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
BEGIN
IF $1 THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
If you will execute SELECT test('tess', 'erer') -> then "tess" will be
returned. If you will execute: SELECT test(NULL, 'buuu'); -> then it
will return NULL, but it should return "buuu". I tried to figure out why
it is happening so i modifye this function to this:
CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
BEGIN
RETURN 'test';
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
And when i execute: SELECT test(NULL, 'buuu'); -> it returns me NULL
value, when it should return "buuu". Well I think that something is
wrong here.
If I will modify this function again to this:
CREATE FUNCTION "public"."test" (varchar, varchar) RETURNS text AS'
BEGIN
IF $1 THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Everything is working OK.. So the problem is in TEXT type definition.
I'm using PG 7.3.1 on Win/Cyg
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
