Re: [SQL] Something I'd like to try...

[Barry Lind]

It is interesting that this should come up now.  Just last week I was
trying to port a SQL statement from Oracle to Postgresql that used table
aliases in an update statement.  While I can see that this functionality
wouldn't be used very often it can be very useful under certain
circumstances.

I have a table that stores a hierarchy.  Sometimes in an update I want
to join back to the same table to get other information related to
children or parent rows.  In Oracle I can do this using the alias, but
in Postgresql I cannot.

Consider the SQL statements below as simplistic examples of what I was
doing in Oracle:

table_foo
  foo_id int
  parent_foo_id int
  column_a  int
  column_b  int

update table_foo f1
set column_a = (select sum(column_a) from table_foo f2
where f2.parent_foo_id = f1.foo_id);

update table_foo f1
set column_b = 1
where exists (select column_a from table_foo f2
  where f2.parent_foo_id = f1.foo_id);


thanks,
--Barry




Tom Lane wrote:
> 
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Can someone comment on this?
> >>
> >> I just noticed that postgres doesn't totally support
> >> column aliases on UPDATE statements, for example
> 
> The SQL92 spec very clearly does not allow an alias on the target table:
> 
>  13.10  
> 
>   ::=
>   UPDATE 
> SET 
> [ WHERE  ]
> 
> While I'm willing to consider variations from the spec that add
> significant functionality, this proposed addition adds no functionality
> worth noticing.  It'd just be another way to trip yourself up when
> moving across DBMSes.
> 
> regards, tom lane

Re: [SQL] jdbc driver

[Barry Lind]

Esteban,

Try http://jdbc.postgresql.org

thanks,
--Barry

PS.  There is a pgsql-jdbc mail list which would be a more appropriate 
place for this question.



Esteban Gutierrez Abarzua wrote:

>   hi.
> 
>   who to knows about the java + postgresql (jdbc)?
> 
>   I need to know where can I get the driver for conection postgres and
> java 1.2? 
> 
>   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
> 
> 



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] [JDBC] column doesn't get calculated - update # 2

[Barry Lind]

Floyd,

I would recommend turning sql statement tracing on in the database to 
see the exact sql text that the driver is sending to the database (in 
case it is somehow munging it).  Then take that exact same text (as 
found in the server log files) and run it in psql to see how it works there.

--Barry

[EMAIL PROTECTED] wrote:
i was wrong. it doesn't work as a prepared statement nor as a dynamic string
using jdbc.
it works fine if i paste it into psql.

is it possible that a problem with a calculated column and a subselect in
conjunction is a jdbc bug?
Regards,

Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX:   702.995.6462
EMAIL: [EMAIL PROTECTED]
ICQ #: 161371538
PGP Fone at private.fwshackelford.com on request
Shackelford Motto: ACTA NON VERBA - Actions, not words

Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 16, 2003 10:05 AM
To: Pgsql-Sql
Cc: Josh Wardle; Gregory S. Dodson
Subject: RE: column doesn't get calculated - updated


when i copy/paste the select stmt into psql, it works. or if i use it
"dynamically". it doesn't work properly when i use it in a prepared
statement -- which is what i am doing.
Regards,

Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX:   702.995.6462
EMAIL: [EMAIL PROTECTED]
ICQ #: 161371538
PGP Fone at private.fwshackelford.com on request
Shackelford Motto: ACTA NON VERBA - Actions, not words

Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 15, 2003 11:14 AM
To: Pgsql-Sql
Subject: column doesn't get calculated


this must be a problem with my sql, but this one has me stumped. the column:
Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" in the long sql statement
below comes out as the literal: Debit."cumm_dbt_blnce" -
Credit."cumm_crd_blnce" rather than as the calculated value. WHY!?!?!
this works with simple sql in psql:

select a."field1" - b."field2" from (select field1 as "field1" from
someTable) as a, (select field2 as "field2" from someOtherTable) as b;
but with my more complex sql, it doesn't. it comes out as a literal string.
it's almost like postgresql forgot to process this column.
select
  cred_vend_acct_table.num as "num" ,
  cred_vend_acct_table.name as "name" ,
  abs_vend_acct_type_table.name as "name" ,
  Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" as "undefined" ,
  Debit."cumm_dbt_blnce" as "cumm_dbt_blnce" ,
  Credit."cumm_crd_blnce" as "cumm_crd_blnce" ,
  cred_vend_acct_table.objid as "__OBJID__" ,
  cred_vend_acct_table.clsref as "__CLSREF__"
from
  only cred_vend_acct_table ,
  abs_vend_acct_type_table ,
  (  select
   daily_acct_blnce_table.cumm_dbt_blnce as "cumm_dbt_blnce"
 from
   only daily_acct_blnce_table
 where
  (  (  ( daily_acct_blnce_table.company_objid *= 2147483647 )  )  and
 (  ( daily_acct_blnce_table.status = 'e' )  )  and
 ( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid )  and
 ( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref )  and
 daily_acct_blnce_table.dte =
   (  select
max(daily_acct_blnce_table.dte) as "dte"
  from
only daily_acct_blnce_table
  where
(  (  ( daily_acct_blnce_table.company_objid *=
147483647 )  )  and
   (  ( daily_acct_blnce_table.status = 'e' )  )  and
   ( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid )  and
   ( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref )  )  )  )  )
 as Debit ,
 (  select
  daily_acct_blnce_table.cumm_crd_blnce as "cumm_crd_blnce"
from
  only daily_acct_blnce_table
where
  (  (  ( daily_acct_blnce_table.company_objid *= 2147483647 )  )
and
 (  ( daily_acct_blnce_table.status = 'e' )  )  and
 ( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid )  and
 ( daily_acct_blnce_table.abs_acct_objref[1] =
cred_vend_acct_table.clsref )  and
 daily_acct_blnce_table.dte =
   (  select
max(daily_acct_blnce_table.dte) as "dte"
  from
only daily_acct_blnce_table
  where
(  (  ( daily_acct_blnce_table.company_objid *=
2147483647 )  )  and
   (  ( daily_acct_blnce_table.status = 'e' )  )  and
   ( daily_acct_blnce_table.abs_acct_objref[2] =
cred_vend_acct_table.objid )  and
   ( daily_acct_bl

Re: [SQL] [JDBC] contrib/ltree

[Barry Lind]

I would recommend using getString()/setString().  You will need to 
marshall the data into the correct format, but you will at least be able 
to get and set the values.

--Barry

[EMAIL PROTECTED] wrote:
how do i get jdbc to recognize the ltree type that comes with the
contrib/ltree extension?
This:

  Object object = resultSet.getObject(columnNumber);

generates the following exception:

  Exception caused by: No class found for ltree
at
org.postgresql.jdbc1.AbstractJdbc1Connection.getObject(AbstractJdbc1Connecti
on.java:693)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.getObject(AbstractJdbc2Connecti
on.java:117)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet
.java:147)
  ...
Regards,

Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX:   702.995.6462
EMAIL: [EMAIL PROTECTED]
ICQ #: 161371538
PGP Key ID: 0x2E84F2F2
PGP Fone available on request at private.fwshackelford.com
Shackelford Motto: ACTA NON VERBA - Actions, not words

Alabama State Motto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights
The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf

"We have allowed our constitutional republic to deteriorate into a virtually
unchecked direct democracy. Today's political process is nothing more than a
street fight between various groups seeking to vote themselves other
people's money. Individual voters tend to support the candidate that
promises them the most federal loot in whatever form, rather than the
candidate who will uphold the rule of law." --Rep. Ron Paul
---(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/docs/faqs/FAQ.html