[SQL] PostgreSQL 7.3.1 multiple schema select query error: java.sql.SQLException: ERROR: parser: parse error at or near "."
I am using postgresql 7.3.1 on windows 2000. I
downloaded the lattest postgres jdbc driverfor version 7.3.1, called
pg73jdbc3.jar and using JBoss-3.04_Tomcat-4.0.6.
I created one database called 'testdb', and a
scheme called 'sc_test', with a table called 'tbl_company' under the sc_test
scheme.When i execute the following query from java code, i get the
following error:
java.sql.Statement stmt1 =
conn.createStatement();Resultset rs = stmt1.executeQuery("SELECT * FROM
sc_test.tbl_company");
15:43:39,877 ERROR [STDERR] java.sql.SQLException: ERROR: parser:
parse error at or near "."15:43:39,877 ERROR
[STDERR] at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)15:43:39,877
ERROR [STDERR] at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)15:43:39,888
ERROR [STDERR] at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)15:43:39,888
ERROR [STDERR] at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)15:43:39,888
ERROR [STDERR] at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)15:43:39,898
ERROR [STDERR] at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:141)15:43:39,908
ERROR [STDERR] at
org.jboss.resource.adapter.jdbc.local.LocalStatement.executeQuery(LocalStatement.java:211)15:43:39,908
ERROR [STDERR] at
com.ilium.eapps.compmenu.dao.GenericModuleDAO.list(GenericModuleDAO.java:246)15:43:39,908
ERROR [STDERR] at
com.ilium.eapps.compmenu.ejb.sb.SBModuleBean.getList(SBModuleBean.java:258)15:43:39,918
ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)15:43:39,918
ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)15:43:39,918
ERROR [STDERR] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
15:43:39,928 ERROR [STDERR] at
java.lang.reflect.Method.invoke(Method.java:324)15:43:39,928 ERROR
[STDERR] at
org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:660)15:43:39,938
ERROR [STDERR] at
org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:186)15:43:39,938
ERROR [STDERR] at
org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:77)15:43:39,948
ERROR [STDERR] at
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:107)15:43:39,948
ERROR [STDERR] at
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:178)
15:43:39,958 ERROR [STDERR] at
org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:60)15:43:39,958
ERROR [STDERR] at
org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:130)15:43:39,958
ERROR [STDERR] at
org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:204)15:43:39,968
ERROR [STDERR] at
org.jboss.ejb.StatelessSessionContainer.invoke(StatelessSessionContainer.java:313)15:43:39,968
ERROR [STDERR] at
org.jboss.ejb.Container.invoke(Container.java:712)15:43:39,968 ERROR
[STDERR] at
org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)15:43:39,978
ERROR [STDERR] at
org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:98)15:43:39,978
ERROR [STDERR] at
org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:102)15:43:39,978
ERROR [STDERR] at
org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:77)15:43:39,988
ERROR [STDERR] at
org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:80)15:43:39,988
ERROR [STDERR] at
org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(StatelessSessionInterceptor.java:111)15:43:39,988
ERROR [STDERR] at
org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:76)15:43:39,998
ERROR [STDERR] at $Proxy33.getList(Unknown
Source)15:43:39,998 ERROR [STDERR] at
com.ilium.eapps.compmenu.controller.ModuleAction.performList(ModuleAction.java:163)15:43:39,998
ERROR [STDERR] at
com.ilium.eapps.compmenu.controller.ModuleAction.execute(ModuleAction.java:131)15:43:40,008
ERROR [STDERR] at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:465)15:43:40,008
ERROR [STDERR] at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)15:43:40,008
ERROR [STDERR] at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1422)15:43:40,018
ERROR [STDERR] at
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:505)15:43:40,018
ERROR [STDERR] at
javax.servlet.http.HttpServlet.service(HttpServlet.java:740)15:43:40,018
ERROR [STDERR] at
javax.servlet.http.HttpServl
Re: [SQL] efficient count/join query
Gary Stainburn wrote: On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: Hi Tomasz, I don't think you understand what I mean. The history table could be thought of as the following SQL statement if the data had actually existed. This table actually represents a manually input summary of the pre-computerised data. insert into history select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid; If I have a history of hsid | hjid | hcount --+--+ 1 |2 | 3 1 |3 | 1 5 |5 | 4 6 |5 | 3 9 |4 | 4 14 |5 | 4 and I have a roster of rodate | rogid | rojid | rosid ---+---+---+--- 2003-02-15 | 1 | 2 | 1 2003-02-15 | 1 | 5 | 5 2003-02-16 | 1 | 5 | 1 I want my view to show hsid | hjid | hcount --+--+ 1 |2 | 4 1 |3 | 1 1 |5 | 1 5 |5 | 5 6 |5 | 3 9 |4 | 4 14 |5 | 4 I understood your problem well and I just thought some idea will be enough to continue work. Here is detailed query for your problem: create view some_view as select coalesce(hjid,rjid) as jid, coalesce(hsid,rsid) as sid, coalesce(hcount,1)+count(*)-1 as hcount from history full outer join roster on (hjid=rjid and hsid=rosid) group by hjid,rjid,hsid,rosid; Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] which will be faster? w/ or w/o indices
Val, > Can't really say without testing - it'll depend on the size of the various > tables, X and Y disk speeds etc. The only suggestion I can make is to try > an EXPLAIN (Reference manual, SQL commands) with and without > ENABLE_SEQSCAN off (Admin guide ch 3.2). Please use EXPLAIN ANALYZE instead of just EXPLAIN.That will show you whether you have performance problems due to things outside of the planner's control. Keep in mind, though, that if your system has no special problems and your planner statistics are up to date, the planner will do a good job of deciding whether or not indexes are faster. You don't need to start out second-guessing the planner ... you're likely to be wrong more often that it is. Save that for real problem queries. Also, upgrade to 7.2.4!!!7.2.1 has several significant bugs, including two security exploits and one backup-and-restore bug that could make it difficult to recover your database from a backup file. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] "function has no parameter $1" - help.
In > 7.3 you can also CREATE OR REPLACE FUNCTION xxx() RETURNS TRIGGER AS' BEGIN END'LANGUAGE plpgsql; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] update column with rowtype
Hi.. I try "rowtype" in trigger, ex: create or replace function fn_tr_aiu_pdcblc() returns trigger as ' declare c_pdcblc cursor for select * from t_pdcblc where fc_branch=new.fc_branch and fc_bankacct=new.fc_bankacct; balance decimal(30,5); row_pdcblc t_pdcblc%rowtype; begin balance:=0; if (select count(*) from t_pdcblc where fc_branch=new.fc_branch and fc_bankacct=new.fc_bankacct and fd_trxdate0 then balance:=new.fm_balance from t_pdcblc where fc_branch=new.fc_branch and fc_bankacct=new.fc_bankacct and fd_trxdate=new.fd_trxdate) loop fetch c_pdcblc into row_pdcblc; row_pdcblc.fm_balance := balance+row_pdcblc.fm_masuk-row_pdcblc.fm_keluar; raise notice ''balance %'',row_pdcblc.fm_balance; new.fm_balance := row_pdcblc.fm_balance; end loop; close c_pdcblc; return new; end;' language 'plpgsql'; the result of this trigger is; oid | fd_trxdate| fm_masuk| fm_keluar | fm_balance +-+ +---+ 562784 | 2003-02-06 00:00:00 | 1000.0 | 0.0 |0.0 and which command i could used to fill column fm_balance with 1000? please gimme some idea...??? thank's betty thank's ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] efficient count/join query
On Friday 07 Feb 2003 12:57 pm, Gary Stainburn wrote: > > Thinking about it, I'm not wanting to perform a join as such, but a merge > of the two selects below, then some form of group by to sum() the two > counts. > > select rosid as sid, rojid as jid, count(*) as count > from roster group by sid, jid order by sid, jid; > select hsid as sid, hjid as jid, hcount as count > from history order by sid, jid; > > so that > > 1 2 1 > 1 3 2 > > and > > 1 3 1 > 1 4 2 > > becomes > > 1 2 1 > 1 3 3 > 1 4 2 You want a UNION, something like (untested): CREATE VIEW all_counts AS SELECT rosid as sid, rojid as jid, count(*) as all_count FROM roster GROUP BY sid,jid UNION ALL SELECT hsid,hjid,hcount FROM history; SELECT sid,jid,SUM(all_count) FROM all_counts GROUP BY sid,jid; -- Richard Huxton ---(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] prosgrees + java + trasnacciones
Hola a todos! Intento hacer una aplicacion en java que va contra pgsql y me gustaria bloquear registros. Un jps llama a un metodo de una clase para modificar un registro. Lo primero que hace es una consulta del registro (aquí quiero bloquear el registro) y despues se muestra en el navegador el resultado, un usuario lo modifica, pulsa el botón de actualizar y lo actualiza, desbloqueo. He probado con select for update y lo hace bien, solo que si un usuario quiere consultarlo para modificar tambien el mismo registro, se queda esperando a que el otro acabe la actualización. Hay alguna manera de detectar que ese registro se está consultando en select for update para que no se quede esperando o hay alguna forma de hacerlo? Gracias por todos y un saludo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] efficient count/join query
On Friday 07 Feb 2003 1:26 pm, Tomasz Myrta wrote: > Gary Stainburn wrote: > > On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > > > > > Hi Tomasz, > > [snip] > > I understood your problem well and I just thought some idea will be enough > to continue work. > > Here is detailed query for your problem: > > create view some_view as > select > coalesce(hjid,rjid) as jid, > coalesce(hsid,rsid) as sid, > coalesce(hcount,1)+count(*)-1 as hcount > from > history > full outer join roster on (hjid=rjid and hsid=rosid) > group by hjid,rjid,hsid,rosid; > > Regards, > Tomasz Myrta Many appologoes Tomasz. Because your select *LOOKED* wrong to me, I didn't even try it. Upon looking at it again I can see what you're doing. When I tried, it complained about the counts and grouping, so I moved the count(*) to a sub-select and changed the coalesce and it's working. Thanks, Gary > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] efficient count/join query
Gary Stainburn wrote: On Friday 07 Feb 2003 1:26 pm, Tomasz Myrta wrote: Gary Stainburn wrote: Many appologoes Tomasz. No problem Because your select *LOOKED* wrong to me, I didn't even try it. Upon looking at it again I can see what you're doing. When I tried, it complained about the counts and grouping, so I moved the count(*) to a sub-select and changed the coalesce and it's working. There were 2 bugs in this query: - in group by - add field hcount - counting when any roster doesn't exist for some history. Here is (I suppose) final query: create view some_view as select coalesce(hjid,rjid) as jid, coalesce(hsid,rsid) as sid, coalesce(hcount,0)+ sum(case when rjid is not null then 1 else 0 end) as hcount from history full outer join roster on (hjid=rjid and hsid=rosid) group by hjid,rjid,hsid,rosid,hcount; 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
[SQL] "function has no parameter $1" - help.
Title: "function has no parameter $1" - help. I'm trying to create a trigger to enforce a constraint onto two possible foreign tables my function is defined as: <-- snip --> CREATE OR REPLACE FUNCTION sp_check_tranitem_productid(tbl_tranitem.fld_tranitem_id%TYPE, tbl_tranitem.fld_tranitem_type%TYPE) RETURNS OPAQUE AS ' DECLARE ---[Parameters]--- pl_product_id ALIAS FOR $1; pl_product_type ALIAS FOR $2; <-- snip --> but when I call the function I get the error: function has no parameter $1 What is going wrong? (I am using PostgreSQL 7.3.1) , Mike Weaver Software Developer 5, 42 Ladner Street O'Connor, WA, 6163 All correspondence: PO Box Y3502 East St Georges Terrace Perth WA 6832 P: (+618) 9331 2700 F: (+618) 9331 3733 M: 0403 385 181 W: http://www.corpusglobe.com/ E: [EMAIL PROTECTED] This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You.
[SQL] PostgreSQL 7.3.1 multiple schema select query error: java.sql.SQLException: ERROR: parser: parse error at or near "."
I am using postgresql 7.3.1 on windows 2000. I downloaded the lattest
postgres jdbc driver
for version 7.3.1, called pg73jdbc3.jar and using JBoss-3.04_Tomcat-4.0.6.
I created one database called 'testdb', and a scheme called 'sc_test', with
a table called 'tbl_company' under the sc_test scheme.
When i execute the following query from java code, i get the following
error:
java.sql.Statement stmt1 = conn.createStatement();
Resultset rs = stmt1.executeQuery("SELECT * FROM sc_test.tbl_company");
15:43:39,877 ERROR [STDERR] java.sql.SQLException: ERROR: parser: parse
error at or near "."
15:43:39,877 ERROR [STDERR] at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
15:43:39,877 ERROR [STDERR] at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
.java:505
)
15:43:39,888 ERROR [STDERR] at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
ava:320)
15:43:39,888 ERROR [STDERR] at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:48)
15:43:39,888 ERROR [STDERR] at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statem
ent.java:
153)
15:43:39,898 ERROR [STDERR] at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statem
ent.java:
141)
15:43:39,908 ERROR [STDERR] at
org.jboss.resource.adapter.jdbc.local.LocalStatement.executeQuery(LocalState
ment.java
:211)
15:43:39,908 ERROR [STDERR] at
com.ilium.eapps.compmenu.dao.GenericModuleDAO.list(GenericModuleDAO.java:246
)
15:43:39,908 ERROR [STDERR] at
com.ilium.eapps.compmenu.ejb.sb.SBModuleBean.getList(SBModuleBean.java:258)
15:43:39,918 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
15:43:39,918 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
15:43:39,918 ERROR [STDERR] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
15:43:39,928 ERROR [STDERR] at
java.lang.reflect.Method.invoke(Method.java:324)
15:43:39,928 ERROR [STDERR] at
org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(Stateles
sSessionC
ontainer.java:660)
15:43:39,938 ERROR [STDERR] at
org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(Cach
edConnect
ionInterceptor.java:186)
15:43:39,938 ERROR [STDERR] at
org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSe
ssionInst
anceInterceptor.java:77)
15:43:39,948 ERROR [STDERR] at
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor
.java:107
)
15:43:39,948 ERROR [STDERR] at
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.
java:178)
15:43:39,958 ERROR [STDERR] at
org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:60)
15:43:39,958 ERROR [STDERR] at
org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:13
0)
15:43:39,958 ERROR [STDERR] at
org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:204)
15:43:39,968 ERROR [STDERR] at
org.jboss.ejb.StatelessSessionContainer.invoke(StatelessSessionContainer.jav
a:313)
15:43:39,968 ERROR [STDERR] at
org.jboss.ejb.Container.invoke(Container.java:712)
15:43:39,968 ERROR [STDERR] at
org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
15:43:39,978 ERROR [STDERR] at
org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:98)
15:43:39,978 ERROR [STDERR] at
org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:102)
15:43:39,978 ERROR [STDERR] at
org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:77
)
15:43:39,988 ERROR [STDERR] at
org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:80)
15:43:39,988 ERROR [STDERR] at
org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(StatelessSessionInter
ceptor.ja
va:111)
15:43:39,988 ERROR [STDERR] at
org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:76)
15:43:39,998 ERROR [STDERR] at $Proxy33.getList(Unknown Source)
15:43:39,998 ERROR [STDERR] at
com.ilium.eapps.compmenu.controller.ModuleAction.performList(ModuleAction.ja
va:163)
15:43:39,998 ERROR [STDERR] at
com.ilium.eapps.compmenu.controller.ModuleAction.execute(ModuleAction.java:1
31)
15:43:40,008 ERROR [STDERR] at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProces
sor.java:
465)
15:43:40,008 ERROR [STDERR] at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
15:43:40,008 ERROR [STDERR] at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1422)
15:43:40,018 ERROR [STDERR] at
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:505)
15:43:40,018 ERROR [STDERR] at
javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
15:43:40,018 ERROR [STDERR] at
javax.servlet.http.HttpServlet.service(HttpSer
Re: [SQL] "function has no parameter $1" - help.
> > I'm trying to create a trigger to enforce a constraint onto two possible > foreign tables > my function is defined as: > > <-- snip --> > CREATE OR REPLACE FUNCTION > sp_check_tranitem_productid(tbl_tranitem.fld_tranitem_id%TYPE, > tbl_tranitem.fld_tranitem_type%TYPE) RETURNS > OPAQUE AS ' > DECLARE > ---[Parameters]--- > pl_product_id ALIAS FOR $1; > pl_product_type ALIAS FOR $2; > <-- snip --> > > but when I call the function I get the error: > > function has no parameter $1 > > What is going wrong? > > (I am using PostgreSQL 7.3.1) > The documentation says about trigger procedures: PL/pgSQL can be used to define trigger procedures. A trigger procedure is created with the CREATE FUNCTION command as a function with no arguments and a return type of OPAQUE. Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER --- trigger arguments are passed via TG_ARGV, as described below. Refer to that chapter for an example. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] which will be faster? w/ or w/o indices
I understand that if you are importing data into the database that this will go faster if there are no indices against the target table. If I want to run this statement: INSERT INTO table_a SELECT * from table_b WHERE value_1 BETWEEN X AND Y; is it best to do that with or without indices? AND, if I want to run this statement: DELETE FROM table_B WHERE value_1 BETWEEN X AND Y; is it best to do that with or without indices? We are using v 7.2.1 on a Sun E4500. TIA, val ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PostgreSQL 7.3.1 multiple schema select query error:
On Fri, 7 Feb 2003, waimeng wrote:
The Exception is thrown in
public java.sql.ResultSet execute()
method of QueryExecutor.java
This method implements the native protocol of postgres server/client
communication.
So, the message is actually from the backend.
See line 104:
errorMessage.append(pg_stream.ReceiveString(connection.getEncoding()));
and then
if ( errorMessage != null )
throw new SQLException( errorMessage.toString());
and thats where you get your Exception.
Be sure you have the right postgres-service.xml
in /usr/local/jboss-3.0.3/server/default/deploy/.
(or whatever it is for windows).
(Could it be that you are pointing to a 7.2.x backend??)
Also place your postgres jdbc driver in /usr/local/jboss-3.0.3/lib/
Do a simple test
Quit any app that uses the DB.
start your jboss, and then invoke some pages that use your database.
then do
ps -ax | grep postgres
Do you see any lines???
If yes then are you sure you are running 7.3.1???
If no then you are hitting another server.
>
> I am using postgresql 7.3.1 on windows 2000. I downloaded the lattest
> postgres jdbc driver
> for version 7.3.1, called pg73jdbc3.jar and using JBoss-3.04_Tomcat-4.0.6.
>
> I created one database called 'testdb', and a scheme called 'sc_test', with
> a table called 'tbl_company' under the sc_test scheme.
> When i execute the following query from java code, i get the following
> error:
>
> java.sql.Statement stmt1 = conn.createStatement();
> Resultset rs = stmt1.executeQuery("SELECT * FROM sc_test.tbl_company");
>
>
> 15:43:39,877 ERROR [STDERR] java.sql.SQLException: ERROR: parser: parse
> error at or near "."
> 15:43:39,877 ERROR [STDERR] at
> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
> 15:43:39,877 ERROR [STDERR] at
> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> .java:505
> )
> 15:43:39,888 ERROR [STDERR] at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
> ava:320)
> 15:43:39,888 ERROR [STDERR] at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
> ava:48)
> 15:43:39,888 ERROR [STDERR] at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statem
> ent.java:
> 153)
> 15:43:39,898 ERROR [STDERR] at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statem
> ent.java:
> 141)
> 15:43:39,908 ERROR [STDERR] at
> org.jboss.resource.adapter.jdbc.local.LocalStatement.executeQuery(LocalState
> ment.java
> :211)
> 15:43:39,908 ERROR [STDERR] at
> com.ilium.eapps.compmenu.dao.GenericModuleDAO.list(GenericModuleDAO.java:246
> )
> 15:43:39,908 ERROR [STDERR] at
> com.ilium.eapps.compmenu.ejb.sb.SBModuleBean.getList(SBModuleBean.java:258)
> 15:43:39,918 ERROR [STDERR] at
> sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> 15:43:39,918 ERROR [STDERR] at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
> )
> 15:43:39,918 ERROR [STDERR] at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
> .java:25)
>
> 15:43:39,928 ERROR [STDERR] at
> java.lang.reflect.Method.invoke(Method.java:324)
> 15:43:39,928 ERROR [STDERR] at
> org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(Stateles
> sSessionC
> ontainer.java:660)
> 15:43:39,938 ERROR [STDERR] at
> org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(Cach
> edConnect
> ionInterceptor.java:186)
> 15:43:39,938 ERROR [STDERR] at
> org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSe
> ssionInst
> anceInterceptor.java:77)
> 15:43:39,948 ERROR [STDERR] at
> org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor
> .java:107
> )
> 15:43:39,948 ERROR [STDERR] at
> org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.
> java:178)
>
> 15:43:39,958 ERROR [STDERR] at
> org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:60)
> 15:43:39,958 ERROR [STDERR] at
> org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:13
> 0)
> 15:43:39,958 ERROR [STDERR] at
> org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:204)
> 15:43:39,968 ERROR [STDERR] at
> org.jboss.ejb.StatelessSessionContainer.invoke(StatelessSessionContainer.jav
> a:313)
> 15:43:39,968 ERROR [STDERR] at
> org.jboss.ejb.Container.invoke(Container.java:712)
> 15:43:39,968 ERROR [STDERR] at
> org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
> 15:43:39,978 ERROR [STDERR] at
> org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:98)
> 15:43:39,978 ERROR [STDERR] at
> org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:102)
> 15:43:39,978 ERROR [STDERR] at
> org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:77
> )
> 15:43:39,988 ERROR [STDERR] at
> org.jboss.proxy.SecurityInterc
[SQL] SET TIMEOUT equivalent / was: Lock timeout detection
> > > > > > > T1 (within psql): > > > BEGIN; DELETE FROM ; > > > DELETE n > > > > > > T2 (within psql): > > > BEGIN; DELETE FROM ; > > > > > > > ... > > > >I don't think there is a deadlock in the example > > given above. If I'm not mistaken a deadlock occurs if > > both transactions are waiting for each other to > > release the lock (i.e T1 waits for T2 to release > > locks/resources while T2 is also waiting for T1 to > > release locks/resources. In the above example, T1 > > doesn't wait for T2 to do something before finishes > > the transaction (Only T2 is waiting for T1 to finish), > > hence the condition for deadlock is not met. > > > Yupp, I agree. > But from former DBMS I was dealing with, > I know this SET TIMEOUT called feature, which if properly set > terminated processes like that hanging on T2. > Is there something comparable within Postgres? > Sorry to bother again with my question. Is it too stupid or trivial to this list? Should I send it to NOVICE? Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] which will be faster? w/ or w/o indices
> I understand that if you are importing data into the database > that this will go faster if there are no indices against the > target table. > > If I want to run this statement: > INSERT INTO table_a SELECT * from table_b WHERE value_1 BETWEEN > X AND Y; > is it best to do that with or without indices? Can't really say without testing - it'll depend on the size of the various tables, X and Y disk speeds etc. The only suggestion I can make is to try an EXPLAIN (Reference manual, SQL commands) with and without ENABLE_SEQSCAN off (Admin guide ch 3.2). - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] conversi ms-sql7 vs postgresql 7.3
> > I have table xx: > id debet credit balance > 1 10000 0 > 2 2000 0 0 > 3 0 2500 0 > 4 0 100 0 > > command in ms-sql 7 can use calculate field (column) balance from id=1 to > id=4: > "update xx set bal=balance=bal+debet-credit" > result: > id debet credit balance > 1 10000 1000 > 2 2000 0 3000 > 3 0 2500 500 > 4 0 100 400 > > How command sql can use in psotgresql 7.3? > Try UPDATE xx SET balance=balance+debet-credit ; Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] efficient count/join query
Hi folks, I've got two tables, first a history table containing tallies for staff/jobs prior to going live, and second a roster table showing date, diagram, job with one record per person per job per day. the tables are: create table history ( hsidint4 not null references staff(sid), hjidint4 not null references jobs(jid), hcount int4, primary key (hsid,hjid)); create table roster ( rodate date not null, rogid int4 not null references diagrams(gid), rojid int4 not null references jobs(jid), rosid int4 references staff(sid), primary key (rodate, rogid, rojid)); What's the best/quickest/cheapest way to create a view in the format of the history table but including the details from the roster table for all records prior to today. I've been looking at some form of sub-select/join scheme but as some will only exist on the history and some will only exist on the roster while many will exist on both. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] Lock timeout detection in postgres 7.3.1
Christoph Haller wrote: Yupp, I agree. But from former DBMS I was dealing with, I know this SET TIMEOUT called feature, which if properly set terminated processes like that hanging on T2. Is there something comparable within Postgres? PostgreSQL 7.3 Documentation 3.4. Run-time Configuration STATEMENT_TIMEOUT (integer) Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer. DEADLOCK_TIMEOUT (integer) This is the amount of time, in milliseconds, to wait on a lock before checking to see if there is a deadlock condition In this case I suppose 2 things: - table has a lot of records and you should just wait to finish operation. - another query locked the table and it is realy a deadlock Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SET TIMEOUT equivalent / was: Lock timeout detection
Christoph Haller wrote: Sorry to bother again with my question. Is it too stupid or trivial to this list? Should I send it to NOVICE? Regards, Christoph There are no stupid questions, there are only stupid answers ;-) Anyway - try to not create more threads for the same subject if possible. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] efficient count/join query
Gary Stainburn wrote: Hi folks, I've got two tables, first a history table containing tallies for staff/jobs prior to going live, and second a roster table showing date, diagram, job with one record per person per job per day. the tables are: create table history ( hsid int4 not null references staff(sid), hjid int4 not null references jobs(jid), hcount int4, primary key (hsid,hjid)); create table roster ( rodate date not null, rogid int4 not null references diagrams(gid), rojid int4 not null references jobs(jid), rosid int4 references staff(sid), primary key (rodate, rogid, rojid)); What's the best/quickest/cheapest way to create a view in the format of the history table but including the details from the roster table for all records prior to today. I've been looking at some form of sub-select/join scheme but as some will only exist on the history and some will only exist on the roster while many will exist on both. Hello again. What if they exists in both tables - you need only one row result? If yes, you should use FULL OUTER JOIN and COALESCE. select coalesce(hjid,rjid) as jid, coalesce(hsid,rsid) as sid, hcount, rodate, rogid from history full outer join roster on (hjid=rjid and hsid=rosid) Using other names for the same field in other tables comes again - If you have the same name for jid and sid, you wouldn't need coalesce. Regards, Tomasz Myrta ---(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] Lock timeout detection in postgres 7.3.1
> > PostgreSQL 7.3 Documentation > 3.4. Run-time Configuration > STATEMENT_TIMEOUT (integer) > Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer. > DEADLOCK_TIMEOUT (integer) > This is the amount of time, in milliseconds, to wait on a lock before checking to see if there is a deadlock condition > > > In this case I suppose 2 things: > - table has a lot of records and you should just wait to finish operation. > - another query locked the table and it is realy a deadlock > Thanks Tomasz for pointing this out. STATEMENT_TIMEOUT is exactly what I was looking for. I should have had a look into the 7.3 Doc on my own. Regards, Christoph ---(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] conversi ms-sql7 vs postgresql 7.3
On Thursday 06 Feb 2003 10:10 am, betty wrote: > Hi.. > > I have table xx: > id debet credit balance > 1 10000 0 > 2 2000 0 0 > 3 0 2500 0 > 4 0 100 0 > > command in ms-sql 7 can use calculate field (column) balance from id=1 to > id=4: > "update xx set bal=balance=bal+debet-credit" > result: > id debet credit balance > 1 10000 1000 > 2 2000 0 3000 > 3 0 2500 500 > 4 0 100 400 > > How command sql can use in psotgresql 7.3? Three options: 1. Write a function to update the records procedurally (see the plpgsql section of the manuals). I'd probably use a cursor for this. 2. Write a trigger to keep entries up to date as you insert/delete/update entries (basically, same as above but keeps things up to date) 3. Use a sub-query richardh=> SELECT * FROM bank; id | credit | debit | balance ++---+- 1 | 1000 | 0 | 0 2 | 0 | 250 | 0 3 | 2000 | 0 | 0 (3 rows) richardh=> UPDATE bank SET balance = (SELECT sum(credit)-sum(debit) AS newbal FROM bank b2 WHERE b2.id<=bank.id); UPDATE 3 richardh=> SELECT * FROM bank; id | credit | debit | balance ++---+- 1 | 1000 | 0 |1000 2 | 0 | 250 | 750 3 | 2000 | 0 |2750 (3 rows) Note that for lots of rows, this may be slow. You might want to rewrite it in the form b2.balance+b2.credit-b2.debit where b2.id=bank.id-1 but this requires "id" to not have any gaps in the sequence and also needs special logic to handle id=1 (or whatever the smallest "id" is). HTH -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] efficient count/join query
On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > Gary Stainburn wrote: > > Hi folks, > > > > I've got two tables, first a history table containing tallies for > > staff/jobs prior to going live, and second a roster table showing date, > > diagram, job with one record per person per job per day. the tables are: > > > > create table history ( > > hsidint4 not null references staff(sid), > > hjidint4 not null references jobs(jid), > > hcount int4, > > primary key (hsid,hjid)); > > > > create table roster ( > > rodate date not null, > > rogid int4 not null references diagrams(gid), > > rojid int4 not null references jobs(jid), > > rosid int4 references staff(sid), > > primary key (rodate, rogid, rojid)); > > > > What's the best/quickest/cheapest way to create a view in the format of > > the history table but including the details from the roster table for all > > records prior to today. > > > > I've been looking at some form of sub-select/join scheme but as some will > > only exist on the history and some will only exist on the roster while > > many will exist on both. > > Hello again. > > What if they exists in both tables - you need only one row result? > If yes, you should use FULL OUTER JOIN and COALESCE. > > select > coalesce(hjid,rjid) as jid, > coalesce(hsid,rsid) as sid, > hcount, > rodate, > rogid > from > history > full outer join roster on (hjid=rjid and hsid=rosid) > > Using other names for the same field in other tables comes again - > If you have the same name for jid and sid, you wouldn't need coalesce. > > Regards, > Tomasz Myrta Hi Tomasz, I don't think you understand what I mean. The history table could be thought of as the following SQL statement if the data had actually existed. This table actually represents a manually input summary of the pre-computerised data. insert into history select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid; If I have a history of hsid | hjid | hcount --+--+ 1 |2 | 3 1 |3 | 1 5 |5 | 4 6 |5 | 3 9 |4 | 4 14 |5 | 4 and I have a roster of rodate | rogid | rojid | rosid ---+---+---+--- 2003-02-15 | 1 | 2 | 1 2003-02-15 | 1 | 5 | 5 2003-02-16 | 1 | 5 | 1 I want my view to show hsid | hjid | hcount --+--+ 1 |2 | 4 1 |3 | 1 1 |5 | 1 5 |5 | 5 6 |5 | 3 9 |4 | 4 14 |5 | 4 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] efficient count/join query
On Friday 07 Feb 2003 12:09 pm, Gary Stainburn wrote: > On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > > Gary Stainburn wrote: > > > Hi folks, > > > > > > I've got two tables, first a history table containing tallies for > > > staff/jobs prior to going live, and second a roster table showing date, > > > diagram, job with one record per person per job per day. the tables > > > are: > > > > > > create table history ( > > > hsid int4 not null references staff(sid), > > > hjid int4 not null references jobs(jid), > > > hcountint4, > > > primary key (hsid,hjid)); > > > > > > create table roster ( > > > rodatedate not null, > > > rogid int4 not null references diagrams(gid), > > > rojid int4 not null references jobs(jid), > > > rosid int4 references staff(sid), > > > primary key (rodate, rogid, rojid)); > > > > > > What's the best/quickest/cheapest way to create a view in the format of > > > the history table but including the details from the roster table for > > > all records prior to today. > > > > > > I've been looking at some form of sub-select/join scheme but as some > > > will only exist on the history and some will only exist on the roster > > > while many will exist on both. > > > > Hello again. > > > > What if they exists in both tables - you need only one row result? > > If yes, you should use FULL OUTER JOIN and COALESCE. > > > > select > > coalesce(hjid,rjid) as jid, > > coalesce(hsid,rsid) as sid, > > hcount, > > rodate, > > rogid > > from > > history > > full outer join roster on (hjid=rjid and hsid=rosid) > > > > Using other names for the same field in other tables comes again - > > If you have the same name for jid and sid, you wouldn't need coalesce. > > > > Regards, > > Tomasz Myrta > > Hi Tomasz, > > I don't think you understand what I mean. > > The history table could be thought of as the following SQL statement if the > data had actually existed. This table actually represents a manually input > summary of the pre-computerised data. > > insert into history > select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid; > > If I have a history of > > hsid | hjid | hcount > --+--+ > 1 |2 | 3 > 1 |3 | 1 > 5 |5 | 4 > 6 |5 | 3 > 9 |4 | 4 >14 |5 | 4 > > and I have a roster of > > rodate | rogid | rojid | rosid > ---+---+---+--- > 2003-02-15 | 1 | 2 | 1 > 2003-02-15 | 1 | 5 | 5 > 2003-02-16 | 1 | 5 | 1 > > I want my view to show > > hsid | hjid | hcount > --+--+ > 1 |2 | 4 > 1 |3 | 1 > 1 |5 | 1 > 5 |5 | 5 > 6 |5 | 3 > 9 |4 | 4 >14 |5 | 4 Thinking about it, I'm not wanting to perform a join as such, but a merge of the two selects below, then some form of group by to sum() the two counts. select rosid as sid, rojid as jid, count(*) as count from roster group by sid, jid order by sid, jid; select hsid as sid, hjid as jid, hcount as count from history order by sid, jid; so that 1 2 1 1 3 2 and 1 3 1 1 4 2 becomes 1 2 1 1 3 3 1 4 2 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] bytea
That would be convenient but there are some difficulties. Say you have a
function that worked something like:
insert.values (, filetobytea("/home/me/myfile"),...);
It would be a nice ability to have but the server may not be on the same
machine (or even the same type/os/filesystem) as the client so sending a
pathname to the server is of little use. Such an ability must be handled by
the client which can read/translate the local file into the appropriate byte
sequence to send to the server.
I'm toying with a client program in C program that would include the ability
to take a file on the client machine and store it in a bytea field but
haven't come up with a good way to handle the necessary buffer space (ie. I
don't see any way to "stream" data to the server - it appears I need to
allocate sufficient buffer space for the entire file plus the entire escaped
version of the file before I can generate the query which causes me some
other problems.) Any suggestions would be appreciated.
Cheers,
Steve
On Wednesday 05 February 2003 2:25 am, Adrian Chong wrote:
> Hi Christoph,
>
> Thanks for your reply. But what I want to do is loading a file of a
> particular path with a sql statement in psql. Why I need to care about how
> the file looks like? Thanks.
>
> Adrian
>
> - Original Message -
> From: "Christoph Haller" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Wednesday, February 05, 2003 6:15 PM
> Subject: Re: [SQL] bytea
>
> > > I have a table containing a field of type bytea:
> > >
> > > CREATE TABLE a_table (
> > > a_field bytea
> > > );
> > >
> > > How can I import a file in a SQL script? What function I can use?
> >
> > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed
> > significantly since)
> >
> > Octets of certain values must be escaped (but all octet values may be
> > escaped) when used as part of a string literal in an SQL
> > statement. In general, to escape an octet, it is converted into the
> > three-digit octal number equivalent of its decimal octet value, and
> > preceded by two backslashes.
> >
> > In general it goes like this
> > INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ;
> > to load the first four ASCII characters.
> > You did not mention how your file looks like.
> > There is also a C function available called PQescapeBytea
> > which does all the required escaping to store memory areas in bytea
> > columns.
> > Refer to Command Execution Functions within libpq - C Library for
> > details.
> >
> > Regards, Christoph
> >
> >
> >
> > ---(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 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Start and End Day of a Week
Hi, how can I get the first and last day of a week given the year and the week number ??? example week 3 of 2003 starts in Jan 12th and ends on Jan 18th. how to know the last two values given the year and the number of week ? thanks. Best Regards. -- Luis Magaña. Gnovus Networks & Software. www.gnovus.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Start and End Day of a Week
Hi,
I've found my own answer... this is in case somebody needs it someday,
it seems that works properly in PG 7.3, one function gives the start of
the week and the other one the end:
CREATE OR REPLACE FUNCTION weekstart(int4,int4)
RETURNS TIMESTAMP
AS'
DECLARE
year ALIAS FOR $1;
week ALIAS FOR $2;
startsin TIMESTAMP;
BEGIN
SELECT INTO startsin
(SELECT CAST(year || ''-01-01'' AS TIMESTAMP) -
(date_part(''dow'',CAST(year || ''-01-01'' AS
TIMESTAMP))||'' days'')::INTERVAL) +
((week*7)||'' days'')::INTERVAL - ''7
days''::INTERVAL;
RETURN startsin;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION weekend(int4,int4)
RETURNS TIMESTAMP
AS'
DECLARE
year ALIAS FOR $1;
week ALIAS FOR $2;
endsin TIMESTAMP;
BEGIN
SELECT INTO endsin
(SELECT CAST(year || ''-01-01'' AS TIMESTAMP) -
(date_part(''dow'',CAST(year || ''-01-01'' AS TIMESTAMP))||''
days'')::INTERVAL) +
((week*7)||'' days'')::INTERVAL - ''1 days''::INTERVAL;
RETURN endsin;
END;
' LANGUAGE 'plpgsql';
On Fri, 2003-02-07 at 14:57, Luis Magaña wrote:
> Hi,
>
> how can I get the first and last day of a week given the year and the
> week number ???
>
> example
>
> week 3 of 2003 starts in Jan 12th and ends on Jan 18th.
>
> how to know the last two values given the year and the number of week ?
>
> thanks.
>
> Best Regards.
--
Luis Magaña.
Gnovus Networks & Software.
www.gnovus.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] plpgsql + dblink() question
I have a problem with (plpgsql + dblink) function call to another postgresql
database server.
The case is when I call a function (from Server 1 at psql prompt) that will
call dblink to do some operation
on another server(it will take certain time), i.e. Server 2, and meanwhile I
just unplug the network cable to Server 2.
The consequence is that the function will never return except I plug the
cable into it again, moreover I cannot even cancel
the query and stop the postgresql server (have to 'kill -9'.)
My question is, for such case, why doesn't the statement_timeout set on
server 1 work?
I expect it will prompt " . query cancelled .'' as usual when the
statement_timeout expires.
(I have set the statement_timeout to 10 seconds and it works fine except in
the case mentioned above.)
More Description to My Problem
Having the following 2 plpgsql functions installed on both servers (Their
database is identical)
Server 1
Host Name: linux
OS: Redhat Linux 7.2
Postgresql: 7.3.1 (statement_timeout=10seconds)
Server 2
Host Name: linux2
OS: Redhat Linux 7.2
Postgresql: 7.3.1 (statement_timeout=10seconds)
create or replace function test() returns int4 as '
declare
tmp record;
begin
-- it just cannot return from the dblink statement on next line
select * into tmp from dblink(''host=linux dbname=twins'', ''select
mysleep();'') as (retval text);
if tmp.retval=''-1'' then
return -1;
end if;
return 1;
end;
' language 'plpgsql';
create or replace function mysleep() returns text as '
declare
sec int4;
begin
sec = 20 * 15; -- it takes about 15 seconds for the servers to count
while sec > 0 loop
sec := sec - 1;
end loop;
return ''OK'';
end;
' language 'plpgsql';
Under PSQL PROMPT of SERVER 1:
twins=# select test();
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] plpgsql + dblink() question
Frankie wrote:
The case is when I call a function (from Server 1 at psql prompt) that will
call dblink to do some operation
on another server(it will take certain time), i.e. Server 2, and meanwhile I
just unplug the network cable to Server 2.
The consequence is that the function will never return except I plug the
cable into it again, moreover I cannot even cancel
the query and stop the postgresql server (have to 'kill -9'.)
dblink just uses libpq to handle the communication, so you can use the
connect_timeout connection parameter. It defaults to infinite if not
explicitly set. Something like this should set a 5 second timeout:
select * into tmp from dblink(''host=linux dbname=twins connect_timeout=5'',
''select mysleep();'') as (retval text);
See:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/libpq-connect.html
HTH,
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
