[SQL] SPI documantation
Where can I get more information about programming in SPI. (more than in PostgreSQL documentation). Thanks for any help Adam
Re: [SQL] Hash Join not using hashed index?
On Wed, Jun 28, 2000 at 10:56:17AM -0400, Tom Lane wrote: > Ang Chin Han <[EMAIL PROTECTED]> writes: > If it was like that then a hash index wouldn't have been applicable > anyway; hashes are only good for strict equality checks. If you want > something that can do ordering checks you need a btree index. > > (There are good reasons why btree is the default index type ;-)) There _was_ a btree index, before I added the extra hash index: pintoo=# \dcountry_pkey Index "country_pkey" Attribute | Type +-- country_id | smallint unique btree (primary key) > > Original cost est: > > Hash Join (cost=8.85..16.76 rows=75 width=18) > > -> Seq Scan on city (cost=0.00..1.75 rows=75 width=16) > > -> Hash (cost=5.53..5.53 rows=253 width=2) > >-> Seq Scan on country (cost=0.00..5.53 rows=253 width=2) > > > I guess the problem is that country-city is a one-to-many relation, > > BUT I've more countries than cities (note the # of rows above), thus > > throwing the planner off... > > Off what? This looks like a pretty reasonable plan to me, given the > fairly small table sizes. Do you have evidence that another plan > type would be quicker for this problem? No evidence, but I was hoping that having a prehashed country_id would speed things up a bit, since the seq scan on country could be redundant, requring only a seq scan on city and a index (hash) lookup on country. Or maybe this is a related question (just curious): pintoo=# explain select country_id from country order by country_id; NOTICE: QUERY PLAN: Sort (cost=15.63..15.63 rows=253 width=2) -> Seq Scan on country (cost=0.00..5.53 rows=253 width=2) pintoo=# explain select name from country order by name; NOTICE: QUERY PLAN: Sort (cost=15.63..15.63 rows=253 width=12) -> Seq Scan on country (cost=0.00..5.53 rows=253 width=12) If there is already in b-tree index on country_id, why bother re-sorting it, when it could be output'd by traversing the tree? Comparing with an unindexed column, we can see that the index is not used at all.
[SQL] SQL stored procedures and JDBC problem
I need a SQL stored procedure to return arrays. as OUT parameters in If i declare the array as a "table", then i cannot do table[i] := some_value. If i declare it as a VARRAY. then i need to initialialize all my VARRAY elements using a costructor which is painful ( a simple for loop does not do it). So how do i declare and use arrays in SQL stored procedures so as to return arrays as OUT paramters and access the same in JDBC kindly reply at [EMAIL PROTECTED] thanks nitin
[SQL] Backup of BLOBS
Greetings, I have a database that will be quite large that must be backed up nightly. I would like to use pg_dump; however, the problem is that we store binary data as well. Is there a way to backup this up without having to write a program to do it? Thanks, Brian
[SQL] plpgsql function gets wierd with Null parameters
When I call the following plpgsql function with the last two parameters
as Null, the first parameter loses it's value:
ma=> select createFund('fred', null, null);
createfund
(1 row)
However it works fine when I give values to the last two params:
ma=> select createFund('fred', 'joe', 5);
createfund
fred
(1 row)
Here's the function (I've hacked it so that it just returns the first
parameter):
create function createFund(varchar, varchar, int) returns text as '
DECLARE
fundnameALIAS FOR $1;
fundsymbol ALIAS FOR $2;
fundcusip ALIAS FOR $3;
existingvarchar(100);
fundid int;
rec RECORD;
retval text;
datecount smallint;
BEGIN
RETURN fundname;
...
END;
' language 'plpgsql';
Any ideas?
-Skeets Norquist
YOU'RE PAYING TOO MUCH FOR THE INTERNET!
Juno now offers FREE Internet Access!
Try it today - there's no risk! For your FREE software, visit:
http://dl.www.juno.com/get/tagj.
Re: [SQL] trigger or something else?
> "EK" == Emils Klotins <[EMAIL PROTECTED]> writes:
EK> Hello,
EK> I have a table that has to have several fields with different names,
EK> but equal content. Sounds stupid, but it is because I have 2
EK> different programs querying the same table for user information and
EK> each of them uses differently named fields.
EK> Eg. I have fields passwd and password.
EK> When passwd field changes, password must automatically change
EK> to be the same as passwd.
EK> I was wondering whether I need a trigger for that, or could I
EK> somehow manage to specify that in the "create table" stmt.
EK> If I need to do it via trigger, then I apparently need the plpgsql, right?
EK> Could you tell which configure option enables that? --enable-
EK> plpgsql?
EK> Thanks in advamce for any comments.
EK> Emils
I suppose you can use view for your need. For example:
create table a (
l varchar(30),
p varchar(30)
);
create view b as select l as login, p as password from a;
insert into a values ('qq', 'ww');
select * from b;
tolik=# select * from b;
login | password
---+--
qq| ww
(1 rows)
Unfortunately this way suits for select only, not for 'insert into b'
and 'update b' statement.
--
Anatoly K. Lasareff Email: [EMAIL PROTECTED]
[SQL] extracting a table description
Hi All, I am trying to get a table description via the web. I have tried using desc tablename but although the desc command appears to be a reserved word, it doesn't appear to be supported. Does anyone know how I can extract the table description (column names)? Thanks in advance David Craig [EMAIL PROTECTED]
Re: [SQL] Hash Join not using hashed index?
On Wed, Jun 28, 2000 at 03:00:04AM -0400, Tom Lane wrote:
> Hash joins don't have anything to do with hash indexes.
> A hash join is a join that makes use of a temporary hashtable
> built on-the-fly *in memory* for that join.
Oh, I see.
> The planner could choose to use an indexscan on a hash index
> as an input for the join, but it'd only be likely to do so
> if there is a restriction clause matching the index. In your
> example you have only a join WHERE clause.
Well, in my original query, there was, but the plan's the same.
Probably the clause wasn't restrictive enough (" and region < n").
Original cost est:
Hash Join (cost=8.85..16.76 rows=75 width=18)
-> Seq Scan on city (cost=0.00..1.75 rows=75 width=16)
-> Hash (cost=5.53..5.53 rows=253 width=2)
-> Seq Scan on country (cost=0.00..5.53 rows=253 width=2)
I guess the problem is that country-city is a one-to-many relation,
BUT I've more countries than cities (note the # of rows above), thus
throwing the planner off...
OTOH, what's bugging me is that Postgresql could have used
pre-generated hash index rather rebuilding it on the fly again.
> Plain btree indexes on city.country_id and country.country_id
> might work better --- at least they'd offer the option of
> a merge join without doing explicit sort.
I tried, and it did worse.
Hmmm... I think I'm better off creating a temporary table
to store the results, since the table is seldom updated
but that query is run often. Rules to update that temp. table, too,
of course.
(cost is now 1.75, if anyone cares)
Re: [SQL] trigger or something else?
> I suppose you can use view for your need. For example: > ... > Unfortunately this way suits for select only, not for 'insert into b' > and 'update b' statement. > Except that you can use rules to update/insert data into tables when an insert/update is done on the view. See the docs for details on how to do this. > > -- > Anatoly K. Lasareff Email: [EMAIL PROTECTED] Grant -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
[SQL]
Re: Antw: [SQL] plpgsql function gets wierd with Null parameters
"Gerhard Dieringer" <[EMAIL PROTECTED]> writes: > It's a known bug of plpgsql that if one arg is NULL, all other args > are also assumed to be NULL. I think (hope) this will be fixed in a > future version. 7.1. It is already fixed in current development sources. BTW it's not actually plpgsql's fault, but that of the function-call interface. The problem appears no matter what programming language you write the function in. regards, tom lane
[SQL] Need Help With Dates.
Hello. I just migrated a database from MySQL to postgreSQL and am having trouble wit postgres' dates. MySQL dealt with dates very well, but i don't see the same sort of functionality in postgres. The database is an archive of imformation, and i would like to do a cron'd select for an interval based on the date. I can get the current date. But i don't know how to have the computer properly figure out the past dates. The select format has been: SELECT blah FROM blah2 WHERE date BETWEEN (past_date) and (current_date); This select is computed monthly. And i do not want to have to change the variables every month when this needs to run. Nor do i think that i should have to result to perl processing to solve this dilemma. I have tried (i think) every possible function and operation to try to get this to work. The problem is trying to figure out whether an extra day should be added for leap years. (It obviously should, but how do i tell the computer that it should). Postgres does not seem to recognize that concept well. Since this is running monthly, if you have any ideas to form a select like: SELECT _ WHERE date BETWEEN (date - 12 months) and ... in other words, since postgres increments by day . . . is there anyway to get it to allow you to increment / decrement by month? thanks in advance. also for reading this (long winded) post. .jtp
Re: [SQL] Need Help With Dates.
> I just migrated a database from MySQL to postgreSQL and am having trouble
> wit postgres' dates.
> MySQL dealt with dates very well, but i don't see the same sort of
> functionality in postgres.
??
> The database is an archive of imformation, and i would like to do a cron'd
> select for an interval based on the date.
> I can get the current date. But i don't know how to have the computer
> properly figure out the past dates.
>
> The select format has been:
> SELECT blah FROM blah2
> WHERE date BETWEEN (past_date) and (current_date);
> This select is computed monthly.
> And i do not want to have to change the variables every month when this
> needs to run. Nor do i think that i should have to result to perl
> processing to solve this dilemma. I have tried (i think) every possible
> function and operation to try to get this to work.
It is not clear to me *exactly* what query you used to run. Were
"past_date" and "current_date" some local program variable in the MySQL
front end? How did you set them in a way which required no external
programming or variable substitution?
> The problem is trying to figure out whether an extra day should be added
> for leap years. (It obviously should, but how do i tell the computer that
> it should).
The computer already knows. How about
select * from t1 where d between
(date_trunc('month', date 'today') - interval '1 month')
and
date_trunc('month', date 'today');
There are *lots* of date/time capabilities in Postgres (if I do say so
myself ;) so I'd be suprised if you don't find what you need.
Good luck.
- Thomas
[SQL] maintain number in variable
Can I maintain id in variable such
as:
CREATE FUNCTION function1() RETURNS int4AS ' $var =
select nextval(''shipment_gen''); select $var;'LANGUAGE
'sql'
Andrey
Re: [SQL] case insensitive search
SELECT whatever FROM wherever WHERE lower(yourfield) = 'this'; You can do it with a case inseneitive regex search but they can't use indexes and can become very slow on large tables.. SELECT whatever FROM wherever WHERE yourfield ~* 'this'; lower() does leak a bit of memory from what I've heard on the list but I'm sure someone is working on it.. -Mitch - Original Message - From: Joern Muehlencord <[EMAIL PROTECTED]> To: gpsql-sql <[EMAIL PROTECTED]> Sent: Monday, June 26, 2000 2:14 PM Subject: [SQL] case insensitive search > Hello together, > > how can I handle case insensitive search in a table? > > > > -- > Linux is like wigwam - no windows, no gates, apache inside. > In diesem Sinne > Joern > > >
