[SQL] puzzled by SELECT INTO

2007-10-30 Thread Gerardo Herzig 

Hi all. Im having some trouble here that cannot understand.
Consider this function:
CREATE OR REPLACE FUNCTION read_words(bigint, varchar)
returns varchar
as
$$
declare
   returnValue varchar ;
BEGIN
   select * into returnValue  from array_to_string(array(select word 
from words where page_id=$1 and word_position in ($2)), ' ');

return returnValue;
END;
$$ language plpgsql;

So far, so good. But...
select * from read_words(99466::bigint, '2994,2995,2996');
read_words


(1 row)

But...if i do a
select  * from array_to_string(array(select word from words where 
page_id=99466 and word_position in (2994,2995,2996)), ' ')

  array_to_string
-
man page inside

Means that the query itself seems OK, but something in the SELECT INTO 
thing is not working to me.

Mmmm...i guess is not that. I just make the sql version of that function
CREATE OR REPLACE FUNCTION read_words(bigint, varchar)
returns varchar
as
$$
select * from array_to_string(array(select word from words where 
page_id=$1 and word_position in ($2)), ' ');

$$
language sql;

with the same (NULL) resultsLooks like im having some mistake near 
'and word_position in ($2)...'

Wreird enough to me, need some advice plz!

Thanks!
Gerardo

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

[SQL] Perfomance benefit using Min() against order by & limit 1?

2007-10-30 Thread Fernando Hevia 
Hi guys. Is there any difference between these two queries regarding
performance?
Table stopvoip has several million records. 
I suspect using the aggregate function would be best, but benchmarking
doesn't seem to confirm it. Both queries take around 150 - 175 ms once data
has been cached. 

Any hindsights?


SELECT min(h323setuptime::date)
FROM stopvoip 
WHERE callingstationid = '2941605118'   
AND h323setuptime >= '2007.07.01'   
AND h323disconnectcause = '10'   
AND acctsessiontime > 0   
AND NOT calledstationid ~ '^99[89]#'   

"Aggregate  (cost=11151.25..11151.27 rows=1 width=8)"
"  ->  Bitmap Heap Scan on stopvoip  (cost=29.29..11149.98 rows=507
width=8)"
"Recheck Cond: ((callingstationid)::text = '2941605118'::text)"
"Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp with
time zone) AND ((h323disconnectcause)::text = '10'::text) AND
(acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))"
"->  Bitmap Index Scan on idx_stopvoip_callingid2  (cost=0.00..29.29
rows=2939 width=0)"
"  Index Cond: ((callingstationid)::text = '2941605118'::text)"



SELECT h323setuptime::date 
FROM stopvoip 
WHERE callingstationid = '2941605118'   
AND h323setuptime >= '2007.07.01'   
AND h323disconnectcause = '10'   
AND acctsessiontime > 0   
AND NOT calledstationid ~ '^99[89]#'   
ORDER BY 1 
LIMIT 1

"Limit  (cost=11174.03..11174.03 rows=1 width=8)"
"  ->  Sort  (cost=11174.03..11175.30 rows=507 width=8)"
"Sort Key: (h323setuptime)::date"
"->  Bitmap Heap Scan on stopvoip  (cost=29.29..11151.25 rows=507
width=8)"
"  Recheck Cond: ((callingstationid)::text =
'2941605118'::text)"
"  Filter: ((h323setuptime >= '2007-07-01
00:00:00-03'::timestamp with time zone) AND ((h323disconnectcause)::text =
'10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~
'^99[89]#'::text))"
"  ->  Bitmap Index Scan on idx_stopvoip_callingid2
(cost=0.00..29.29 rows=2939 width=0)"
"Index Cond: ((callingstationid)::text =
'2941605118'::text)"


Thanks,
Fernando.

Re: [SQL] puzzled by SELECT INTO

2007-10-30 Thread Rodrigo De León 
On 10/30/07, Gerardo Herzig <[EMAIL PROTECTED]> wrote:
> Wreird enough to me, need some advice plz!

CREATE OR REPLACE FUNCTION READ_WORDS(BIGINT, INT[])
RETURNS VARCHAR
AS
$$
  DECLARE
RETURNVALUE VARCHAR;
  BEGIN
SELECT ARRAY_TO_STRING(ARRAY(
  SELECT WORD
  FROM WORDS WHERE PAGE_ID=$1
  AND WORD_POSITION = ANY ($2)
), ' ') INTO RETURNVALUE;
RETURN RETURNVALUE;
  END;
$$ LANGUAGE PLPGSQL;

SELECT READ_WORDS(99466, '{2994,2995,2996}');

See:
http://www.postgresql.org/docs/8.2/static/arrays.html

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

Re: [SQL] Perfomance benefit using Min() against order by & limit 1?

2007-10-30 Thread Fernando Hevia 
Oops. Previous message went in HMTL. Sorry for that.
Text-only version follows.

---
Hi guys. Is there any difference between these two queries regarding
performance?
Table stopvoip has several million records. 
I suspect using the aggregate function would be best, but benchmarking
doesn’t seem to confirm it. Both queries take around 150 - 175 ms once data
has been cached. 
Any hindsights?

SELECT min(h323setuptime::date)
FROM stopvoip 
WHERE callingstationid = '2941605118'   
AND h323setuptime >= '2007.07.01'   
AND h323disconnectcause = '10'   
AND acctsessiontime > 0   
AND NOT calledstationid ~ '^99[89]#'   

Aggregate  (cost=11151.25..11151.27 rows=1 width=8)"
  ->  Bitmap Heap Scan on stopvoip  (cost=29.29..11149.98 rows=507 width=8)
    Recheck Cond: ((callingstationid)::text = '2941605118'::text)
    Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp with
time zone) AND ((h323disconnectcause)::text = '10'::text) AND
(acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))
    ->  Bitmap Index Scan on idx_stopvoip_callingid2  (cost=0.00..29.29
rows=2939 width=0)
  Index Cond: ((callingstationid)::text = '2941605118'::text)


SELECT h323setuptime::date 
FROM stopvoip 
WHERE callingstationid = '2941605118'   
AND h323setuptime >= '2007.07.01'   
AND h323disconnectcause = '10'   
AND acctsessiontime > 0   
AND NOT calledstationid ~ '^99[89]#'   
ORDER BY 1 
LIMIT 1

Limit  (cost=11174.03..11174.03 rows=1 width=8)
  ->  Sort  (cost=11174.03..11175.30 rows=507 width=8)
    Sort Key: (h323setuptime)::date
    ->  Bitmap Heap Scan on stopvoip  (cost=29.29..11151.25 rows=507
width=8)
  Recheck Cond: ((callingstationid)::text = '2941605118'::text)
  Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp
with time zone) AND ((h323disconnectcause)::text = '10'::text) AND
(acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))
  ->  Bitmap Index Scan on idx_stopvoip_callingid2 
(cost=0.00..29.29 rows=2939 width=0)
    Index Cond: ((callingstationid)::text =
'2941605118'::text)


Thanks,
Fernando.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match