[SQL] Classes, Inheritance, and Children
I think I may have asked this before... If I did I'm sorry, but maybe this attempt, assuming a prior one, may be a little more clear. create table foo (id int8); create table bar1 (name text) inherits (foo); create table bar2 (data text) inherits (foo); create table hybrid ( ) inherits (bar1, bar2); INSERT INTO foo VALUES (1); INSERT INTO bar1 VALUES (2,'myname'); INSERT INTO bar2 VALUES (3,'mydata'); INSERT INTO hybrid VALUES (4,'morename','moredata'); I want to do a SELECT * FROM foo*; but I only get the 'id' column as in : id --- 1 2 3 4 What would be the query to get the following table or a magical way to expand children? I had originally hoped that SELECT * FROM foo* would yield the following, but it's not so. id | name | data ---++- 1 | null | null 2 | 'myname' | null 3 | null | 'mydata' 4 | 'morename' | 'moredata' | | I tried SELECT id, name AS NULL, data AS NULL FROM foo*; but that didn't do anything but make 2 null columns... Any help would be ... helpful... Thanks, Thomas
[SQL] Large text insertion
Can anybody tell me, how I can insert text data larger then 20k in database wihtout using large objects?
Re: [SQL] Classes, Inheritance, and Children
Thomas Swan wrote: >I think I may have asked this before... If I did I'm sorry, but maybe this >attempt, assuming a prior one, may be a little more clear. > >create table foo (id int8); >create table bar1 (name text) inherits (foo); >create table bar2 (data text) inherits (foo); >create table hybrid ( ) inherits (bar1, bar2); > >INSERT INTO foo VALUES (1); >INSERT INTO bar1 VALUES (2,'myname'); >INSERT INTO bar2 VALUES (3,'mydata'); >INSERT INTO hybrid VALUES (4,'morename','moredata'); > > >I want to do a SELECT * FROM foo*; but I only get the 'id' column as in : > >id >--- > 1 > 2 > 3 > 4 This is correct in object-oriented theory. foo only knows about its own features; it does not know about additional features of its descendants, nor should it. >What would be the query to get the following table or a magical way to >expand children? > >I had originally hoped that SELECT * FROM foo* would yield the following, >but it's not so. > >id | name | data >---++- > 1 | null | null > 2 | 'myname' | null > 3 | null | 'mydata' > 4 | 'morename' | 'moredata' You need to use a UNION of the four tables, with nulls supplied where necessary: select * from hybrid -- specify first to establish the -- column types union select id, null, null from foo union select id, name, null from bar1 union select id, null, data from bar2; Unfortunately, you can't make this a view, because views of unions are not yet supported. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But the wisdom that is from above is first pure, then peaceable, gentle, and easy to be intreated, full of mercy and good fruits, without partiality, and without hypocrisy." James 3:17
Re: [SQL] Large text insertion
Vladimir Terziev wrote: > >Can anybody tell me, how I can insert text data larger then 20k in database > wihtout using large objects? > 7.1 will be able to hold megabytes in the "text" data type. It's already in the CURRENT sources and works well. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Antw: Re: [SQL] Large text insertion
>>> [EMAIL PROTECTED] 27.07.2000 11.39 Uhr >>> >7.1 will be able to hold megabytes in the "text" data type. >It's already in the CURRENT sources and works well. Is there a release date visible at the horizon? Gerhard
RE: Re(2): [SQL] optimize sql
If you know that 't' will always be the highest character in the active field for all records: SELECT name FROM office, office_application WHERE code = office_code GROUP BY name HAVING MAX(active) < 't' Of course, if you have an active that is 'z' for example, then this won't work. I think this should work also regardless of max(active) for the table: SELECT name FROM office, office_application WHERE code = office_code AND active <= 't' GROUP BY name HAVING MAX(active) < 't' UNION SELECT name FROM office, office_application WHERE code = office_code AND active >= 't' GROUP BY name HAVING MIN(active) > 't' Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 26, 2000 9:40 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re(2): [SQL] optimize sql [EMAIL PROTECTED] writes: >How does the output of the above differ from: > >SELECT name FROM office, office_application >WHERE code = office_code >AND active != 't'; > >Without knowing the table structures (which tables to active, code, >and office_code belong to?) it's hard to suggest much else. > >Ross The name and code fields belong to office table. While office_code and active fields belong to office_application table. The name field have duplicates and among the duplicates, only one active field is TRUE. I just wanted to get name field that has no TRUE active field. Any other idea? Thanks. sherwin
[SQL] Compile
I must compile my program (who is accessing to DB) with g++, but his methods are using by another program who is compiling in CC. how do i ? thanks.
[SQL] Aggregates and Primary Keys
Hi, I have this: create table a ( x int4 primary key, dat int4, count int4 ) ; create table b ( x int4 references a(x), count int4 ) ; insert into a values ( 1, 1, 10 ) ; insert into a values ( 2, 2, 20 ) ; insert into b values ( 1, 2 ) ; insert into b values ( 1, 3 ) ; insert into b values ( 2, 3 ); insert into b values ( 2, 4 ); select * from a ; select * from b ; x | dat | count ---+-+--- 1 | 1 |10 2 | 2 |20 (2 rows) x | count ---+--- 1 | 2 1 | 3 2 | 3 2 | 4 (4 rows) select a.x, a.dat, a.count - sum(b.count) from a, b where a.x = b.x group by a.x, a.dat, a.count ; x | ?column? ---+-- 1 |5 2 | 13 (2 rows) My concern is with the "group by" clause. Strictly speaking, it shouldn't be necessary to *also* group by a.dat and a.count, since a.x is a primary key, right? Is there some performance loss in specifying a.dat and a.count in the group by? Should I be doing this some other way? Thanks, -itai
[SQL] Conditional rule?
I've been looking through the material I have on postgreSQL, but can't seem to find an answer to my problem. Very simplied, my tables are something like this: create table news ( id serial, story text, publishtime timestamp ) create table news_unpublished ( news_id ) I wish to make rule looking something like this: create rule newsrule as on insert to news do if new.publishtime is not null insert into news_unpublished values(new.id); I.e. "On an insert to news, if new.publish is not null, insert the new post's id into news_unpublished. Is this possible? Thanks André Næss
Re: [SQL] Conditional rule?
> I.e. "On an insert to news, if new.publish is not null, insert the new > post's id into news_unpublished. How about: CREATE RULE newsrule AS ON INSERT TO news DO INSERT INTO news_unpublished SELECT NEW.id WHERE NEW.publishtime IS NOT NULL -itai
[SQL] Automatic Deletes?
Hi, I have: CREATE TABLE a ( id SERIAL, val INT4 ) ; I would like to create some rules to keep "a" free from rows where val == 0. Something like: CREATE RULE a_insert AS ON INSERT TO a WHERE NEW.val = 0 DO INSTEAD NOTHING ; CREATE RULE a_update AS ON UPDATE TO a WHERE NEW.val = 0 DO INSTEAD DELETE FROM a WHERE id = NEW.id ; Does this look right? I think there should be a better way to express the second rule... Thanks, -itai
Re: [SQL] Conditional rule?
"=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <[EMAIL PROTECTED]> writes: > I wish to make rule looking something like this: > create rule newsrule as > on insert to news do > if new.publishtime is not null insert into news_unpublished > values(new.id); > I.e. "On an insert to news, if new.publish is not null, insert the new > post's id into news_unpublished. What you want here is a trigger, not a rule. The closest you could come with a rule is to copy *all* unpublished ids into news_unpublished each time something got inserted into news. There are applications for that sort of thing, but this ain't it. See the trigger examples in the plpgsql or pltcl sections of the manual. regards, tom lane
Re: [SQL] Conditional rule?
Hm... I'm a true newbie when it comes to plpgsql and triggers, but I looked
through some texts and managed to come up with the following rather odd (to
me at least) behaviour:
*
create table news (
id serial,
title varchar(50),
time timestamp
)
create table news_un (
news_id int
)
Table "news_un"
Attribute | Type | Modifier
---+-+--
news_id | integer |
create function setpublish() returns opaque as '
begin
insert into news_un select news_id_seq.last_value where new.time is not
null;
return null;
end;
'
language 'plpgsql';
create trigger newstrigger after insert on news for each row execute
procedure setpublish();
*
Attempting to do an insert to news:
testruledb=# insert into news (title, time) values('Test', now());
INSERT 24028 1
testruledb=# select * from news; select * from news_un;
id | title | time
+---+
48 | Test | 2000-07-27 19:20:24+02
(1 row)
news_id
-
47
48
(2 rows)
I also tried setting time to null:
testruledb=# insert into news (title) values('Test2');
INSERT 24031 1
testruledb=# select * from news; select * from news_un;
id | title | time
+---+--
50 | Test2 |
(1 row)
news_id
-
49
(1 row)
There's obviously something about triggers and functions I don't understand,
any help would be greatly appreciated.
Thanks
André Næss
- Original Message -
> "=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <[EMAIL PROTECTED]> writes:
> > I wish to make rule looking something like this:
> > create rule newsrule as
> > on insert to news do
> > if new.publishtime is not null insert into news_unpublished
> > values(new.id);
>
> > I.e. "On an insert to news, if new.publish is not null, insert the new
> > post's id into news_unpublished.
>
> What you want here is a trigger, not a rule. The closest you could come
> with a rule is to copy *all* unpublished ids into news_unpublished each
> time something got inserted into news. There are applications for that
> sort of thing, but this ain't it. See the trigger examples in the
> plpgsql or pltcl sections of the manual.
>
> regards, tom lane
>
Re: [SQL] Conditional rule?
Ooops... seems I had a rule tied to my news table which caused the
malfunction, sorry if I wasted anyone's time :(
André Næss
- Original Message -
From: "André Næss" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 27, 2000 7:21 PM
Subject: Re: [SQL] Conditional rule?
> Hm... I'm a true newbie when it comes to plpgsql and triggers, but I
looked
> through some texts and managed to come up with the following rather odd
(to
> me at least) behaviour:
>
> *
>
> create table news (
> id serial,
> title varchar(50),
> time timestamp
> )
>
> create table news_un (
> news_id int
> )
>
> Table "news_un"
> Attribute | Type | Modifier
> ---+-+--
> news_id | integer |
>
> create function setpublish() returns opaque as '
> begin
> insert into news_un select news_id_seq.last_value where new.time is
not
> null;
> return null;
> end;
> '
> language 'plpgsql';
>
> create trigger newstrigger after insert on news for each row execute
> procedure setpublish();
>
> *
> Attempting to do an insert to news:
>
> testruledb=# insert into news (title, time) values('Test', now());
> INSERT 24028 1
> testruledb=# select * from news; select * from news_un;
> id | title | time
> +---+
> 48 | Test | 2000-07-27 19:20:24+02
> (1 row)
>
> news_id
> -
> 47
> 48
> (2 rows)
>
> I also tried setting time to null:
>
> testruledb=# insert into news (title) values('Test2');
> INSERT 24031 1
> testruledb=# select * from news; select * from news_un;
> id | title | time
> +---+--
> 50 | Test2 |
> (1 row)
>
> news_id
> -
> 49
> (1 row)
>
> There's obviously something about triggers and functions I don't
understand,
> any help would be greatly appreciated.
>
> Thanks
>
> André Næss
>
>
> - Original Message -
> > "=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <[EMAIL PROTECTED]> writes:
> > > I wish to make rule looking something like this:
> > > create rule newsrule as
> > > on insert to news do
> > > if new.publishtime is not null insert into news_unpublished
> > > values(new.id);
> >
> > > I.e. "On an insert to news, if new.publish is not null, insert the new
> > > post's id into news_unpublished.
> >
> > What you want here is a trigger, not a rule. The closest you could come
> > with a rule is to copy *all* unpublished ids into news_unpublished each
> > time something got inserted into news. There are applications for that
> > sort of thing, but this ain't it. See the trigger examples in the
> > plpgsql or pltcl sections of the manual.
> >
> > regards, tom lane
> >
>
>
>
[SQL] BLOBs
Hi, everyone, Browsing through the online manual, I didn't find anything related to BLOBs, but I know there must be BLOBs since many people are asking about it on the list and there is a pg_fetch_object() in PHP for Postgres. Could anyone points me an URL where I can look it up? Thanks. -Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
Re: [SQL] Conditional rule?
On Thu, 27 Jul 2000, André Næss wrote: > I've been looking through the material I have on postgreSQL, but can't seem > to find an answer to my problem. Very simplied, my tables are something like > this: > > create table news ( >id serial, >story text, >publishtime timestamp > ) > > create table news_unpublished ( > news_id > ) > > I wish to make rule looking something like this: > create rule newsrule as > on insert to news do > if new.publishtime is not null insert into news_unpublished > values(new.id); > > I.e. "On an insert to news, if new.publish is not null, insert the new > post's id into news_unpublished. > > Is this possible? > > Thanks > > André Næss I think a PL/pgSQL trigger will work: -- Load the PGSQL procedural language -- This could also be done with the createlang script/program. -- See man createlang. CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; CREATE FUNCTION news_trigproc RETURNS OPAQUE AS ' IF TG_OP = ''INSERT'' THEN -- unnessary IF above since this is always called on insert only -- but shows how can detect which OP called the trigger when -- you make a trigger handle more than just INSERT IF NEW.publishtime NOTNULL THEN INSERT INTO news_unpublished VALUES (NEW.id); END IF; RETURN NEW; END IF; ' LANGUAGE 'plpgsql'; CREATE TRIGGER newstrigger AFTER INSERT ON news FOR EACH ROW EXECUTE PROCEDURE news_trigproc(); -- - Robert
Re: [SQL] Conditional rule?
On Thu, 27 Jul 2000, Robert B. Easter wrote: > On Thu, 27 Jul 2000, André Næss wrote: > > I've been looking through the material I have on postgreSQL, but can't seem > > to find an answer to my problem. Very simplied, my tables are something like > > this: > > > > create table news ( > >id serial, > >story text, > >publishtime timestamp > > ) > > > > create table news_unpublished ( > > news_id > > ) > > > > I wish to make rule looking something like this: > > create rule newsrule as > > on insert to news do > > if new.publishtime is not null insert into news_unpublished > > values(new.id); > > > > I.e. "On an insert to news, if new.publish is not null, insert the new > > post's id into news_unpublished. > > > > Is this possible? > > > > Thanks > > > > André Næss > (forgot the BEGIN/END in the function!) I think a PL/pgSQL trigger will work: -- Load the PGSQL procedural language -- This could also be done with the createlang script/program. -- See man createlang. CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; CREATE FUNCTION news_trigproc RETURNS OPAQUE AS ' BEGIN IF TG_OP = ''INSERT'' THEN -- unnessary IF above since this is always called on insert only -- but shows how can detect which OP called the trigger when -- you make a trigger handle more than just INSERT IF NEW.publishtime NOTNULL THEN INSERT INTO news_unpublished VALUES (NEW.id); END IF; RETURN NEW; END IF; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER newstrigger AFTER INSERT ON news FOR EACH ROW EXECUTE PROCEDURE news_trigproc(); -- - Robert
Re: [SQL] Conditional rule?
Tom Lane wrote: > "=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <[EMAIL PROTECTED]> writes: > > I wish to make rule looking something like this: > > create rule newsrule as > > on insert to news do > > if new.publishtime is not null insert into news_unpublished > > values(new.id); > > > I.e. "On an insert to news, if new.publish is not null, insert the new > > post's id into news_unpublished. > > What you want here is a trigger, not a rule. The closest you could come > with a rule is to copy *all* unpublished ids into news_unpublished each > time something got inserted into news. There are applications for that > sort of thing, but this ain't it. See the trigger examples in the > plpgsql or pltcl sections of the manual. No. The rule CREATE RULE newsrule AS ON INSERT TO news WHERE new.publishtime NOTNULL DO INSERT INTO news_unpublished VALUES (new.id); should do the job perfectly. Maybe you want to have the following rules too: CREATE RULE newsrule2 AS ON UPDATE TO news WHERE old.publishtime ISNULL AND new.publishtime NOTNULL DO INSERT INTO news_unpublished VALUES (new.id); CREATE RULE newsrule3 AS ON UPDATE TO news WHERE old.publishtime NOTNULL AND new.publishtime ISNULL DO DELETE FROM news_unpublished WHERE news_unpublished.id = old.id; CREATE RULE newsrule4 AS ON DELETE TO news WHERE old.publishtime NOTNULL DO DELETE FROM news_unpublished WHERE news_unpublished.id = old.id; With these four rules, all the inserts and deletes are done automatically. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
