Re: [SQL] "Flattening" query result into columns

[Tambet Matiisen]

The problem in linked article could be solved with custom aggregate in 
PostgreSQL:

create or replace function concat(text, text) returns text
   immutable
   language sql
as '
select case when $1 =  then $2 else $1 || '' '' || $2 end
';

drop aggregate concat(text) cascade;
create aggregate concat 
(
   basetype = text,
   stype = text,
   sfunc = concat,
   initcond = ''
);

The query:

select Col_A, concat(Col_B)
from table
group by Col_A

If you want only distinct values concatenated:

select Col_A, concat(distinct Col_B)
from table
group by Col_A

  Tambet

> -Original Message-
> From: Leif B. Kristensen [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, March 22, 2005 12:37 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: "Flattening" query result into columns
> 
> 
> On Monday 21 March 2005 22:57, Thomas Borg Salling wrote:
> > I am looking for a way to "flatten" a query result, so that 
> rows are 
> > "transposed" into columns, just as asked here for oracle:
> >
> > Is there any way to do this with pgsql  ?
> 
> Just to help out the guys, here's a working link:
> 
> 

What you're asking for is called a pivot table, and at least in Oracle 
they use a function called decode(). I need exactly the same thing, but 
I too am unable to find the optimal way to do it in PostgreSQL.
-- 
Leif Biberg Kristensen
http://solumslekt.org/

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Self-referencing table question

[Sean Davis]

On Mar 22, 2005, at 7:07 PM, Sean Davis wrote:
- Original Message - From: "Richard Huxton" 
To: "Sean Davis" <[EMAIL PROTECTED]>
Cc: "PostgreSQL SQL" 
Sent: Tuesday, March 22, 2005 3:59 PM
Subject: Re: [SQL] Self-referencing table question

Sean Davis wrote:
I answer my own question, if only for my own records.  The following 
query is about 5-6 times faster than the original.  Of course, if  
anyone else has other ideas, I'd be happy to hear them.

Sean
explain analyze select from_id,to_id,val from exprsdb.correlation 
where from_id in (select to_id from exprsdb.correlation where 
from_id=2424 order by val desc limit 100) and to_id in (select to_id 
from exprsdb.correlation where from_id=2424 order by val desc limit 
100) and val>0.6 and to_idMight not be any faster, but you can do this as a self-join with 
subquery:

SELECT c1.from_id, c1.to_id, c1.val
FROM
  correlation c1,
  (
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
  ) AS c2
  (
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
  ) AS c3
WHERE
  c1.from_id = c2.to_id
  AND c1.to_id = c3.to_id
  AND c1.val > 0.5
  AND c1.to_id < from_id
;
I think PG should be smart enough nowadays to figure out these two 
queries are basically the same.
Oops, I DID do a different query in my previous email than what you 
suggest in the your email.  Testing both against each other, the two 
queries--using subselects in 'in' and doing a self-join via 
subquery--have basically the same performance.

Thanks again for the help.
Sean
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

[SQL] How do I do this?

[Wei Weng]

I have a table with column filepath with contents that look like the following:
filepath
==
/var/log/foo
/var/log/bar
/var/cache/foo
/var/cache/bar
/var/foo
/var/bar
Is there anyway to retrieve the directory information only regarding those 
filepaths?

So that I can get
/var/log
/var/cache
/var
as the result of a query?
Thanks!
(I am using PostgreSQL 7.4)

Wei
---(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] best way to swap two records (computer details)

[Gary Stainburn]

On Friday 18 March 2005 7:54 pm, you wrote:
> > My question is what's the best way to swap settings between the two
> > computer records and swap any software installed?  Ideally I'd like
> > it in the form of a function where I can pass the two p_id's and
> > return a boolean reflecting success (true) or fail (false).
>
>   I'd say something like that (generic table names) :
>
> If you're confident :
> UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE 'A' END)
> WHERE owner IN ('A','B')
>
> If you're paranoid :
> UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE IF
> owner='B' THEN 'A' ELSE owner END) WHERE owner IN ('A','B')

Hello again.

I've tried the first one but get a syntax error. Anyone see why?

hardware=# update pieces set p_owner = (case if p_owner = 305 then 724 
else 305 end) where p_owner in (305, 724);
ERROR:  parser: parse error at or near "p_owner"
hardware=#



-- 
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 8: explain analyze is your friend

Re: [SQL] best way to swap two records (computer details)

[Gary Stainburn]

On Friday 18 March 2005 4:32 pm, you wrote:
> How about a user defined function ???
>
> CREATE OR REPLACE FUNCTION harwareupdate(integer, integer) RETURNS
> BOOLEAN AS '
>
> update pieces set p_name = \'LSALES1\', p_location = \'Mike
> Haley\', p_site = \'L\' where p_id = $1;
>
> update pieces set p_name = \'SPARE\', p_location = \'spare\',
> p_site = \'L\'
> where p_id = 2;
>

This isn't quite what I want.  I want to SWAP the p_name and p_location 
between the two records, not set them to static values.

Any help would be appreciated.
-- 
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 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] best way to swap two records (computer details)

[Michael Long]

> On Friday 18 March 2005 4:32 pm, you wrote:
>> How about a user defined function ???
>>
>> CREATE OR REPLACE FUNCTION harwareupdate(integer, integer) RETURNS
>> BOOLEAN AS '
>>
>> update pieces set p_name = \'LSALES1\', p_location = \'Mike
>> Haley\', p_site = \'L\' where p_id = $1;
>>
>> update pieces set p_name = \'SPARE\', p_location = \'spare\',
>> p_site = \'L\'
>> where p_id = 2;
>>
>

Why not declare variables and select the current values into them and then 
update
the records with the appropriate values.


---(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: [despammed] [SQL] How do I do this?

[Andreas Kretschmer]

am  23.03.2005, um 12:37:54 -0500 mailte Wei Weng folgendes:
> I have a table with column filepath with contents that look like the 
> following:
> 
> filepath
> ==
> /var/log/foo
> /var/log/bar
> /var/cache/foo
> /var/cache/bar
> /var/foo
> /var/bar
> 
> Is there anyway to retrieve the directory information only regarding those 
> filepaths?
> 
> So that I can get
> 
> /var/log
> /var/cache
> /var
> 

test_db=# select * from filepath ;
  path

 /var/log/foo
 /var/log/bar
 /var/cache/foo
 /var/cache/bar
 /var/foo
(5 Zeilen)

test_db=# select substring (path, '/.*/') from filepath ;
  substring
-
 /var/log/
 /var/log/
 /var/cache/
 /var/cache/
 /var/
(5 Zeilen)


test_db=# select trim(trailing '/'from substring (path, '/.*/')) from filepath ;
   rtrim

 /var/log
 /var/log
 /var/cache
 /var/cache
 /var
(5 Zeilen)




test_db=# select version();
   version
--
 PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 
3.3.5 (Debian 1:3.3.5-5)
(1 Zeile)


Regards, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47212,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] view function on pg_toast

[bandeng]

to Michael,

We use pg manager from EMS and misstype. it should be created on
public but on pg_toast


On Wed, 23 Mar 2005 10:09:19 +0500, Mihail Nasedkin
<[EMAIL PROTECTED]> wrote:
> MN> March, 23 2005, 8:39:31:
> 
> b>> hello guys,
> 
> b>> I need help.  I have functions but it is saved to pg_toast, when I
> b>> dump the database, the function from pg_toast is not copied. so is
> b>> there any sql or syntax to view function data ?
> 
> MN> xxx:=\d pg-catalog.pg_proc
> xxx:=\d pg_catalog.pg_proc
> 
> MN> xxx:=select procsrc from pg-catalog.pg_proc where proname=... and
> MN> pronamespace=...;
> xxx:=select procsrc from pg_catalog.pg_proc where proname=... and
> pronamespace=...;
> 
> --
> Regards,
> Mihail Nasedkin mailto:[EMAIL PROTECTED]
> 
> 


-- 
Gutten Aben Sugeng Sonten, Jangane Kurang Santen
bandeng

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] best way to swap two records (computer details)

[Gary Stainburn]

On Wednesday 23 March 2005 5:26 pm, you wrote:
> On Friday 18 March 2005 7:54 pm, you wrote:
> > > My question is what's the best way to swap settings between the
> > > two computer records and swap any software installed?  Ideally
> > > I'd like it in the form of a function where I can pass the two
> > > p_id's and return a boolean reflecting success (true) or fail
> > > (false).
> >
> > I'd say something like that (generic table names) :
> >
> > If you're confident :
> > UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE 'A' END)
> > WHERE owner IN ('A','B')
> >
> > If you're paranoid :
> > UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE IF
> > owner='B' THEN 'A' ELSE owner END) WHERE owner IN ('A','B')
>
> Hello again.
>
> I've tried the first one but get a syntax error. Anyone see why?
>
> hardware=# update pieces set p_owner = (case if p_owner = 305 then
> 724 else 305 end) where p_owner in (305, 724);
> ERROR:  parser: parse error at or near "p_owner"
> hardware=#

Sorted it.  The 'IF' should be 'WHEN'
-- 
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 4: Don't 'kill -9' the postmaster

Re: [SQL] How do I do this?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


> I have a table with column filepath with contents that look like the 
> following:
..
> Is there anyway to retrieve the directory information only regarding those
> filepaths?

SELECT rtrim(substring(filepath from '.+/'),'/') FROM mytable;

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200503232033
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iD8DBQFCQhkMvJuQZxSWSsgRAjTaAJ9zmgSOBH/Nre/XMw+JajBni8YyDwCg1DyV
Cd5rIhi026KFoFZEFjMOYvI=
=V9ov
-END PGP SIGNATURE-



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