Re: [SQL] Postgres - Delphi Application
Dnia 2003-06-27 16:07, Użytkownik Együd Csaba napisał: Tomasz, it seems to be interesting for me as well, but actually I can't download a file from the given url. I just click on the binary zip link, but nothing happen. How should I click? :) Thanks, There is nothing special - it's a default sourceforge download. First you choose version and target of file, after this you have to choose mirror you want to use and then you download. Tomasz ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] select column from table
Hi everybody! I just want to know is there a way in SQL command that can handle column selection?. The case is : If I've a table with 10 columns and I want to select 8 of them without rewrite the columns/fields name in the SQL query? eg : Current SQL query is :- Select a,b,c,d,e,f,g,h from abctable; - greps 8 columns Can SQL do something like Select * from abctable without i,j ? - greps all 8 columns? Thanks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Seqno. is not btree?
Title: Seqno. is not btree?
Hi,
I use Postgresql-7.2.4
I’ve created following table:
create table vchar (
seqno SERIAL,
col_var character varying (8192)
);
After as many as 2412081 records are inserted, I shutdown my server. My every record was inserted programatically by :
insert into vchar (col_var) values (‘VVV’)……(VVV’ char sequence was 8192 times long. i.e. in one row 8192 V’s are present.)
Next day,I tried the following:
test=# select count(*) from vchar;
count
---
2568
(1 row)
test=# insert into vchar (col_var) values ('VV’);
ERROR: Index vchar_seqno_key is not a btree
What does this error mean? My server log shows 2412081 records inserted but actual count(*) shows only 2568 records.
Pls. help.
Thx.,
Anagha
[SQL] Bitwise operation
Hi, I'm learning to use Postgresql's bitwise operator's as I'm interested in building super fast search's based on user selections in web forms. So far so good but I have just lost it a little so I thought I'd post. Please growl at me if I'm asking on the wrong list :-) I understand this: SELECT 111 & 11 = 11 but not this SELECT & 111 = 71 I was expecting the second example to be SELECT & 111 = 111 Hmm ... Maybe I should search on the web for some more info on how to understand this logic. Math was never my strongest I was always better at creative writing. Any advice much appreciated. Thanks Best Regards Rudi. ---(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] need some help with a delete statement
On Fri, 27 Jun 2003, Matthew Hixson wrote:
> Hi, I have a bunch of records that I need to delete from our database.
> These records represent shopping carts for visitors to our website.
> The shopping carts I'd like to delete are the ones without anything in
> them. Here is the schema:
>
> create sequence carts_sequence;
> create table carts(
> cart_id integer default nextval('carts_sequence') primary key,
> cart_cookie varchar(24));
>
> create sequence cart_contents_sequence;
> create table cart_contents(
> cart_contents_id integer default nextval('cart_contents_sequence')
> primary key,
> cart_id integer not null,
> content_id integer not null,
> expire_time timestamp);
>
> I'm trying to use this query to delete the carts that are not
> referenced from the cart_contents table.
>
> delete from carts where cart_id in (select cart_id from carts except
> (select distinct cart_id from cart_contents));
>
> My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
> running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries in
> v_carts and only 3746 entries in v_cart_contents. Clearly there are a
> very large number of empty carts. Running the delete statement above
> runs for over 15 minutes on this machine. I just cancelled it because
> I want to find a faster query to use in case I ever need to do this
> again. While the query is running the disk does not thrash at all. It
> is definitely CPU bound.
>Limiting the statement to 1 item takes about 12 seconds to run:
>
> delete from carts where cart_id in (select cart_id from carts except
> (select distinct cart_id from cart_contents) limit 1);
> Time: 12062.16 ms
While in() is notoriously slow, this sounds more like a problem where your
query is having to seq scan due to mismatching or missing indexes.
So, what kind of index do you have on cart_id, and what happens if you:
select cart_id from carts except
(select distinct cart_id from cart_contents) limit 1;
then feed the cart_id into
explain analyze delete from carts where cart_id=id_from_above;
from psql?
Is cart_id a fk to another table (or is another table using it as a fk?)
---(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] Bitwise operation
On Mon, 30 Jun 2003 22:28:15 +1000 "Rudi Starcevic" <[EMAIL PROTECTED]> wrote: > SELECT & 111 = 71 > I was expecting the second example to be > SELECT & 111 = 111 hmm ..: let's see: dec() is bin(10001010111) dec(111) is bin(110) so, when we'll "AND" them: : 10001010111 111 : 110 --- 1000111 : dec: 71 quite reasonable, isn't it? depesz ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Bitwise operation
On Mon, Jun 30, 2003 at 22:28:15 +1000, Rudi Starcevic <[EMAIL PROTECTED]> wrote: > > Hi, > > I'm learning to use Postgresql's bitwise operator's as I'm interested > in building super fast search's based on user selections in web forms. > > So far so good but I have just lost it a little so I thought I'd post. > Please growl at me if I'm asking on the wrong list :-) > > I understand this: > > SELECT 111 & 11 = 11 > > but not this > > SELECT & 111 = 71 > > I was expecting the second example to be > > SELECT & 111 = 111 In the above examples the numbers are decimal values. Maybe you wanted to do something like the following: area=> select b'' & b'0111'; ?column? -- 0111 (1 row) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] select column from table
On Mon, 30 Jun 2003, Abdul Wahab Dahalan wrote: > Hi everybody! > > I just want to know is there a way in SQL command that can handle column > selection?. > The case is : > If I've a table with 10 columns and I want to select 8 of them without > rewrite the columns/fields name in the SQL query? > eg : Current SQL query is :- Select a,b,c,d,e,f,g,h from abctable; - > greps 8 columns >Can SQL do something like Select * from abctable without i,j ? - > greps all 8 columns? Not really like that, but you can create a view: create view test as selct a,b,c,d,e,f,g,h from table then select * from test; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Seqno. is not btree?
"Anagha Joshi" <[EMAIL PROTECTED]> writes:
> After as many as 2412081 records are inserted, I shutdown my server.
> Next day,I tried the following:
> test=3D# select count(*) from vchar;
> count
> ---
> 2568
> (1 row)
> test=3D# insert into vchar (col_var) values ('VV');
> ERROR: Index vchar_seqno_key is not a btree
It sounds to me like you've got serious disk problems --- losing a
couple hundred thousand rows is not normal :-(. The index complaint
also suggests on-disk corruption --- it means that the btree version
information that's stored in the first page of a btree index wasn't
right.
How exactly did you "shut down the server"? Have you noticed any other
evidence of disk problems, outside Postgres?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] ERROR: ExecEvalExpr: unknown expression type 108
Hi,
datetest=# select version();
version
--
PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
(1 запись)
datetest=# \d daten
Таблица "public.daten"
Колонка | Тип|
Модификаторы
+-+---
id | integer | not null default
nextval('public.daten_id_seq'::text)
menge | integer |
von| timestamp without time zone |
bis| timestamp without time zone |
Индексы: daten_pkey ключевое поле btree (id)
datetest=# select * from daten;
id | menge | von | bis
+---+-+-
1 | 2 | 2000-01-01 10:00:00 | 2000-01-01 12:00:00
2 | 3 | 2000-01-01 11:00:00 | 2000-01-01 14:00:00
3 | 1 | 2000-01-01 14:00:00 | 2000-01-01 15:00:00
4 | 9 | 2000-01-01 12:00:00 | 2000-01-01 16:00:00
5 | 4 | 2000-01-01 10:00:00 | 2000-01-01 11:00:00
(записей: 5)
datetest=# select * from (select (select count(1) from (select von from
daten union select bis as von from daten) as d1 where d1.von < d2.von)
as number, von from (select von from daten union select bis as von from
daten) d2) as bar join (select (select count(1)+1 from (select von from
daten union select bis as von from daten) as d1 where d1.von < d2.von)
as number, von from (select von from daten union select bis as von from
daten) d2) as foo using (number);
ERROR: ExecEvalExpr: unknown expression type 108
To explain what I'm actually trying to do, I'll at first visualize the
data:
1011121314151617t
--->
[-2--]
[-3]
[---1--]
[--9-]
[---4--]
This displays use periods for microphones, with the number of
microphones for each period of time. So from 10 to 12, one guy needs 2
microphones, and from 11 to 14 another guy needs 3, and so on.
Now let's suppose a third guy asks, if we have enough microphones for
his event from 10 to 17. I know how many microphones there are at all.
So I want to know the maximum number of microphones that are used at a
point in time within that period 10-17. That number is 12 obviously.
It's easy to find out how many microphones are in use at a certain point
in time:
datetest=# select sum(menge) from daten where von < '2000-01-01 10:30'
and bis > '2000-01-01 10:30';
sum
-
6
(1 запись)
So I could ask the maximum of sum for each point in time from 10 to 17.
This is a lot of points in time. So if two points in time don't differ
with respect to the number of microphones in use, I want to look at only
one of them. For that I query the points in time where something
changes:
datetest=# select von from daten union select bis as von from daten;
von
-
2000-01-01 10:00:00
2000-01-01 11:00:00
2000-01-01 12:00:00
2000-01-01 14:00:00
2000-01-01 15:00:00
2000-01-01 16:00:00
(записей: 6)
Now I want the point in time in the middle between each two subsequent
points in time, i.e. 10:30, 11:30, 12:30, 14:30, 15:30. These would be
the points in time I have to query the sum of microphones in use, then I
have to take the maximum of these sums. To achieve that, I insert a
record counter, topdog from IRC kindly showed me how to do that:
datetest=# select (select count(1) from (select von from daten union
select bis as von from daten) as d1 where d1.von < d2.von) as number,
von from (select von from daten union select bis as von from daten) d2;
number | von
+-
0 | 2000-01-01 10:00:00
1 | 2000-01-01 11:00:00
2 | 2000-01-01 12:00:00
3 | 2000-01-01 14:00:00
4 | 2000-01-01 15:00:00
5 | 2000-01-01 16:00:00
(записей: 6)
And another one:
datetest=# select (select count(1) + 1 from (select von from daten union
select bis as von from daten) as d1 where d1.von < d2.von) as number,
von from (select von from daten union select bis as von from daten) d2;
number | von
+-
1 | 2000-01-01 10:00:00
2 | 2000-01-01 11:00:00
3 | 2000-01-01 12:00:00
4 | 2000-01-01 14:00:00
5 | 2000-01-01 15:00:00
6 | 2000-01-01 16:00:00
(записей: 6)
Now I want to join these using number and then calculate the average of
both von columns:
datetest=# select * from (select (select count(1) from (select von from
daten union select bis as von from daten) as d1 where d1.von < d2.von)
as number, von from (select von from daten union select bis as von from
daten) d2) as table1 join (select (select count(1) + 1 from (select von
from
Re: [SQL] ERROR: ExecEvalExpr: unknown expression type 108
> datetest=# select * from (select (select count(1) from (select von from > daten union select bis as von from daten) as d1 where d1.von < d2.von) > as number, von from (select von from daten union select bis as von from > daten) d2) as table1 join (select (select count(1) + 1 from (select von > from daten union select bis as von from daten) as d1 where d1.von < > d2.von) as number, von from (select von from daten union select bis as > von from daten) d2) as table2 using (number); > ERROR: ExecEvalExpr: unknown expression type 108 > > Is that a bug? Yes. Regardless of whether this is a valid SQL statement, it shouldn't be caught in this manner. SubSelects have undergone a large simplification by Tom while implementing read-only plans in the executor. This seems to have accidentally fixed this case. rbt=# create table daten rbt-# ( id serial not null primary key rbt(# , menge integer rbt(# , von timestamp(0) without time zone rbt(# , bis timestamp(0) without time zone rbt(# ); NOTICE: CREATE TABLE will create implicit sequence 'daten_id_seq' for SERIAL column 'daten.id' NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'daten_pkey' for table 'daten' CREATE TABLE rbt=# rbt=# INSERT INTO daten rbt-# VALUES (DEFAULT, 2, '2000-01-01 10:00:00', '2000-01-01 12:00:00'); INSERT 17132 1 rbt=# rbt=# rbt=# INSERT INTO daten rbt-# VALUES (DEFAULT, 3, '2000-01-01 11:00:00', '2000-01-01 14:00:00'); INSERT 17133 1 rbt=# rbt=# rbt=# INSERT INTO daten rbt-# VALUES (DEFAULT, 1, '2000-01-01 14:00:00', '2000-01-01 15:00:00'); INSERT 17134 1 rbt=# rbt=# INSERT INTO daten rbt-# VALUES (DEFAULT, 8, '2000-01-01 12:00:00', '2000-01-01 16:00:00'); INSERT 17135 1 rbt=# rbt=# rbt=# INSERT INTO daten rbt-# VALUES (DEFAULT, 4, '2000-01-01 10:00:00', '2000-01-01 11:00:00'); INSERT 17136 1 rbt=# rbt=# select * from (select (select count(1) from (select von from rbt(# daten union select bis as von from daten) as d1 where d1.von < d2.von) rbt(# as number, von from (select von from daten union select bis as von from rbt(# daten) d2) as bar join (select (select count(1)+1 from (select von from rbt(# daten union select bis as von from daten) as d1 where d1.von < d2.von) rbt(# as number, von from (select von from daten union select bis as von from rbt(# daten) d2) as foo using (number); number | von | von +-+- 1 | 2000-01-01 11:00:00 | 2000-01-01 10:00:00 2 | 2000-01-01 12:00:00 | 2000-01-01 11:00:00 3 | 2000-01-01 14:00:00 | 2000-01-01 12:00:00 4 | 2000-01-01 15:00:00 | 2000-01-01 14:00:00 5 | 2000-01-01 16:00:00 | 2000-01-01 15:00:00 (5 rows) rbt=# select version(); version PostgreSQL 7.4devel on i386-unknown-freebsd4.8, compiled by GCC 2.95.4 (1 row) -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] ERROR: ExecEvalExpr: unknown expression type 108
Markus Bertheau <[EMAIL PROTECTED]> writes: > PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 > datetest=# select * from (select (select count(1) from (select von from > daten union select bis as von from daten) as d1 where d1.von < d2.von) > as number, von from (select von from daten union select bis as von from > daten) d2) as bar join (select (select count(1)+1 from (select von from > daten union select bis as von from daten) as d1 where d1.von < d2.von) > as number, von from (select von from daten union select bis as von from > daten) d2) as foo using (number); > ERROR: ExecEvalExpr: unknown expression type 108 7.3 has a problem with subselects referenced as join outputs. There is a fix in 7.3.1, and a better fix in place for 7.4. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ERROR: ExecEvalExpr: unknown expression type 108
Rod Taylor <[EMAIL PROTECTED]> writes: > SubSelects have undergone a large simplification by Tom while > implementing read-only plans in the executor. This seems to have > accidentally fixed this case. Nothing accidental about it. http://archives.postgresql.org/pgsql-general/2002-12/msg00375.php 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] need some help with a delete statement
On Monday, June 30, 2003, at 05:06 AM, scott.marlowe wrote:
On Fri, 27 Jun 2003, Matthew Hixson wrote:
Hi, I have a bunch of records that I need to delete from our database.
These records represent shopping carts for visitors to our website.
The shopping carts I'd like to delete are the ones without anything in
them. Here is the schema:
create sequence carts_sequence;
create table carts(
cart_id integer default nextval('carts_sequence') primary key,
cart_cookie varchar(24));
create sequence cart_contents_sequence;
create table cart_contents(
cart_contents_id integer default nextval('cart_contents_sequence')
primary key,
cart_id integer not null,
content_id integer not null,
expire_time timestamp);
I'm trying to use this query to delete the carts that are not
referenced from the cart_contents table.
delete from carts where cart_id in (select cart_id from carts except
(select distinct cart_id from cart_contents));
My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries in
v_carts and only 3746 entries in v_cart_contents. Clearly there are a
very large number of empty carts. Running the delete statement above
runs for over 15 minutes on this machine. I just cancelled it because
I want to find a faster query to use in case I ever need to do this
again. While the query is running the disk does not thrash at all.
It
is definitely CPU bound.
Limiting the statement to 1 item takes about 12 seconds to run:
delete from carts where cart_id in (select cart_id from carts except
(select distinct cart_id from cart_contents) limit 1);
Time: 12062.16 ms
While in() is notoriously slow, this sounds more like a problem where
your
query is having to seq scan due to mismatching or missing indexes.
So, what kind of index do you have on cart_id,
Its is a btree index.
Table "public.carts"
Column| Type |Modifiers
-+---
+--
cart_id | integer | not null default
nextval('carts_sequence'::text)
cart_cookie | character varying(24) |
Indexes: v_carts_pkey primary key btree (cart_id),
cart_cart_cookie btree (cart_cookie)
and what happens if you:
select cart_id from carts except
(select distinct cart_id from cart_contents) limit 1;
then feed the cart_id into
explain analyze delete from carts where cart_id=id_from_above;
from psql?
#explain analyze delete from carts where cart_id=2700;
QUERY PLAN
Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1 width=6)
(actual time=162.14..162.17 rows=1 loops=1)
Index Cond: (cart_id = 2700)
Total runtime: 162.82 msec
(3 rows)
Is cart_id a fk to another table (or is another table using it as a
fk?)
cart_id is the pk of the carts table. cart_contents also has a cart_id
and that is the fk pointing to its entry in the carts table. There is
nothing else using cart_id in either of those tables as a fk.
Thanks for the reply,
-M@
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] need some help with a delete statement
On Mon, 30 Jun 2003, Matthew Hixson wrote:
> On Monday, June 30, 2003, at 05:06 AM, scott.marlowe wrote:
>
> > On Fri, 27 Jun 2003, Matthew Hixson wrote:
> >
> >> Hi, I have a bunch of records that I need to delete from our database.
> >> These records represent shopping carts for visitors to our website.
> >> The shopping carts I'd like to delete are the ones without anything in
> >> them. Here is the schema:
> >>
> >> create sequence carts_sequence;
> >> create table carts(
> >> cart_id integer default nextval('carts_sequence') primary key,
> >> cart_cookie varchar(24));
> >>
> >> create sequence cart_contents_sequence;
> >> create table cart_contents(
> >> cart_contents_id integer default nextval('cart_contents_sequence')
> >> primary key,
> >> cart_id integer not null,
> >> content_id integer not null,
> >> expire_time timestamp);
> >>
> >> I'm trying to use this query to delete the carts that are not
> >> referenced from the cart_contents table.
> >>
> >> delete from carts where cart_id in (select cart_id from carts except
> >> (select distinct cart_id from cart_contents));
> >>
> >> My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
> >> running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries in
> >> v_carts and only 3746 entries in v_cart_contents. Clearly there are a
> >> very large number of empty carts. Running the delete statement above
> >> runs for over 15 minutes on this machine. I just cancelled it because
> >> I want to find a faster query to use in case I ever need to do this
> >> again. While the query is running the disk does not thrash at all.
> >> It
> >> is definitely CPU bound.
> >>Limiting the statement to 1 item takes about 12 seconds to run:
> >>
> >> delete from carts where cart_id in (select cart_id from carts except
> >> (select distinct cart_id from cart_contents) limit 1);
> >> Time: 12062.16 ms
> >
> > While in() is notoriously slow, this sounds more like a problem where
> > your
> > query is having to seq scan due to mismatching or missing indexes.
> >
> > So, what kind of index do you have on cart_id,
>
> Its is a btree index.
>
> Table "public.carts"
> Column| Type |Modifiers
> -+---
> +--
> cart_id | integer | not null default
> nextval('carts_sequence'::text)
> cart_cookie | character varying(24) |
> Indexes: v_carts_pkey primary key btree (cart_id),
> cart_cart_cookie btree (cart_cookie)
>
>
> > and what happens if you:
> >
> > select cart_id from carts except
> > (select distinct cart_id from cart_contents) limit 1;
> >
> > then feed the cart_id into
> >
> > explain analyze delete from carts where cart_id=id_from_above;
> >
> > from psql?
>
> #explain analyze delete from carts where cart_id=2700;
> QUERY PLAN
>
>
> Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1 width=6)
> (actual time=162.14..162.17 rows=1 loops=1)
> Index Cond: (cart_id = 2700)
> Total runtime: 162.82 msec
> (3 rows)
what does the output of psql say if you have the /timing switch on?
>
>
> >
> > Is cart_id a fk to another table (or is another table using it as a
> > fk?)
>
> cart_id is the pk of the carts table. cart_contents also has a cart_id
> and that is the fk pointing to its entry in the carts table. There is
> nothing else using cart_id in either of those tables as a fk.
>Thanks for the reply,
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] cleaning up useless pl/pgsql functions
Hi Do you have any easy script to remove all pl/pgsql function? After a lot of changes inside "create or replace function..." scripts I have a big mess. I want to remove all user defined pl/pgsql functions and restore some of them from my scripts again. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] cleaning up useless pl/pgsql functions
Dnia 2003-06-30 23:13, Użytkownik Tomasz Myrta napisał: Hi Do you have any easy script to remove all pl/pgsql function? After a lot of changes inside "create or replace function..." scripts I have a big mess. I want to remove all user defined pl/pgsql functions and restore some of them from my scripts again. Regards, Tomasz Myrta Hmm Answer to myself: DELETE from pg_proc where prolang =(select oid from pg_language where lanname='plpgsq'); What do you think about it? Tomasz ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Need help creating a BEFORE DELETE trigger
Hello everyone, I'm creating two database tables that will be used to cache the results of a search. Basically, when a search is initiated, an entry will be created in the "Search" table that represents the search, and a single entry will be created in a child table "SearchResults" for every result returned. A foreign key relationship will be associated between the two. CREATE TABLE Search ( id SERIAL , accountid INTEGER REFERENCES account(id) ON DELETE CASCADE NOT NULL , sessionnum CHAR(32) UNIQUE NOT NULL , createdTIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL , sqlcodeTEXT ); CREATE TABLE SearchResults_Customer ( idSERIAL , searchid INTEGER REFERENCES search(id) ON DELETE CASCADE NOT NULL , customeridINTEGER REFERENCES customer(id) ON DELETE CASCADE -- All the results go in fields here ); Now, when any record is deleted in the SearchResults table (via an ON DELETE CASCADE, or other trigger), I'd like the entire search set to be deleted since the search is now invalid. Therefore, if a single record in the SearchResults table is deleted, I want it to instead delete the associated record in the Search table; this'll cause a CASCADE into the SearchResults table, toasting my entire result set. The problem I'm looking at is: could this cause a recursion problem, where the cascading deletion will try to cause the whole thing to cascade again? How can I set this up so I can kill an entire tree of data if any one of it's members dies? Thanks in advance. -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "I don't know, " said the voice on the PA, "apathetic bloody planet, I've no sympathy at all. " ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] cleaning up useless pl/pgsql functions
Tomasz,
> Hmm Answer to myself:
>
> DELETE from pg_proc where prolang =(select oid from pg_language where
> lanname='plpgsq');
>
> What do you think about it?
You probably also want to put a "proowner = {your userid}" condition in there,
just in case there are any builtins/contrib stuff that uses plpgsql.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] help with "delete joins"
create table foo (a int, b int, c int, d text); create table bar (a int, b int, c int); insert into foo values (1,2,3,'a'); insert into foo values (1,2,4,'A'); insert into foo values (4,5,6,'b'); insert into foo values (7,8,9,'c'); insert into foo values (10,11,12,'d'); insert into bar values (1,2,3); insert into bar values (7,8,9); insert into bar values (10,11,12); what i want to do is: delete * from foo where not (foo.a = bar.a and foo.b=bar.b and foo.c=bar.c) ; so i end up with postgres=# select * from foo; a | b | c | d ---+---+---+--- 1 | 2 | 4 | A 4 | 5 | 6 | b (2 rows) but thats not valid sql, is there some way to accomplish this? Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] help with "delete joins"
On 30 Jun 2003, Robert Treat wrote: > create table foo (a int, b int, c int, d text); > > create table bar (a int, b int, c int); > > insert into foo values (1,2,3,'a'); > insert into foo values (1,2,4,'A'); > insert into foo values (4,5,6,'b'); > insert into foo values (7,8,9,'c'); > insert into foo values (10,11,12,'d'); > > insert into bar values (1,2,3); > insert into bar values (7,8,9); > insert into bar values (10,11,12); > > what i want to do is: > > delete * from foo where not (foo.a = bar.a and foo.b=bar.b and > foo.c=bar.c) ; > > so i end up with > > postgres=# select * from foo; > a | b | c | d > ---+---+---+--- > 1 | 2 | 4 | A > 4 | 5 | 6 | b > (2 rows) > > but thats not valid sql, is there some way to accomplish this? Maybe something like: delete from foo where exists (select * from bar where bar.a=foo.a and bar.b=foo.b and bar.c=foo.c); ---(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] help with "delete joins"
On Mon, 2003-06-30 at 18:26, Robert Treat wrote:
> create table foo (a int, b int, c int, d text);
>
> create table bar (a int, b int, c int);
>
> insert into foo values (1,2,3,'a');
> insert into foo values (1,2,4,'A');
> insert into foo values (4,5,6,'b');
> insert into foo values (7,8,9,'c');
> insert into foo values (10,11,12,'d');
>
> insert into bar values (1,2,3);
> insert into bar values (7,8,9);
> insert into bar values (10,11,12);
>
> what i want to do is:
>
> delete * from foo where not (foo.a = bar.a and foo.b=bar.b and
> foo.c=bar.c) ;
>
> so i end up with
>
> postgres=# select * from foo;
> a | b | c | d
> ---+---+---+---
> 1 | 2 | 4 | A
> 4 | 5 | 6 | b
> (2 rows)
>
> but thats not valid sql, is there some way to accomplish this?
>
ok, i have a solution from the other end:
create table baz as select * from foo except (select foo.* from foo,bar
where foo.a = bar.a and foo.b=bar.b and foo.c=bar.c);
but i'd still be interested in a delete based method :-)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] cleaning up useless pl/pgsql functions
"Tomasz Myrta" <[EMAIL PROTECTED]> wrote:
> Hi
> Do you have any easy script to remove all pl/pgsql function? After a lot
of
> changes inside "create or replace function..." scripts I have a big mess.
I
> want to remove all user defined pl/pgsql functions and restore some of
them
> from my scripts again.
You shall be able to identify the name of your own function and do:
SELECT 'DROP function ' || proname || ' ('|| oidvectortypes(proargtypes) ||
');' from pg_proc WHERE proname ~ '^sp_';
I identify my own function because the prefix sp_.
I hope that this help you.
Gaetano
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Bitwise operation
Bruno,Hubert, Thanks. It's all crystal clear now. Now I can keep moving on with my app. If all goes well I hope to add a 'techdoc' on how to do this. I've found documents from other DBMS on using Bitwise operations but not PG. Cheers Rudi. Bruno Wolff III wrote: On Mon, Jun 30, 2003 at 22:28:15 +1000, Rudi Starcevic <[EMAIL PROTECTED]> wrote: Hi, I'm learning to use Postgresql's bitwise operator's as I'm interested in building super fast search's based on user selections in web forms. So far so good but I have just lost it a little so I thought I'd post. Please growl at me if I'm asking on the wrong list :-) I understand this: SELECT 111 & 11 = 11 but not this SELECT & 111 = 71 I was expecting the second example to be SELECT & 111 = 111 In the above examples the numbers are decimal values. Maybe you wanted to do something like the following: area=> select b'' & b'0111'; ?column? -- 0111 (1 row) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help with "delete joins"
Robert, > delete * from foo where not (foo.a = bar.a and foo.b=bar.b and > foo.c=bar.c) ; > > so i end up with > > postgres=# select * from foo; > a | b | c | d > ---+---+---+--- > 1 | 2 | 4 | A > 4 | 5 | 6 | b > (2 rows) > > but thats not valid sql, is there some way to accomplish this? Um, your example result doesn't match your pseudo-query. Assuming that you want to delete everything that DOES match, not everything that DOESN'T, do: DELETE FROM foo WHERE EXISTS ( SELECT bar.a FROM bar WHERE bar.a = foo.a AND bar.b = foo.b AND bar.c = foo.c ); -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] cleaning up useless pl/pgsql functions
On Mon, 2003-06-30 at 21:13, Tomasz Myrta wrote: > Hi > Do you have any easy script to remove all pl/pgsql function? After a lot of > changes inside "create or replace function..." scripts I have a big mess. I > want to remove all user defined pl/pgsql functions and restore some of them > from my scripts again. DROP PROCEDURAL LANGUAGE plpgsql CASCADE; CREATE PROCEDURAL LANGUAGE plpgsql ... -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Need help creating a BEFORE DELETE trigger
> The problem I'm looking at is: could this cause a recursion problem, where the > cascading deletion will try to cause the whole thing to cascade again? How It will only be able to delete the row (and cascade) once per row. The second time it tries to find the row, the row won't exist anymore. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[SQL] CREATE SEQUENCE fails in plpgsql function
Hi, I need a table to hold the last n INSERTs into it. To keep track of how many entries there are, and to provide a unique id to order the records in the table, I use a sequence. A trigger function deletes and entry on an insert if the table is full. The number n maybe changed, so I implemented this PL/PgSQL function: CREATE OR REPLACE FUNCTION set_max_records(integer) RETURNS integer AS ' DECLARE new_max_records ALIAS FOR $1; BEGIN DROP SEQUENCE my_sequence; --CREATE SEQUENCE my_sequence MAXVALUE 4 CYCLE; CREATE SEQUENCE my_sequence MAXVALUE new_max_records CYCLE; RETURN 0; END; ' LANGUAGE 'plpgsql'; (I left out the part where the table is shrunk and renumbered if n goes down). rdb=# select set_max_records(3); LOG: query: CREATE SEQUENCE my_sequence MAXVALUE $1 CYCLE LOG: statement: select set_max_records(3); WARNING: Error occurred while executing PL/pgSQL function set_max_records LOG: statement: select set_max_records(3); WARNING: line 6 at SQL statement LOG: statement: select set_max_records(3); DEBUG: AbortCurrentTransaction ERROR: parser: parse error at or near "$1" at character 39 If I don't use the variable new_max_records, it works (the commented out line). What could be the problem ? Also, if there is a better mechanism to implement this, I'm all ears... Erik Erkelens. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] LEAST and GREATEST functions?
Hi, I know the LEAST and GREATEST functions are not part of standard SQL, but they sure were handy where I came from (Oracle-land). Has anyone written user-defined functions that do the same thing? Are there any plans to add these functions as part of a future version Postgres? Thanks, -Stefan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LEAST and GREATEST functions?
Stefan, > I know the LEAST and GREATEST functions are not part > of standard SQL, but they sure were handy where I came > from (Oracle-land). > > Has anyone written user-defined functions that do the > same thing? > > Are there any plans to add these functions as part of > a future version Postgres? Um, what's wrong with MAX and MIN, exactly? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CREATE SEQUENCE fails in plpgsql function
On Mon, 30 Jun 2003, Erik Erkelens wrote: > new_max_records ALIAS FOR $1; > BEGIN > DROP SEQUENCE my_sequence; > --CREATE SEQUENCE my_sequence MAXVALUE 4 > CYCLE; > CREATE SEQUENCE my_sequence MAXVALUE > new_max_records CYCLE; Most of the creates/drops/etc... don't directly work with variables/arguments. You can probably do this with execute however. ---(end of broadcast)--- TIP 8: explain analyze is your friend
