[SQL] returning results from an update for joining other tables

[Patrick Scharrenberg]

Hi!

I have a table containing data and a column which holds information on
which compute-node processes the data. In a given interval I'd like to
request some data from this table and mark these returned rows by
setting the "process_node" column to the node-name, which asked for data.
There may also be rows which have the column process_node set to the
name of the node currently asking.

What I tried was something like this, which gave me a syntax error:

SELECT * FROM
( UPDATE ta
SET process_node='nodename'
WHERE a>10 AND process_node is null
RETURNING *
) AS ta
JOIN someothertable ON ...

Can I somehow select some rows and do multiple operations on exactly
this resultset?
In my case update columns, then join columns from other tables and then
return the resultset with the joined columns?

Regards
Patrick

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] returning results from an update for joining other tables

[A. Kretschmer]

am  Tue, dem 10.06.2008, um 10:50:52 +0200 mailte Patrick Scharrenberg 
folgendes:
> Hi!
> 
> I have a table containing data and a column which holds information on
> which compute-node processes the data. In a given interval I'd like to
> request some data from this table and mark these returned rows by
> setting the "process_node" column to the node-name, which asked for data.
> There may also be rows which have the column process_node set to the
> name of the node currently asking.
> 
> What I tried was something like this, which gave me a syntax error:
> 
> SELECT * FROM
> ( UPDATE ta
>   SET process_node='nodename'
>   WHERE a>10 AND process_node is null
>   RETURNING *
> ) AS ta
> JOIN someothertable ON ...

It's a know limitation, see <[EMAIL PROTECTED]>


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] returning results from an update for joining other tables

[Patrick Scharrenberg]

Hi!
>> What I tried was something like this, which gave me a syntax error:
>>
>> SELECT * FROM
>> ( UPDATE ta
>>  SET process_node='nodename'
>>  WHERE a>10 AND process_node is null
>>  RETURNING *
>> ) AS ta
>> JOIN someothertable ON ...
>
> It's a know limitation, see <[EMAIL PROTECTED]>

Oh, I see.

Are there ways to work around this limitation?

Patrick

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Nextval & Currval

[Shavonne Marietta Wijesinghe]

Hello

I'm using Nextval and Currval in my ASP programme. But everytime i run the code 
only the nextval record is inserted and the currval record is lost. I wrote the 
INSERT INTO in the log and when i tried it via pgadmin both the records were 
inserted correctly, so there is no syntax error. 
I thought the problem would be the connection to the DB. I connect to the DB in 
the same ASP page where I do the INSERT INTO.

What am i doing wrong?? Any ideas anyone??

Thank you.

Shavonne Wijesinghe

Re: [SQL] Nextval & Currval

[Shavonne Marietta Wijesinghe]

This is the test i did for the connection.

' Connecting to the database
WriteToFile logfilepath, date & " " & time & "isobject(conn) = " & 
isobject(session("connection")) & vbcrlf , True
if not isobject(session("connection")) then
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Form_Store_PSQL"
session("connection") = conn
WriteToFile logfilepath, date & " " & time & "IF " & conn & vbcrlf , True
else
set conn = session("connection")
WriteToFile logfilepath, date & " " & time & "ELSE " & conn & vbcrlf , True
end if


Thank you.

Shavonne Wijesinghe

Re: [SQL] Nextval & Currval

[A. Kretschmer]

am  Tue, dem 10.06.2008, um 11:56:39 +0200 mailte Shavonne Marietta Wijesinghe 
folgendes:
> Hello
>  
> I'm using Nextval and Currval in my ASP programme. But everytime i run the 
> code
> only the nextval record is inserted and the currval record is lost. I wrote 
> the
> INSERT INTO in the log and when i tried it via pgadmin both the records were
> inserted correctly, so there is no syntax error.
> I thought the problem would be the connection to the DB. I connect to the DB 
> in
> the same ASP page where I do the INSERT INTO.
>  
> What am i doing wrong?? Any ideas anyone??

I don't know your code, but nextval() returns the next sequence number
from a sequence and currval() returns the last with nextval() generated
sequence-number WITHIN THIS SESSION.


In a new session you can't use currval() first, you need to call
nextval() and later you can use currval().



Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] returning results from an update for joining other tables

[Patrick Scharrenberg]

Hi!
>> >> What I tried was something like this, which gave me a syntax error:
>> >>
>> >> SELECT * FROM
>> >> ( UPDATE ta
>> >>   SET process_node='nodename'
>> >>   WHERE a>10 AND process_node is null
>> >>   RETURNING *
>> >> ) AS ta
>> >> JOIN someothertable ON ...
> >
> > It's a know limitation, see <[EMAIL PROTECTED]>

Oh, I see.

Are there ways to work around this limitation?

Patrick


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Conceptual Design Question

[Bryan Emrys]

Hello Everyone, 

In a text-heavy database, I'm trying to make an initial design decision in the 
following context. 

There is a lot of long text that I could break down into three different 
categories:
a. Laws 
i. Only 1 country per law, many laws
ii. There are multiple types of laws (statutes, regulations, 
court cases, proposed laws, etc)
iii. Each law will have only one type
iv. Each law may refer to many other laws
b. Treaties 
i. 2 countries per treaty
ii. At any one time, there will only be one treaty in force 
between any two countries
iii. There may be proposed new treaties which will supercede 
old treaties when finally ratified
c. Commentary 
i. Any commentary could refer to one or more laws or treaties
ii. Any commentary may have one or more authors
iii. Any commentary may refer to one or more countries

The conceptual question is what are the trade-offs between having one textual 
table compared with multiple text tables? Any help on pointing out practical 
considerations would be appreciated.

Thanks.

Bryan


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Apologies to the list. Please ignore accidental thread intrusion

[Bryan Emrys]

Sorry about that.

Bryan

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] One Text Table or Multiple Text Tables - Design Tradeoffs?

[Bryan Emrys]

(Trying to start a new thread instead of my accidental intrusion into another 
thread)

Hello Everyone, 

In a text-heavy database, I'm trying to make an initial design decision in the 
following context. 

There is a lot of long text that I could break down into three different 
categories:
a. Laws 
i. Only 1 country per law, many laws
ii. There are multiple types of laws (statutes, regulations, 
court cases, proposed laws, etc)
iii. Each law will have only one type
iv. Each law may refer to many other laws
b. Treaties 
i. 2 countries per treaty
ii. At any one time, there will only be one treaty in force 
between any two countries
iii. There may be proposed new treaties which will supercede 
old treaties when finally ratified
c. Commentary 
i. Any commentary could refer to one or more laws or treaties
ii. Any commentary may have one or more authors
iii. Any commentary may refer to one or more countries

The conceptual question is what are the trade-offs between having one textual 
table compared with multiple text tables? Any help on pointing out practical 
considerations would be appreciated.

Thanks.

Bryan

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Nextval & Currval

[Shavonne Marietta Wijesinghe]

I managed to make the connection work. I tried inserting records from 2 
computers. It works fine untill 2 computers insert a nextwall one after 
another. 

Here is a small example of what i see in my DB

++|+|++
N °|   Session|   Number
++|+ |++
  1|   DB last record   |   269
  2|   PC A (nextval )  |   270
  3|   PC A (nextval )  |   271
  4|   PC A (currval )   |   271
  5|   PC A (nextval )  |   272
  6|   PC A (nextval )  |   273
  7|   PC B (nextval )  |   274
  8|   PC A (currval )   |   274
  9|   PC B (nextval )  |   275
  10  |   PC A (nextval )   |   276

Isn't currval connected to its session?? I expected line N° 8  (PC A) to have 
the Number of the record it inserted a while ago (line N° 273). 


Thank you.

Shavonne Wijesinghe

Re: [SQL] help in writing query

[Pavel Stehule]

Hello

SELECT i.name, p.property_name, p.property_value
   FROM sample_info i
 JOIN
 sample_properties p
 ON i.id = p.id

maybe
Pavel

2008/6/10 maria s <[EMAIL PROTECTED]>:
> Hello friends,
> I need help in write a query.
>
> I have 2 tables, one is sample_info and sample_properties,
>
> sample_info (id integer, string name)
> --
> 1, c_01
> 2, c_02
> ...
>
> sample_properties(sample_id integer(ref. sample_info), property_name string
> ,property_value string )
> -
> 1, prop1, value1
> 1, prop2, value2
> 2, prop1, value1
> 2, prop2, value 2
> 2, prop3, value3
>
>
> I would like to get the result by joining 2 tables,  for sample id 1 from
> sample_info, the result should be
>
> 1,c_01,value1,value2
>
> for sample 2
>
> 2, c_02,value1,value2,value3
>
> with property_value column header as property_name
>
> Can anyone help me to write a query /function/view to get the above output?
>
> Thank you so much for your help.
>
> -maria
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Nextval & Currval

[Scott Marlowe]

On Tue, Jun 10, 2008 at 7:14 AM, Shavonne Marietta Wijesinghe
<[EMAIL PROTECTED]> wrote:
> I managed to make the connection work. I tried inserting records from 2
> computers. It works fine untill 2 computers insert a nextwall one after
> another.
>
> Here is a small example of what i see in my DB
>
> ++|+|++
> N °|   Session|   Number
> ++|+ |++
>   1|   DB last record   |   269
>   2|   PC A (nextval )  |   270
>   3|   PC A (nextval )  |   271
>   4|   PC A (currval )   |   271
>   5|   PC A (nextval )  |   272
>   6|   PC A (nextval )  |   273
>   7|   PC B (nextval )  |   274
>   8|   PC A (currval )   |   274
>   9|   PC B (nextval )  |   275
>   10  |   PC A (nextval )   |   276

Are you using connection pooling?  Could it be that session PC A on
the client side is switching connections between 6 and 8?  What to you
get from pg_backend_pid() in the two instances of time 6 and 8?

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] One Text Table or Multiple Text Tables - Design Tradeoffs?

[Chris Browne]

[EMAIL PROTECTED] (Bryan Emrys) writes:
> (Trying to start a new thread instead of my accidental intrusion into another 
> thread)
>
> Hello Everyone, 
>
> In a text-heavy database, I'm trying to make an initial design decision in 
> the following context. 
>
> There is a lot of long text that I could break down into three different 
> categories:
> a. Laws 
> i. Only 1 country per law, many laws
> ii. There are multiple types of laws (statutes, regulations, 
> court cases, proposed laws, etc)
> iii. Each law will have only one type
> iv. Each law may refer to many other laws
> b. Treaties 
> i. 2 countries per treaty
> ii. At any one time, there will only be one treaty in force 
> between any two countries
> iii. There may be proposed new treaties which will supercede 
> old treaties when finally ratified
> c. Commentary 
> i. Any commentary could refer to one or more laws or treaties
> ii. Any commentary may have one or more authors
> iii. Any commentary may refer to one or more countries
>
> The conceptual question is what are the trade-offs between having
> one textual table compared with multiple text tables? Any help on
> pointing out practical considerations would be appreciated.

If there is validation that could be done as to the
inter-relationships between tuples, e.g. - there are foreign keys that
would apply to "laws" that would not apply to "treaties" or
"commentary," with the whole set of vice-versas, then I would surely
think that you'd want to have separate relations for each of these
sorts of data.

If you have multiple tables, then you can readily express validation
constraints (such as the ones you describe for each of them), and can
even have the database system enforce those constraints.

If, in contrast, you pound all the data into some single "Procrustean
bed," you'll find that you can't readily/analytically deal with the
differences between them.

A main reason to try to force all the data into a single
representation would be if you think the developers can't cope with
the complexity of the differences between the representations.  But
that's actually pretty crummy reasoning; if they can't cope with the
complexity of having per-purpose tables, then it is totally
implausible to imagine they are competent to cope properly with a
unified representation.
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://linuxfinances.info/info/spreadsheets.html
"Unless you're on  the Forbes' richest 100 list,  you're not a market,
just another photon in the rainbow."
-- Monty Brandenberg <[EMAIL PROTECTED]>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] help in writing query

[maria s]

Hi Pavel,
Thank you for your reply.

I tried the query and it is returning result as ,
for a single entry in sample info in separate rows

The result of the query as

1, prop1,value1
1,prop2,value2
2,prop1,value1
2 prop2,value2
2 prop3,value3

but i want the output as single row per sample id like

1,value1,value2
2 value1,value2,value3

Is this possible? or functions will help to get the result?

please help.

Thanks,
-maria

On Tue, Jun 10, 2008 at 11:03 AM, Pavel Stehule <[EMAIL PROTECTED]>
wrote:

> Hello
>
> SELECT i.name, p.property_name, p.property_value
>   FROM sample_info i
> JOIN
> sample_properties p
> ON i.id = p.id
>
> maybe
> Pavel
>
> 2008/6/10 maria s <[EMAIL PROTECTED]>:
> > Hello friends,
> > I need help in write a query.
> >
> > I have 2 tables, one is sample_info and sample_properties,
> >
> > sample_info (id integer, string name)
> > --
> > 1, c_01
> > 2, c_02
> > ...
> >
> > sample_properties(sample_id integer(ref. sample_info), property_name
> string
> > ,property_value string )
> > -
> > 1, prop1, value1
> > 1, prop2, value2
> > 2, prop1, value1
> > 2, prop2, value 2
> > 2, prop3, value3
> >
> >
> > I would like to get the result by joining 2 tables,  for sample id 1 from
> > sample_info, the result should be
> >
> > 1,c_01,value1,value2
> >
> > for sample 2
> >
> > 2, c_02,value1,value2,value3
> >
> > with property_value column header as property_name
> >
> > Can anyone help me to write a query /function/view to get the above
> output?
> >
> > Thank you so much for your help.
> >
> > -maria
> >
>

Re: [SQL] Trouble with exception

[samantha mahindrakar]

Thanks Craig that reply really helped.

I had used second approach where i converted the INSERT into a loop
with an exception block.but as you said it does take a lot of
time. So i changed the INSERt query itself to exclude the records that
were causing the problem. It sad that the query is quite
expensivebut better than the looping.

Iam posting the query..if anybody suggest a better way of writting
it...i would be great.

INSERT INTO table1
 (SELECT 
lane_id,speed,volume,occupancy,quality,measurement_start,measurement_end,effective_date,expiration_date
FROM table2
 WHERE lane_id IN(select lane_id from table3 where inactive is  
null
)AND (volume=255 OR speed=255 OR occupancy=255 OR occupancy>=100 OR
volume>52 OR volume<0 OR speed>120 OR speed<0)
 AND date_part('hour', measurement_start) between 5 and 23
 AND date_part('day',measurement_start)='||theDate'||
 AND (lane_id,measurement_start) NOT IN (SELECT lane_id,
measurement_start from table1);

The query is trying to insert a set of recordsfrom table2 into
table1.The NOT IN i used to exclude the records that can cause
integrity constraint errors.
I was wondering if there was a better way of doing this.

Thanks
Sam



On 6/6/08, Craig Ringer <[EMAIL PROTECTED]> wrote:
> samantha mahindrakar wrote:
> > Hi
> > Iam trying to insert records into a table..when an integrity
> > constarint violation occurs the exception is caughtbut i dont
> > want the whole thing to be rolled back or stopped because of one
> > exception.
> >
>
> [snip]
>
> > Is there a way i can just skip the record that causes the violation
> > and insert the rest of the records into the table???
>
> One option is to add an additional constraint to the INSERT query that
> excludes rows that'd provoke the constraint voliation error. This might make
> the insert more expensive. If the constraint is a foreign key constraint it
> might be quite a bit more expensive as you'll be executing every foreign key
> constraint check twice*. In practice this probably won't matter much.
>
> Another alternative is to convert your INSERT to a looping PL/PgSQL function
> that uses an EXCEPTION block to trap insert errors row-by-row. Note however
> that 8.2 and earlier have awful performance when lots of rows are inserted
> in a single transaction using an exception block around each INSERT. Even
> with newer versions it might still not perform great, though changes were
> included in 8.3 to improve performance in this case.
>
> To me it seems much better to just avoid attempting to insert the invalid
> records in the first place by using an appropriate WHERE constraint on your
> INSERT query.
>
>
> * It'd be truly fantastic if the optimizer could infer that the WHERE clause
> on an INSERT/UPDATE/DELETE prevent a given constraint from being volated and
> could prevent the normal execution of the constraint check when the
> insert/update/delete ran. However, I can imagine that might be *really* hard
> to implement, and only useful for a very rare sort of query. Even then it'd
> only make a difference when the constraint check was fairly expensive.
>
> --
> Craig Ringer
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] help in writing query

[Osvaldo Rosario Kussama]

maria s escreveu:


I tried the query and it is returning result as ,
for a single entry in sample info in separate rows

The result of the query as

1, prop1,value1
1,prop2,value2
2,prop1,value1
2 prop2,value2
2 prop3,value3

but i want the output as single row per sample id like

1,value1,value2
2 value1,value2,value3

Is this possible? or functions will help to get the result?


Try tablefunc/crosstab:
http://www.postgresql.org/docs/current/interactive/tablefunc.html




please help.

Thanks,
-maria

On Tue, Jun 10, 2008 at 11:03 AM, Pavel Stehule <[EMAIL PROTECTED] 
> wrote:


Hello

SELECT i.name , p.property_name, p.property_value
  FROM sample_info i
JOIN
sample_properties p
ON i.id  = p.id 

maybe
Pavel

2008/6/10 maria s <[EMAIL PROTECTED] >:
 > Hello friends,
 > I need help in write a query.
 >
 > I have 2 tables, one is sample_info and sample_properties,
 >
 > sample_info (id integer, string name)
 > --
 > 1, c_01
 > 2, c_02
 > ...
 >
 > sample_properties(sample_id integer(ref. sample_info),
property_name string
 > ,property_value string )
 > -
 > 1, prop1, value1
 > 1, prop2, value2
 > 2, prop1, value1
 > 2, prop2, value 2
 > 2, prop3, value3
 >
 >
 > I would like to get the result by joining 2 tables,  for sample
id 1 from
 > sample_info, the result should be
 >
 > 1,c_01,value1,value2
 >
 > for sample 2
 >
 > 2, c_02,value1,value2,value3
 >
 > with property_value column header as property_name
 >
 > Can anyone help me to write a query /function/view to get the
above output?
 >


Osvaldo

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] help in writing query

[Pavel Stehule]

2008/6/10 maria s <[EMAIL PROTECTED]>:
> Hi Pavel,
> Thank you for your reply.
>
> I tried the query and it is returning result as ,
> for a single entry in sample info in separate rows
>
> The result of the query as
>
> 1, prop1,value1
> 1,prop2,value2
> 2,prop1,value1
> 2 prop2,value2
> 2 prop3,value3
>
> but i want the output as single row per sample id like
>
> 1,value1,value2
> 2 value1,value2,value3
>
> Is this possible? or functions will help to get the result?

I am not sure. Optimal query depend on your postgresql version and
expected size of result set.

I don't see property name in you result?

Pavel

>
> please help.
>
> Thanks,
> -maria
>
> On Tue, Jun 10, 2008 at 11:03 AM, Pavel Stehule <[EMAIL PROTECTED]>
> wrote:
>>
>> Hello
>>
>> SELECT i.name, p.property_name, p.property_value
>>   FROM sample_info i
>> JOIN
>> sample_properties p
>> ON i.id = p.id
>>
>> maybe
>> Pavel
>>
>> 2008/6/10 maria s <[EMAIL PROTECTED]>:
>> > Hello friends,
>> > I need help in write a query.
>> >
>> > I have 2 tables, one is sample_info and sample_properties,
>> >
>> > sample_info (id integer, string name)
>> > --
>> > 1, c_01
>> > 2, c_02
>> > ...
>> >
>> > sample_properties(sample_id integer(ref. sample_info), property_name
>> > string
>> > ,property_value string )
>> > -
>> > 1, prop1, value1
>> > 1, prop2, value2
>> > 2, prop1, value1
>> > 2, prop2, value 2
>> > 2, prop3, value3
>> >
>> >
>> > I would like to get the result by joining 2 tables,  for sample id 1
>> > from
>> > sample_info, the result should be
>> >
>> > 1,c_01,value1,value2
>> >
>> > for sample 2
>> >
>> > 2, c_02,value1,value2,value3
>> >
>> > with property_value column header as property_name
>> >
>> > Can anyone help me to write a query /function/view to get the above
>> > output?
>> >
>> > Thank you so much for your help.
>> >
>> > -maria
>> >
>
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] help in writing query

[maria s]

Hi Rosario,
Thanks for the link. I hope this will solve my problem.

Thanks,
Maria

On Tue, Jun 10, 2008 at 11:34 AM, Osvaldo Rosario Kussama <
[EMAIL PROTECTED]> wrote:

> maria s escreveu:
>
>>
>> I tried the query and it is returning result as ,
>> for a single entry in sample info in separate rows
>>
>> The result of the query as
>>
>> 1, prop1,value1
>> 1,prop2,value2
>> 2,prop1,value1
>> 2 prop2,value2
>> 2 prop3,value3
>>
>> but i want the output as single row per sample id like
>>
>> 1,value1,value2
>> 2 value1,value2,value3
>>
>> Is this possible? or functions will help to get the result?
>>
>
> Try tablefunc/crosstab:
> http://www.postgresql.org/docs/current/interactive/tablefunc.html
>
>
>
>> please help.
>>
>> Thanks,
>> -maria
>>
>> On Tue, Jun 10, 2008 at 11:03 AM, Pavel Stehule <[EMAIL PROTECTED]> [EMAIL PROTECTED]>> wrote:
>>
>>Hello
>>
>>SELECT i.name , p.property_name, p.property_value
>>  FROM sample_info i
>>JOIN
>>sample_properties p
>>ON i.id  = p.id 
>>
>>maybe
>>Pavel
>>
>>2008/6/10 maria s <[EMAIL PROTECTED] >:
>> > Hello friends,
>> > I need help in write a query.
>> >
>> > I have 2 tables, one is sample_info and sample_properties,
>> >
>> > sample_info (id integer, string name)
>> > --
>> > 1, c_01
>> > 2, c_02
>> > ...
>> >
>> > sample_properties(sample_id integer(ref. sample_info),
>>property_name string
>> > ,property_value string )
>> > -
>> > 1, prop1, value1
>> > 1, prop2, value2
>> > 2, prop1, value1
>> > 2, prop2, value 2
>> > 2, prop3, value3
>> >
>> >
>> > I would like to get the result by joining 2 tables,  for sample
>>id 1 from
>> > sample_info, the result should be
>> >
>> > 1,c_01,value1,value2
>> >
>> > for sample 2
>> >
>> > 2, c_02,value1,value2,value3
>> >
>> > with property_value column header as property_name
>> >
>> > Can anyone help me to write a query /function/view to get the
>>above output?
>> >
>>
>
> Osvaldo
>

Re: [SQL] Conceptual Design Question

[Steve Midgley]

At 10:52 AM 6/10/2008, [EMAIL PROTECTED] wrote:

Date: Tue, 10 Jun 2008 05:05:24 -0700
From: Bryan Emrys <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: Conceptual Design Question
Message-ID: <[EMAIL PROTECTED]>

Hello Everyone,

In a text-heavy database, I'm trying to make an initial design 
decision in the following context.


There is a lot of long text that I could break down into three 
different categories:

[snip]
The conceptual question is what are the trade-offs between having one 
textual table compared with multiple text tables? Any help on pointing 
out practical considerations would be appreciated.


Thanks.

Bryan


Hi Bryan,

Firstly, I might investigate the GiST index and TSearch2 in this 
regard. I'm not an expert on them, and it maybe is cart before the 
horse, but if those tools are applicable and are easier to 
implement/maintain with one design approach or the other, I might use 
their design "preferences" as my guide for picking the "right" 
relationships.


Beyond that advice, it does seem to me that a polymorphic relationship 
(where one table holds multiple entities) *could* describe laws and 
treaties, though they are kind of different in their relations. 
Commentaries seem pretty distinct from these two things.


My overall opinion would also depend on the architecture. Will you have 
a unified middleware/ORM layer that can manage the business rules for 
the polymorphic data retrieval? Or will developers be going directly 
into the database to pull items directly?


If you have a unified ORM that stores the business rules, you can be 
more aggressive about using polymorphism, b/c the complexity can be 
hidden from most developers.


All in all, I think your model is really describing three distinct data 
entities, and should be stored in three separate tables, but that's a 
very high level and uninformed opinion! I'd let TSearch2 drive your 
design if that's a relevant consideration. Of course TSearch2 is very 
flexible so it might not really care much about this. :)


In general, I find that a data model that "looks like" the real data is 
the one that I'm happiest with - the systems I've seen with too much 
UML optimization and collapsing of sets of data into single tables tend 
to be harder to maintain, etc.


Just some random opinions for you there. I'm sure others have different 
perspectives which are equally or more valid!


Best,

Steve


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] help in writing query

[Scott Marlowe]

On Tue, Jun 10, 2008 at 11:51 AM, maria s <[EMAIL PROTECTED]> wrote:
> Hi Rosario,
> Thanks for the link. I hope this will solve my problem.

It should be able to.  Note that crosstab functions expect "square"
inputs from the select they run.  I.e. you can't have empty columns,
you need to replace NULL output with something like a space or empty
string.

This is bad input for crosstab:

col1 col2 col3
1 2 3
2 3 NULL
3 NULL 6

But this will work:

col1 col2 col3
1 2 3
2 3 '' <- an empty string
3 '' 6

The crosstab functions are wonderfully useful btw, once you figure all
the little quirks like this out.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] help in writing query

[maria s]

Hi Scott,
Thanks for the information. This is very useful for me.
I will be careful when forming the column.

Thanks,
-maria

On Tue, Jun 10, 2008 at 3:16 PM, Scott Marlowe <[EMAIL PROTECTED]>
wrote:

> On Tue, Jun 10, 2008 at 11:51 AM, maria s <[EMAIL PROTECTED]> wrote:
> > Hi Rosario,
> > Thanks for the link. I hope this will solve my problem.
>
> It should be able to.  Note that crosstab functions expect "square"
> inputs from the select they run.  I.e. you can't have empty columns,
> you need to replace NULL output with something like a space or empty
> string.
>
> This is bad input for crosstab:
>
> col1 col2 col3
> 1 2 3
> 2 3 NULL
> 3 NULL 6
>
> But this will work:
>
> col1 col2 col3
> 1 2 3
> 2 3 '' <- an empty string
> 3 '' 6
>
> The crosstab functions are wonderfully useful btw, once you figure all
> the little quirks like this out.
>

Re: [SQL] Conceptual Design Question

[Medi Montaseri]

Assuming common semantics for a given field then the question of breaking it
to many parts is also a function of its size as related to I/O.

We know that memory allocation and I/O read/writes are not granular to bytes
and are rather blocks of bytes as it travels from VM (virtual memory) all
the way down to sectors on disk.

Hence a common field of say 2000 bytes will most likely cause multiple I/O
requests where application layer did not have any use for  80% of it,  80%
of the times.

Having said that, 1 Gig of RAM is about $25 at your local Cosco with a free
slice of pizzaperformance tuning paradigms are in big time flux and are
really uncle Bob's war stories

cheers

On Tue, Jun 10, 2008 at 11:35 AM, Steve Midgley <[EMAIL PROTECTED]> wrote:

> At 10:52 AM 6/10/2008, [EMAIL PROTECTED] wrote:
>
>> Date: Tue, 10 Jun 2008 05:05:24 -0700
>> From: Bryan Emrys <[EMAIL PROTECTED]>
>> To: pgsql-sql@postgresql.org
>> Subject: Conceptual Design Question
>> Message-ID: <[EMAIL PROTECTED]>
>>
>> Hello Everyone,
>>
>> In a text-heavy database, I'm trying to make an initial design decision in
>> the following context.
>>
>> There is a lot of long text that I could break down into three different
>> categories:
>>
> [snip]
>
>> The conceptual question is what are the trade-offs between having one
>> textual table compared with multiple text tables? Any help on pointing out
>> practical considerations would be appreciated.
>>
>> Thanks.
>>
>> Bryan
>>
>
> Hi Bryan,
>
> Firstly, I might investigate the GiST index and TSearch2 in this regard.
> I'm not an expert on them, and it maybe is cart before the horse, but if
> those tools are applicable and are easier to implement/maintain with one
> design approach or the other, I might use their design "preferences" as my
> guide for picking the "right" relationships.
>
> Beyond that advice, it does seem to me that a polymorphic relationship
> (where one table holds multiple entities) *could* describe laws and
> treaties, though they are kind of different in their relations. Commentaries
> seem pretty distinct from these two things.
>
> My overall opinion would also depend on the architecture. Will you have a
> unified middleware/ORM layer that can manage the business rules for the
> polymorphic data retrieval? Or will developers be going directly into the
> database to pull items directly?
>
> If you have a unified ORM that stores the business rules, you can be more
> aggressive about using polymorphism, b/c the complexity can be hidden from
> most developers.
>
> All in all, I think your model is really describing three distinct data
> entities, and should be stored in three separate tables, but that's a very
> high level and uninformed opinion! I'd let TSearch2 drive your design if
> that's a relevant consideration. Of course TSearch2 is very flexible so it
> might not really care much about this. :)
>
> In general, I find that a data model that "looks like" the real data is the
> one that I'm happiest with - the systems I've seen with too much UML
> optimization and collapsing of sets of data into single tables tend to be
> harder to maintain, etc.
>
> Just some random opinions for you there. I'm sure others have different
> perspectives which are equally or more valid!
>
> Best,
>
> Steve
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

[SQL] Update and trigger

[Medi Montaseri]

Hi,

I need to increament a counter such as myTable.Counter of type integer
everytime myTable.status a boolean column is updated. Can you help me
complete this...

create trigger counter_trigger after update on myTable.counter
execute procedure 'BEGIN statement; statement; statement END'

Q1- how do I narrow the event to "update on a column not a row"
Q2- can I just provide an inline procedure for the execute

Thanks
Medi

Re: [SQL] Update and trigger

[A. Kretschmer]

am  Tue, dem 10.06.2008, um 18:45:51 -0700 mailte Medi Montaseri folgendes:
> Hi,
> 
> I need to increament a counter such as myTable.Counter of type integer
> everytime myTable.status a boolean column is updated. Can you help me complete
> this...
> 
> create trigger counter_trigger after update on myTable.counter
> execute procedure 'BEGIN statement; statement; statement END'

much simpler, use a RULE instead a TRIGGER like my example:

Suppose, i have a table called foo, it contains now:

test=# select * from foo;
 i
---
 1
 2
(2 rows)


I create a sequence and a RULE:

test=*# create sequence foo_counter;
CREATE SEQUENCE
test=*# create or replace rule foo_update as on update to foo do also select 
nextval('foo_counter');
CREATE RULE


And now i do a update on foo:


test=*# update foo set i=2;
 nextval
-
   1
(1 row)

test=*# update foo set i=3;
 nextval
-
   2
(1 row)


test=*# select currval('foo_counter');
 currval
-
   2
(1 row)



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Update and trigger

[Craig Ringer]

Medi Montaseri wrote:

Hi,

I need to increament a counter such as myTable.Counter of type integer 
everytime myTable.status a boolean column is updated. Can you help me 
complete this...


create trigger counter_trigger after update on myTable.counter
execute procedure 'BEGIN statement; statement; statement END'

Q1- how do I narrow the event to "update on a column not a row"


Use a row-level trigger and test to see if the column of interest has 
been altered. Eg:


IF new.fieldname IS DISTINCT FROM old.fieldname THEN
   -- Do the work
END IF;


Q2- can I just provide an inline procedure for the execute


No, at present you must create a function that returns TRIGGER and then 
use that as the target to execute. At least as far as I know.


--
Craig Ringer

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql