[SQL] Problem in writing functions
Hi, I am writing a function in postgresql which inserts a row into the database, but it is not working, can anybody help me out with a simple example. thanx a lot. Kris. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Obtaining primary key information from pg system tables
Hi, I am trying to generate some xml metadata from the pg system tables for my java code generation utility. I have can get most information, but i would like to be able to tell which column is a primary key or which one is a foreign key (i dont need to know which table the foreign key references at this point). Which tables do i need reference, could anyone provide an example query? Kris
Re: [SQL] Obtaining primary key information from pg system tables
Thanx for replying Dmitri, I am aware of the JDBC API exposing information, it might be an idea to use both the system tables and JDBC information in the this case. The reason why i want to use the system tables is for example , (in MS SQL Server and Oracle) the NVARCHAR and NVARCHAR2 datatypes are not part of the JDBC spec and when using JDBC there is no way of telling these part from a VARCHAR or VARCHAR2. I needed to do this with when writing a java based insert script generator so i could cast the strings with the N. I was thinking the system tables were more reliable but i dont need to use just those i guess. Kris - Original Message - From: Dmitri Colebatch To: Kris ; [EMAIL PROTECTED] Sent: Saturday, June 22, 2002 11:44 AM Subject: Re: [SQL] Obtaining primary key information from pg system tables Kris, I'm assuming you dont want to do this, but just in case you're not aware of the option - the JDBC API exposes this information. hth dim - Original Message - From: Kris To: [EMAIL PROTECTED] Sent: Saturday, June 22, 2002 8:30 PM Subject: [SQL] Obtaining primary key information from pg system tables Hi, I am trying to generate some xml metadata from the pg system tables for my java code generation utility. I have can get most information, but i would like to be able to tell which column is a primary key or which one is a foreign key (i dont need to know which table the foreign key references at this point). Which tables do i need reference, could anyone provide an example query? Kris
Re: [SQL] MINUS & ROWNUM in PostGres
On Thu, 4 Sep 2003, Rod Taylor wrote: > > 2 Also I need to find an alternative for ROWNUM in oracle.. > > If you are looking for a unique identifier, try using the OID. > ROWID is oracle's unique identifier, ROWNUM is the row number in a query result. In the past it has been suggested that ROWNUM could be replaced by the nextval of a temporary sequence. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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
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] [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] [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])
Re: [SQL] [JDBC] Error in DatabaseMetaData.getColumns() with Views
On Sat, 3 Jul 2004, Dario V. Fassi wrote: > In the sample adjunct, you can see that error arise at the time when the > view's sql text is parsed and saved in database catalog. > Then generic NUMERIC type is forced for every calculated column without > regard or precision. > And at execute time the f2 column has varying type decimals (in row 2 > you can see 4 decimals and in other rows has 3 decimals), this is not a > behavior , this is an ERROR. It isn't clear that an operation like + should retain the same size restrictions as it's arguments. Consider adding two numeric(6,2) values of .99, how do you handle the overflow? Your other arguments about the sizing of derived columns may make sense for your application, but it is unlikely that they make sense for all users. Note that you can put a cast into your view definition like so: CREATER VIEW v AS SELECT (a+b)::numeric(6,2) FROM tab; Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] org.postgresql.PG_Stream.ReceiveChar(PG_Stream.java:143)
On Mon, 2 Aug 2004, [iso-8859-1] Smita Marda wrote: > Hi, I have been using SOFIA framework and postgres database as a > backend. i noticed as the database started growing larger.. today > encountered some error while firing a query to save a record. The table > in which i am going to save the record, has two columns having datatype > text. the data of these two column contains 2652 and 5817 characters. If > I reduce some characters from both columns, it saves the record > successfully. But i reduce some characters from only single > column(whether it has 2652 chars or 5817 chars whereas both have the > same datatype), it gives me the following error :-- > > The backend has broken the connection. Possibly the > action you have attempted has caused it to close. > at > org.postgresql.PG_Stream.ReceiveChar(PG_Stream.java:143) > at This looks like it could be a server crash. Does the server's log show anything informative? Also you don't mention what server and JDBC driver version you are using, but the stacktrace tells me the driver must be from the 7.2 or earlier release. If the server log doesn't reveal anything you might try upgrading the JDBC driver to a later version. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [JDBC] Calling a table in another database from plpgsql
On Wed, 5 Jan 2005, Dave Cramer wrote: > Kris is correct, this is a jdbc list, however to satisfy your curiosity, > you can't access data from another database. > You certainly can with dblink which is what his question was about. I didn't answer him because I remembered a recent off-topic question from him which I responded to with an answer and a suggestion of the appropriate list. As long as answers keep coming the hint will not be picked up. 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] LIMIT BASED ON PERCENT
Could you not create a function to do this instead? Set var_limit = 20% of row count Replace subquery with var_limit Kris On 18-Nov-09, at 14:27, Guillaume Lelarge wrote: Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : No, It doesn't. In my machine: First select ERROR: syntax error at end of input LINE 1: select * from rapadura.cliente limit 20% ^ Second one: ERROR: argument of LIMIT must not contain subqueries Postgres 8.3 It works for Lee because obviously he's working on a 8.4 server. You can use subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For earlier releases, there's no way to do this in a single query. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
