[SQL] How can I selet rows which have 2 columns values cross equal?

[Fay Du]

Hi All:
I have a table like this:

   Table test
Id   |  a | b
-++---
1| 100| 101
2| 101| 100
3| 100| 3
4| 20 | 30
5| 11 | 13
6| 3  | 33
7| 30 | 20

I want to get row 1, 2,4 and 7 selected. Because their values of a and b
are cross equal. i.e., for each pair of rows, 
a.Row1 = b.Row2 and b.Ro1 = a.Row2
a.Row4 = b.Row7 and b.Ro4 = a.Row7

How can I construct a sql statement to select them?
Thanks in advance.
Fay


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] pgsql2shp - Could not create dbf file

[Fay Du]

Hi everyone:

 I got an error
when I use pgsal2shp.

 My server is
Linux

 The command I
used is: pgsql2shp -f newroads gisdb
testarea 

 Where, newroads is my out put file name, gisdb
is database name, and testarea is table name.

 After hit enter button,
I got the message:  Initializing... Could
not create dbf file

 

 What was wrong?
Any help will be appreciated.

 

Fay

[SQL] Update question

[Fay Du]

Hi all:

 

I have a table like this:

 

Table cost

Id  edge_id    cost

1   30 101.4

2   30  null

3   40  500.2

4   40   null

5    45  300.7

6    45   null

 

I want to set cost value with same edge_id
by same value. The existing values are calculated by calling a function
(calculate edge length). I can calculate the value by same function, but the
function was slow. I wander if I can use update statement to set the second
value with same edge_id. Thanks in advance for your
help.

 

Fay

 

Re: [SQL] Update question

[Fay Du]

Markus and Gábriel:
Thank you very much for the help.
Yes, the table is redundant. What I am trying to do is using pgdijstra
module from postGIS which is on the top of postgreSQL. And the module
asks for entire network is directional or non-directional. But my
network part is directional and other part is non-directional. I made it
is directional by duplicate all non-directional edges into 2 directional
edges and it is where redundant come from. And the module only accept
data from one table, so I have to keep all data in one table.

Thanks again.
Fay

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gábriel Ákos
Sent: Tuesday, March 28, 2006 12:34 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Update question

Markus Schaber wrote:
> Hi, Fay,
> 
> Fay Du wrote:
>> Table cost
>>
>> Id  edge_idcost
>> 1   30 101.4
>> 2   30  null
>> 3   40  500.2
>> 4   40   null
>> 545  300.7
>> 645   null
> 
>> I want to set cost value with same edge_id by same value. The
existing
>> values are calculated by calling a function (calculate edge length).
I
>> can calculate the value by same function, but the function was slow.
I
>> wander if I can use update statement to set the second value with
same
>> edge_id. Thanks in advance for your help.
> 
> It should go like:
> 
> UPDATE cost SET edge_id = (SELECT cost FROM cost innr WHERE
innr.edge_id
> = edge_id AND innr.cost is not null) WHERE cost is null;
> 
> HTH,
> Marku
> 

This is ok, but this is redundant, i guess. you should put edge_id and
cost into another table and use join to retrieve cost for each id.
put a key on edge_id, most likely a primary (unique) key.


-- 
Üdvözlettel,
Gábriel Ákos
-=E-Mail :[EMAIL PROTECTED]|Web:  http://www.i-logic.hu=-
-=Tel/fax:+3612367353|Mobil:+36209278894=-


---(end of broadcast)---
TIP 1: 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: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] How can I read display message from a C function

[Fay Du]

Hi All:

    I would like to
put some message in my C function ( myTestFunction).
Currently, I want to see time for each function call inside myTestFunction.
The output to the screen commands are in myTestFunction.
myTestFunction is called
from postgresql. How can I see the messages? Thanks
in advance.

 

Fay