[SQL]Number of rows effected
Hi, I have a question how to get number of rows effected in last DML in Postgres. actually i am using procedures for each DML so when a update procedure is called i have to return no of rows effected by it. Thanks Pankaj Chugh This email may contain confidential or privileged information for the intended recipient(s). If you are not the intended recipient, please do not use or disseminate the information, notify the sender and delete it from your system. Thanks __ ---(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] [JDBC] Prepare Statement
On Mon, 14 Jun 2004, Jie Liang wrote: > I have a question about performance, in SQL commands: there is a > prepare/execute command, document says it will improve the performance > while repeatly execute a statement. In java.sql: there is a > PreparedStatement object, which can store precompiled SQL statement, > document says it can improve the performance also. > If I use java jdbc to connect postgresql database, which one I should > use? Can I use both? > When using JDBC it is best to use the standard Statement/PreparedStatement interfaces. It is possible to directly use PREPARE/EXECUTE, but this can be handled by the driver. Let me give you a run down of the different driver versions and their capabilities: Current released version: can enable using PREPARE/EXECUTE behind the scenes on PreparedStatement by casting the prepared statement to PGStatement and issuing setUseServerPrepare. Current cvs version: can enable using PREPARE/EXECUTE by setting an execution threshold that will turn it on when reached. This threshold can be set at a number of levels, see the following for more information http://www.ejurka.com/pgsql/docs/cvs/ch09s05.html Soon to be committed cvs version: can directly use server prepared statements without using the SQL level PREPARE/EXECUTE. Kris Jurka ---(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] Scalar subqueries
Not sure what am I missing. I really appreciate if anyone could point it out to me. I've a logs table that has both sign-in and sign-out records which are differentiated by action flag. Records with action flag = (1,2) => sign-in records and action flag = (3,4,5,6,7) => sign-out records. playfield=# select * from logs; log_id | log_creation_date | account_id | user_id | service | action | mac +-++-+-++--- -- 1 | 2004-04-29 10:48:36 | Robert | 3 | 5 | 2 | 00-00-00-00 3 | 2004-04-29 10:53:44 | Robert | 3 | 5 | 3 | 00-00-00-00 5 | 2004-04-29 11:11:35 | Robert | 3 | 5 | 1 | 00-00-00-00 1003 | 2004-05-03 15:18:53 | Robert | 3 | 5 | 5 | 00-00-00-00 1004 | 2004-05-03 15:19:50 | Robert | 8 | 5 | 1 | 00-00-00-00 (5 rows) All I'm trying to do is print signin id and corresponding sign-out id's in single row. Select I wrote : select log_id as signin_id, (select foo.log_id from (select foo1.log_id from logs as foo1 where foo1.action in (3,4,5,6,7) and l.log_id > foo1.log_id order by foo1.account_id, foo1.user_id, foo1.mac, foo1.log_creation_date) as foo limit 1) as signout_id from logs as l where action in (1,2); Gives... signin_id | signout_id ---+ 1 | 5 | 3 1004 | 3 (3 rows) Expected output : signin_id | signout_id ---+ 1 | 3 5 | 1003 1004 | (3 rows) Thanks, Stalin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] pivot-like transformation
Hello, I have a table with measurement values and columns like this: analyses(id, sample_id, parameter[temperatur...], value, unit[°C...], error) With PL/PgSQL at the end I want try to perform a pivot-like arrangement of these data: sample_id|Temperature [°C]|parameter2 [mg/L]|...|parameterN [L/year] -++-+---+--- 5| 23.00|0.036|...| 35.1 My first attempts to only give back the original table within a function failed. There are tons of examples to select one value into a variable... But for more I browsed through the net and my book but I couldn't find anything that works. I'm new to PL/PgSQL. Could anyone show me a _very_simple_ example of how to to read (the columns I like) from a table and return the columns (I like) using tablename%rowtype and an other possiblity if exists. I also found very few sources about handling of arrays and how to fill them up with query results... things like this. Does anyone know a more comprehensive source? Thank you, Torsten ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] question about which column(s) are the right foreign key
Hi, I have objects in my database, and they have an object id generated with a sequence. Then I have object versions. The ids of object versions need to be unique only within one object id. But for simplicity they're generated with a sequence, too. Now I want to reference an object version. I can use just the object version id, because it "happens" to be globally unique. Conceptually though, I should use the object's id and its version's id. Now redundancy is Not Goodâ, so I wonder which way is the Right Oneâ. I understand that both ways work and that the variant with just the object id is probably both faster and smaller. I'd be thankful for enlightenment :) -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] pivot-like transformation
Torsten Lange wrote: Hello, I have a table with measurement values and columns like this: analyses(id, sample_id, parameter[temperatur...], value, unit[?C...], error) With PL/PgSQL at the end I want try to perform a pivot-like arrangement of these data: sample_id|Temperature [?C]|parameter2 [mg/L]|...|parameterN [L/year] -++-+---+--- 5| 23.00|0.036|...| 35.1 Not a direct answer with respect to plpgsql, but for pivot table functionality see the contrib/tablefunc function crosstab(). I also found very few sources about handling of arrays and how to fill them up with query results... things like this. Does anyone know a more comprehensive source? Have you looked at the online docs? http://www.postgresql.org/docs/7.4/static/arrays.html http://www.postgresql.org/docs/7.4/static/functions-array.html http://www.postgresql.org/docs/7.4/static/functions-comparisons.html http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] help with Postgres function
Hello.
I'd appreciate any help with a function I'm writing.
I have a simple table like this:
CREATE TABLE websites (
id BIGSERIAL not null primary key,
domain character varying(256) NOT NULL,
last_fetch timestamp without time zone DEFAULT 'now()',
crawl_status smallint not null DEFAULT 1
);
and my function retrieves the next available row in this table, by age
(the oldest inserted and with status 1)
I have written this but I have problems with it...and also it doesn't
seems right to me...from coding style and performance point of view.
CREATE OR REPLACE FUNCTION getNextWebsiteForCrawl(integer) RETURNS
website AS '
DECLARE
my_record RECORD;
w website%rowtype;
count smallint;
BEGIN
SELECT id, domain into my_record FROM websites WHERE crawl_status=1
AND date(last_fetch) > (current_timestamp - interval ''$1 days'')
ORDER BY last_fetch LIMIT 1;
select count(*) into count from my_record;
if count > 0 then
w.id := my_record.id;
w.domain := my_record.domain;
update websites set crawl_status=2 where id = my_record.id;
end IF;
return w;
END;
' LANGUAGE plpgsql;
The reason I have that IF is for when there are no more rows
available...
when I call this function (SELECT * FROM getNextWebsiteForCrawl(5))
I get an error:
WARNING: Error occurred while executing PL/pgSQL function
getnextwebsiteforcrawl
WARNING: line 8 at select into variables
ERROR: parser: parse error at or near "(" at character 13
Could somebody good show me how to do it better?
Thanks a lot!
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Last insert id
Andrei Bintintan wrote:
"Is it safe to use "select max(table1_id) from table1" after the insert?"
Yes it is safe, but ONLY if you use it inside a transaction.(BEGIN/COMMIT).
BR.
Hi,
I think it would be safe to use :
select currval('tablename_idname_seq');
inside a session to receive the current value of the sequence that feeds
the serial column (i.e. the last inserted value on that session). Every
other session is irrelevant (as the currval returns the session-specific
value).
If I understand correctly, it doesn't even need the transaction to be
open (in which case all consecutive inserts on the same table would block).
M.K.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of mixo
Sent: Wednesday, June 09, 2004 9:24 AM
To: [EMAIL PROTECTED]
Subject: [SQL] Last insert id
I have three tables which are related a serial field, table1_id, in on of
the tables. Updating the tables is done through a transaction. My problem
is, once I have insert a row in the first tables with table1_id, I need for
the other two tables. How can I get this? Is it safe to use "select
max(table1_id) from table1" after the insert?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] use of a composite type in CREATE TABLE?
Hannes, > does anyone know how it is posible to set a composite type as the data > type of a column when creating a new table? This is not yet supported. Hopefully it will be supported in the upcoming version 7.5. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [JDBC] Prepare Statement
On Wed, 16 Jun 2004, Jie Liang wrote: > Kris, > Thank you for your valuable response, I used the code you list > following: > > [7.5 code example] > > Then, the compiler complaint: > ServerSidePreparedStatement.java:20: cannot resolve symbol symbol : > method setPrepareThreshold (int) > location: interface org.postgresql.PGStatement > pgstmt.setPrepareThreshold(3); I downloaded > pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at This example is from the 7.5 documentation and requires a pgdev.302.jdbcX.jar file. I mentioned this cvs example because this functionality is undocumented in the released version. In the 7.4 version the enabling of server side statements is only possible via a boolean flag at the statement level, namely PGStatement.setUseServerPrepare(true); Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] help with Postgres function
On Wed, 16 Jun 2004, ctrl wrote: > CREATE OR REPLACE FUNCTION getNextWebsiteForCrawl(integer) RETURNS > website AS ' > DECLARE > my_record RECORD; > w website%rowtype; > count smallint; You can't safely use a variable named count and the count(*) expression below I think, so you'll want to rename this variable. > BEGIN > SELECT id, domain into my_record FROM websites WHERE crawl_status=1 > AND date(last_fetch) > (current_timestamp - interval ''$1 days'') I don't think that'll get you the interval you want. I think you want current_timestamp - $1 * interval '1 day' instead since the $1 isn't going to get replaced inside the string. > ORDER BY last_fetch LIMIT 1; > select count(*) into count from my_record; > if count > 0 then This isn't going to work either. my_record isn't some kind of recordset, it's a single variable so I the select count(*) from my_record doesn't make sense. Perhaps you could check for my_record.id being non-null. > w.id := my_record.id; > w.domain := my_record.domain; > update websites set crawl_status=2 where id = my_record.id; > end IF; > return w; If you couldn't get a value, what do you want this to return? Right now it returns a website where the values are null I believe. The function as written is not going to guarantee distinct results if used from multiple transactions concurrently. How are you planning to use the function? Many sequential calls from a single connection, random usage from multiple, etc? ---(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] [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote: > Kirs, > > I re-compile with setUseServerPrepare(true), it works fine, thanks. > However, reading from my log file, what I saw is that five same SELECTs > with different argument, so I am wondering that the PrepareStatement > really save time than individualy execute five SELECTs ??? > This is what I see in the log file: 2004-06-17 11:55:35 [23254] LOG: statement: PREPARE JDBC_STATEMENT_1(integer) AS SELECT $1 ; EXECUTE JDBC_STATEMENT_1(1) 2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(2) 2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(3) 2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(4) 2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(5) 2004-06-17 11:55:35 [23254] LOG: statement: DEALLOCATE JDBC_STATEMENT_1 I don't know why this would be different for you. What exact version of the server and driver are you using? Kris Jurka ---(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] [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kris,
> I have another question, I saw some discussion regarding
> PreparedStatement work with array argument, I get a error when I try to
> play with it.
> E.g.
> I have myfunction(int[]),
> So,
> PrepareStatement st = conn.prepareStatment("SELECT myfunction(?)");
> String arr="{1,2,3}";
> St.setString(1,arr};
> Result rs = st.executeQuery();
>
> Then it will complaint when it run:
> Myfuntion(text) does not exist!
>
This is actually a case where prepared statements actually cause trouble.
With the directly executed SELECT myfunction('{1,2,3}'); The backend can
determine that there is only one version of myfunction so it can convert
the unkown argument type to it, but note that this won't work if
myfunction is overloaded.
With the prepared case, you must tell it what types to use when doing the
prepare. The JDBC driver doesn't have a whole lot of information to work
with, so it takes what it knows (that you called setString) and says the
argument is of type text, issuing a prepare like this: PREPARE
JDBC_STATEMENT_1(text) AS SELECT myfunction($1); At this time (before it
actually calls EXECUTE) it tries to lookup myfunction that takes a text
argument and determines there isn't one. In this case it doesn't have the
opportunity to apply any casts because we were quite clear in specifying
that it should take a text argument, not one of unknown type.
Ideally you should be using setArray, but there is no existing way to
create Array objects and I'm not sure that code would work even if there
was.
Kris Jurka
---(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] question about which column(s) are the right foreign key
Markus, > I have objects in my database, and they have an object id generated with > a sequence. Then I have object versions. The ids of object versions need > to be unique only within one object id. But for simplicity they're > generated with a sequence, too. > > Now I want to reference an object version. I can use just the object > version id, because it "happens" to be globally unique. Conceptually > though, I should use the object's id and its version's id. > > Now redundancy is Not Goodâ, so I wonder which way is the Right Oneâ. > Well, conceptually, you should have generated a numerical version id for each object version which would have told you the sequence in which that version was created, i.e. version #1 of object 23421, version #2 of object 23421, etc. This can be automated a number of ways, although it does require locking the object during a version save. The problem with the setup you have now is that you have an Object ID, which doesn't intrinsically mean anything, and an Object Version ID, which also doesn't tell you anything about the object or the version. If you want to keep information about which "edition" of an object this particular object-version is, you'll have to add a column -- which will then make the object-version id redundant, since the table will then have two keys. That's "the Right Oneâ" The concept probably nobody ever told you is that, in relational DB design, you want to minimize the number of columns in your database that contain no real data and exist only for internal purposes. Ideally, one would construct a database in which no surrogate keys or sequences at all; but performance and query-writing considerations make that impossible. However, if fixing this issue is not an option, I'd just use the object-version id as my FK. Unless, of course, you think you might fix the problem later. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kris,
> You are right, I modified that piece of code a little bit,
> CallableStatement stmt = conn.prepareCall("{?=call chr(?)}");
> Then my log file were:
> Select * from chr(65) as result;
> Select * from chr(66) as result;
> ..
> However, if I use:
> PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
> Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
>
> So, I am getting confusion.
> I think CallableStatement is extended from PrepareStatement, it should
> have same behaviou.
>
What's happening here is that you can only use prepared statements for
certain operations. You can't for example prepare a CREATE TABLE
statement. The driver examines the query to see if it is valid for
preparing and I believe the problem here is that with a callable statement
it is examinging the query with "call" before it is transformed to a
SELECT, so it doesn't recognize it as a preparable. This looks like a bug
to me.
Kris Jurka
---(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] [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote: > Hmm, intersting. > I am using jdk 1.3.1, and pg74.213.jdbc2.jar driver, I hope this bug > could be fixed in later version. > I suppose, but I'm going to put it pretty close to the bottom of my todo list because it still works even though it doesn't use a server prepared statement, and as I mentioned earlier the performance improvement if any will be minimal. Have you done any testing to show that you are even getting a performance gain? Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [JDBC] Prepare Statement
On Fri, 18 Jun 2004, Jie Liang wrote: > However, I am still thinking if I call one SELECT and one DELECT and one > UPDATE and one INSERT a thousand times against same table with different > arguments, should I consider performance iusse? Right, this is a case where some benefits can be found, but remember the premature optimization adage. > > Secondly, I assume the function should be a pre-compiled object stored > on server side, doesn't it. > I depends on the language the function is written. plpgsql caches plans, but not all procedural languages do. Kris Jurka ---(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] [JDBC] Prepare Statement
On Fri, 18 Jun 2004, Jie Liang wrote:
> So, I think that PreparedStatement should have a way at least case a
> String to an Array or a way to create a Array, because of
> conn.prepareStatement("SELECT myfunction('{1,2,3}')") is NOT very
> useful.
Right, this is a known issue. It's on the list.
Kris Jurka
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Function Parameters - need help !!!
Hi all, I am a new joinee to this mailing list. I am using PostgreSql for my project. We are trying to port a few stored procedures written in SQL to PGSQL. But i am finding difficulties in doing that. I m new to Postgresqldont know much about it. I tried a lot of books and online documents.no go. I have problems in passing default values to function paraameters. I would really appreciate if anyone can help me out with this. The function I am trying to port is as follows : CREATE PROC PP_ReadPointByValue @SessionID int = NULL, --these r default parameters @SPSID int = 1,--default value @ParameterName nvarchar (50) = NULL, -- if NULL read all parameters @NumValue real = NULL, @StrValue nvarchar (255) = NULL, @ParameterID int = NULL Now how do I specify default parameters to the arguments in Plpgsql . > With Best Regards > Pradeep Kumar P J > Honeywell___ > No.151/1, Doraisanipalya, (Opp. to IIMB) Bannerghatta Rd, Bangalore 560 > 076. > Telephone No +91 80 26588360 / 51197222 xtn. 8211 > Fax No +91 80 26584750 > Website: Honeywell.com > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Strange behaviour updating primary key column.
[Sorry if this is a double post.] Hello all, [Postgresql 7.4.2] CREATE TABLE "story" ( "id" int4 NOT NULL, "title" varchar (100) NOT NULL, "body" text NOT NULL, "entryDate" timestamptz NOT NULL, CONSTRAINT "story_pkey" PRIMARY KEY (id) ); In the above table why does the query: UPDATE "story" SET id = (id + 1500); fails with: "ERROR: duplicate key violates unique constraint 'story_pkey'" (I have 6000 records in the table with "id" spanning from 1 to 6000) I find this behaviour strange, SQL is a set based language, but PostgreSQL is acting like is changing the values in sequence. Can anyone with a better understanding of PostgreSQL explain to me why this happens? Are my expectations wrong? Best regards, Luis Neves ---(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
