[SQL] Internal functions documented

[Daniel Bruce Lynes]

I've noticed a lot of the functions that are documented for libpg are also 
stored in pg_proc.  Are these functions additionally callable from pl/pgsql?  
And if so, are they documented for pl/pgsql anywhere, or do we need to make 
educated guesses as to the parameters for them under pl/pgsql?

For instance, lo_lseek, lo_create, ...

The reason I'm asking is because I would like to be able to do something 
similar to Oracle's DBMS_LOB.getlength(), DBMS_LOB.copy(), DBMS_LOB.write(), 
DBMS_LOB.read() under pl/pgsql, without having to resort to writing a stored 
procedure in C, if I can possibly avoid it (easier for porting concerns).

Thanks.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] Execution Plans and Casts

[Hans-Jürgen Schönig]

Does anybody know how the planner treats casts?
It seems as if casts are not taken into consideration when planning the 
query because the costs  seem to stay the same.


[hs@backup mag]$ psql -p 5400 test -c "EXPLAIN SELECT id FROM t_data1"
   QUERY PLAN
---
Seq Scan on t_data1  (cost=0.00..168966.00 rows=1000 width=4)
(1 row)

[hs@backup mag]$ psql -p 5400 test -c "EXPLAIN SELECT id::numeric FROM 
t_data1"
   QUERY PLAN
---
Seq Scan on t_data1  (cost=0.00..168966.00 rows=1000 width=4)
(1 row)

[hs@backup mag]$ psql -p 5400 test -c "EXPLAIN SELECT id::text FROM t_data1"
   QUERY PLAN
---
Seq Scan on t_data1  (cost=0.00..168966.00 rows=1000 width=4)
(1 row)

[hs@backup mag]$ psql -p 5400 test -c "EXPLAIN SELECT COUNT(id::text) 
FROM t_data1"
  QUERY PLAN
-
Aggregate  (cost=193966.00..193966.00 rows=1 width=4)
  ->  Seq Scan on t_data1  (cost=0.00..168966.00 rows=1000 width=4)
(2 rows)

[hs@backup mag]$ psql -p 5400 test -c "EXPLAIN SELECT COUNT(id) FROM 
t_data1"
  QUERY PLAN
-
Aggregate  (cost=193966.00..193966.00 rows=1 width=4)
  ->  Seq Scan on t_data1  (cost=0.00..168966.00 rows=1000 width=4)
(2 rows)


   Hans





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

[SQL] CREATE INDEX question

[Hans-Jürgen Schönig]

Is there a way to influence the data type of an index being created?

Some like that would be fine:

CREATE INDEX idx_data2_x ON t_data2(x::int4);


It would be nice to have a workaround for that:


[hs@backup mag]$ time psql -p 5400 test -c "EXPLAIN SELECT * FROM 
t_data1 WHERE id > (SELECT AVG(id) FROM t_data3) "
QUERY PLAN
-
Seq Scan on t_data1  (cost=0.00..218966.00 rows=333 width=22)
  Filter: ((id)::numeric > $0)
  InitPlan
->  Aggregate  (cost=1887.00..1887.00 rows=1 width=4)
  ->  Seq Scan on t_data3  (cost=0.00..1637.00 rows=10 width=4)
(5 rows)


real0m0.057s
user0m0.010s
sys 0m0.000s
[hs@backup mag]$ time psql -p 5400 test -c "EXPLAIN SELECT * FROM 
t_data1 WHERE id > (SELECT AVG(id) FROM t_data3)::int4 "
 QUERY PLAN
---
Index Scan using idx_data1_id on t_data1  (cost=0.00..83623.33 
rows=333 width=22)
  Index Cond: (id > ($0)::integer)
  InitPlan
->  Aggregate  (cost=1887.00..1887.00 rows=1 width=4)
  ->  Seq Scan on t_data3  (cost=0.00..1637.00 rows=10 width=4)
(5 rows)


Logically PostgreSQL cannot do an index scan in the first example due to 
a wrong data type.
I could use a serializable transaction to extract the AVG first but it 
would be fine if it could be done in one query. Casting a decimal value 
to integer to make use of the index is definitely not a good solution. 
Changing the data type of the column is not a practical solution as well.

Does anybody have an elegant idea?
I have heard that there are plans to fix this in the future but does 
anybody know a workaround for 7.3.1?

   Hans



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] Reference integrity question

[Evgen Potemkin]

Good time of day!

I have two tables
  news(newsid int4, newscltid int4 references clt(cltid) match full,newstext text)
and
  clt(cltid int4, somedata text).
after clt is renamed , for ex. to clt_old, newscltid start to reference to 
clt_old.cltid.
  i'm create new table clt(cltid int4, anotherdata text);

is there any way to make newscltid reference to newly created clt, beside
recreation of news ?

thank in advance,

---
.evgen




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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Stuck on SQL Query where a calculated value is used for a join.

[ScottRArndt]

Never mind ... a good night's sleep with my subconscious working on the 
problem yielded:

SELECT 
reg_type.reg_type_desc "Registration Type",
CASE result.result_defn
WHEN NULL THEN CONVERT(VARCHAR,reg_stats.result_code)
ELSE result.result_defn
END 'Result',
reg_stats.create_date "Create Date",
forms.logical_form_number,
-- FORM 1
CASE 
WHEN
(CHARINDEX('',SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.
reg_trace1) + 5, 255)) - 1) < 0 
THEN SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.reg_trace1)
+ 5,255)
ELSE
SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.reg_trace1)
+
5,CHARINDEX('',SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace
.reg_trace1) + 5, 255)) - 1)
END 'Form 1',
-- END FORM 1
CASE CHARINDEX('992',reg_trace.reg_trace1)
WHEN 0 THEN 'No Forms Trace'
ELSE
   SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.reg_trace1) +
5, 255)
END 'Forms Trace',
reg_trace.reg_trace1
from reg_stats, reg_trace, reg_type, result, forms
where
reg_stats.reg_type_code = reg_type.reg_type_code
and reg_stats.reg_stats_logical_key = reg_trace.reg_stats_logical_key
and reg_stats.result_code *= result.result_code
and reg_trace.reg_stats_logical_key in
  (select reg_trace.reg_stats_logical_key
  from reg_stats, reg_trace 
  WHERE reg_stats.reg_stats_logical_key = reg_trace.reg_stats_logical_key
and reg_stats.create_date >= "12/18/02 09:57:00"
and reg_stats.create_date <= "12/18/02 10:15:00"
and reg_trace.reg_trace1 like '%992%')
and forms.form_number = 
CASE 
WHEN
(CHARINDEX('',SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace.
reg_trace1) + 5, 255)) - 1) < 0 
THEN
convert(smallint,SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trac
e.reg_trace1) + 5,255))
ELSE
  convert(smallint,SUBSTRING(reg_trace.reg_trace1,CHARINDEX('
992',reg_trace.reg_trace1) +
5,CHARINDEX('',SUBSTRING(reg_trace.reg_trace1,CHARINDEX('992',reg_trace
.reg_trace1) + 5, 255)) - 1))
END


---(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

[SQL] Multiple counts

[Colin Fox]

Hi, all. 

I'm trying to calculate two count()s.

I have a campaigns table, a campaign_parts table and a people table.

Here is a simplified view of 3 tables I'm trying to use:

create table campaigns {id serial, company_id int, name varchar(20));
create table campaign_parts(id serial, campaign_id int, name varchar(20));
create table people (id serial, campaignpart_id int, name varchar(20));

(fk references ignored for brevity).


I'd like to create a query which shows how many campaign parts there are
for a given set of campaigns, and how many people signed up via a campaign
(which means a sum of all the signups under all the parts of that
campaign).

The naive way to do this would be something like:
select
camp.id,
camp.name,
count(cp.id) as numparts,
count(p.id) as numsignups
from
campaigns camp,
campaign_parts cp,
people p
where
camp.company_id =  and
cp.campaign_id = camp.id and
p.cpid = cp.id
group by
camp.id, camp.name;

(I know this is really naive, but I hope it shows what I'm trying to do).

However, there are some problems with this. First, if there are no
campaign parts, I'd like to show a 0, but then that campaign doesn't
show up at all. So I need to UNION it with another selection.

Also, the counting doesn't seem to be working. I'm getting a multiple of
the correct answer.

Please help!



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

[SQL] empty arrays

[floyds]

using: psql (PostgreSQL) 7.2.1

why does an empty array return an array of length 1 rather than array of
length 0? one would think that the results below would have returned { }
instead of {0}.

simple test using psql:

# create table test_table ( test_column integer[] );
CREATE
# insert into test_table (test_column) values ( '{ }' );
INSERT 43475 1
# select * from test_table;

test_column

 {0}
(1 row)


i want to be able to store a zero-length array field in the database. how do
i specify this with  sql?

Regards,

Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX:   916.404.7125
EMAIL: [EMAIL PROTECTED]
ICQ #: 161371538

Shackelford Motto: ACTA NON VERBA - Actions, not words

Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our
Rights



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

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Doubts porting from Oracle to postgresql

[Senthil]

Hai everybody,
  
Hai,    Currently, I'm at the work of 
converting database from Oracle to Postgresql.   I have some 
doubts:
 1) What is the 
equivalent of Oracle ROWNUM in Postgresql?  If there is no such equivalent, 
can U give me an idea about         
        
that? 2) How to replace 
stored procedures with many OUT parameters?  I want to call the procedure 
from Java 
code. 
Please tell me the way to return the values of OUT parameters returned 
previously by Oracle, from the plpgsql 
code.  
It's very urgent.  Please reply quickly with some code examples.
 
HOW 
to handle the values returned by the Oracle OUT parameters, in Postgresql?
    
i.e, how to return the Oracle OUT parameters'  values to the calling Java 
code, in postgresql.
    
If anybody knows, please explain with suitable examples with plpgsql code 
and also the Calling Java code.
 3) Can default values 
be assigned to postgresql function parameters?  If yes, how? 
 
 
Thanks in advance.
Senthil.

[SQL] Slow self-join on a 100 million record table

[Mark Davies]

I have a database containing 100 million records, in which each record
contains (in sequence) all of the words in a 100 million word
collection of texts.  There are two columns: TheID (offset value) and
TheWord (sequential words), e.g.:

TheID  TheWord
  -

1  I 
2  saw 
3  the 
4  man 
5  that
6  came
 . . . 
1 xxx 

To extract strings, I then use self-joins on this one table, in which
[ID], [ID-1], [ID+1] etc are used to find preceding and following
words, e.g.:

select count(*),w1.w1,w2.w1,w3.w1 from 
((select w1, ID+1 as ID from seq where w1 in ('the','that','this')) w1
inner join 
(select w1, ID as ID from seq where w1 in ('man','woman','person')) w2
on w2.ID = w1.ID) 
inner join 
(select w1, ID-1 as ID from seq where w1 in ('who','that','which')) w3
on w3.ID=w1.ID 
group by w1.w1,w2.w1,w3.w1 

This would yield results like "the man that" (words 3-5 above),"that
woman who","this man which", etc.

The problem is, the self-join solution is extremely slow.  I have a
SQL Server 7.0 database with a clustered index on TheWord (sequential
words) and a normal index on TheID.  Even with all of this, however, a
self-join query like the one just listed takes about 15 seconds on my
machine (dual CPU 1.2GHz, 4GB RAM, three 10K rpm SCSI HD w/ RAID-0).

Any suggestions?  Have I messed up in terms of the SQL statement? 
Thanks in advance for any help that you can give.

Mark Davies
Illinois State University

P.S. Yes, I know about Full-Text Indexing in SQL Server, but it's not
adequate for my purposes -- there's a lot more to the project than
what I've described here.

---(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] Slow self-join on a 100 million record table

[Andrew J. Kelly]

Mark,

I doubt very much you will ever get much faster results (without increasing
hardware) in a situation such as that.  Your queries don't look selective
enough to effectively use the indexes.  What is the query plan for each of
the individual selects and what does it look like as a whole?   How many
rows does each individual select return and how many for the final
statement?

-- 
Andrew J. Kelly
SQL Server MVP


"Mark Davies" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> I have a database containing 100 million records, in which each record
> contains (in sequence) all of the words in a 100 million word
> collection of texts.  There are two columns: TheID (offset value) and
> TheWord (sequential words), e.g.:
>
> TheID  TheWord
>   -
>
> 1  I
> 2  saw
> 3  the
> 4  man
> 5  that
> 6  came
>  . . .
> 1 xxx
>
> To extract strings, I then use self-joins on this one table, in which
> [ID], [ID-1], [ID+1] etc are used to find preceding and following
> words, e.g.:
>
> select count(*),w1.w1,w2.w1,w3.w1 from
> ((select w1, ID+1 as ID from seq where w1 in ('the','that','this')) w1
> inner join
> (select w1, ID as ID from seq where w1 in ('man','woman','person')) w2
> on w2.ID = w1.ID)
> inner join
> (select w1, ID-1 as ID from seq where w1 in ('who','that','which')) w3
> on w3.ID=w1.ID
> group by w1.w1,w2.w1,w3.w1
>
> This would yield results like "the man that" (words 3-5 above),"that
> woman who","this man which", etc.
>
> The problem is, the self-join solution is extremely slow.  I have a
> SQL Server 7.0 database with a clustered index on TheWord (sequential
> words) and a normal index on TheID.  Even with all of this, however, a
> self-join query like the one just listed takes about 15 seconds on my
> machine (dual CPU 1.2GHz, 4GB RAM, three 10K rpm SCSI HD w/ RAID-0).
>
> Any suggestions?  Have I messed up in terms of the SQL statement?
> Thanks in advance for any help that you can give.
>
> Mark Davies
> Illinois State University
>
> P.S. Yes, I know about Full-Text Indexing in SQL Server, but it's not
> adequate for my purposes -- there's a lot more to the project than
> what I've described here.



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

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] bug?

[Minghann Ho]

The following SELECT are errors?!

tpcr=# select extract (week from date '2000-01-01');
 date_part
---
52
(1 row)

tpcr=# select extract (week from date '2000-01-02');
 date_part
---
52
(1 row)

Please check ...

thanks
Hans

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] empty arrays

[Tom Lane]

<[EMAIL PROTECTED]> writes:
> # insert into test_table (test_column) values ( '{ }' );

Try it without the space:

insert into test_table (test_column) values ( '{}' );

regards, tom lane

---(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] Slow self-join on a 100 million record table

[Tom Lane]

[EMAIL PROTECTED] (Mark Davies) writes:
> The problem is, the self-join solution is extremely slow.  I have a
> SQL Server 7.0 database with a clustered index on TheWord (sequential
> words) and a normal index on TheID.

Kindly do not pester Postgres mailing lists with SQL Server questions.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] bug?

[Tom Lane]

Minghann Ho <[EMAIL PROTECTED]> writes:
> The following SELECT are errors?!

No, they're not.  PG follows the ISO definition of week numbers:
Monday is the first day of the week, and the first week of a year
is the first one containing a Thursday.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org