[SQL] three-way join
Hi folks, here's a straight forward join that I simply can't get my head round. I've got consumables:cs_id, cs_make, cs_comments cons_locations: cl_id, cl_desc cons_stock: cs_id, cl_id, status (1=ordered, 2=in-stock) (one stock record per stock item, qty=3 means 3 records) I'm struggling to create a quiery to produce cs_id, cs_make, cs_comments, cl_desc, hand_qty, order_qty where hand_qty and order_qty is the number of records grouped by cs_id, cl_id, and status. I've done the simple part and created a view balances to tally the cons_stock as: create view balances as select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty from cons_stock group by cost_cs_id, cost_cl_id, cost_css_id; I then have trouble joining this to the consumables and locations table to get the results I need. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] Concatenate results of a single column query
>
>
> Marco Lazzeri <[EMAIL PROTECTED]> writes:
>
> > SELECT
> > p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id)
> > FROM people AS p
>
> > Any suggestions?
>
> Something like:
>
> db=> create aggregate array_aggregate (basetype = integer, sfunc = array_append,
> stype = integer[], initcond = '{}');
> CREATE AGGREGATE
>
> db=> select array_aggregate(id) from tab;
>
> array_aggregate
>
>
>
> {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,29,30,31,32,33,34,36,37,38,39,40,41,42,43,27,26,44,45,46,47,48,49,50,51,52,53,54,35}
> (1 row)
>
> --
> greg
>
>
Interesting feature, but I cannot find function array_append:
ERROR: AggregateCreate: function array_append(integer[], integer) does not exist
TIA
Regards, Christoph
---(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] Concatenate results of a single column query
Christoph Haller <[EMAIL PROTECTED]> writes: > Interesting feature, but I cannot find function array_append: > ERROR: AggregateCreate: function array_append(integer[], integer) does not exist It's new in Postgres 7.4 I think you could do this in 7.3 though, it would just be more awkward. Try || but I think that's new in 7.4 as well. Otherwise I think you would have to pick out the upper bound of the array with array_dims and set the upper+1'th element of the array. If you're doing text you may want to go directly to a textual concatenation like: CREATE FUNCTION concat_agg_accum(text, text) RETURNS text AS 'select $1 || '', '' || $2' LANGUAGE sql IMMUTABLE STRICT; CREATE AGGREGATE concat_agg ( BASETYPE = text, SFUNC = concat_agg_accum, STYPE = text ); -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] three-way join
Gary wrote: > Hi folks, > > here's a straight forward join that I simply can't get my head round. > > I've got > > consumables: cs_id, cs_make, cs_comments > cons_locations: cl_id, cl_desc > cons_stock: cs_id, cl_id, status (1=ordered, 2=in-stock) > > (one stock record per stock item, qty=3 means 3 records) assuming that the PK's are: consumables : cs_id cons_loacations: cl_id cons_stock: cs_id, cl_id You could only have 1 record in cons_stock for each unique combination of consumable and location. If the primary key for cons_stock would also include the field status you could have 2 records for each unique combination of consumable and location, one where status is ordered, and one where status is in-stock. > I'm struggling to create a quiery to produce > > cs_id, cs_make, cs_comments, cl_desc, hand_qty, order_qty > > where hand_qty and order_qty is the number of records grouped > by cs_id, cl_id, > and status. Given the previous, the result for qty would be pretty obvious I think, since you would have only 1 record for the combination cs_id,cl_id and status. > > I've done the simple part and created a view balances to > tally the cons_stock > as: > > create view balances as > select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty > from cons_stock > group by cost_cs_id, cost_cl_id, cost_css_id; I don't understand where the cost_* fields come from, especially the cost_css_id field. Assuming that these fields are the cs_id, cl_id and status qty is most likley going to be 1 all the time? Maybe it's worth to rethink your database structure, or adding the qty fields to the table cons_stock and keeping them up-to-date? (eg. CONS_STOCK (cs_id, cl_id, hand_qty, order_qty) PK(cs_id, cl_id) ) that way you simply change the quantity fields for each combination of location-consumable according to the situation (and sound the alarm if the reach a certain level?). If anyone thinks I'm wrong, please correct me. Regards, Stijn Vanroye ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] three-way join
On Monday 19 April 2004 3:06 pm, Stijn Vanroye wrote:
> Gary wrote:
> > Hi folks,
> >
> > here's a straight forward join that I simply can't get my head round.
> >
> > I've got
> >
> > consumables:cs_id, cs_make, cs_comments
> > cons_locations: cl_id, cl_desc
> > cons_stock: cs_id, cl_id, status (1=ordered, 2=in-stock)
> >
> > (one stock record per stock item, qty=3 means 3 records)
>
> assuming that the PK's are:
> consumables : cs_id
> cons_loacations: cl_id
> cons_stock: cs_id, cl_id
> You could only have 1 record in cons_stock for each unique combination of
> consumable and location. If the primary key for cons_stock would also
> include the field status you could have 2 records for each unique
> combination of consumable and location, one where status is ordered, and
> one where status is in-stock.
Sorry for the confusion. For the purpose of simplicity I trimmed the info - a
little too far it seems.
cons_stock has as it's PK a serial field, cost_id (cost_ is the prefix I use
for fields on this table, the other fields therefore are cost_cs_id and
cost_cl_id). I need to be able to track individual items, and thus give it a
unique id. If I ordered 4 HP 4100 toners, they'd create 4 seperate records
even though the cost_cs_id and cost_cl_id's would all be the same.
>
> > I'm struggling to create a quiery to produce
> >
> > cs_id, cs_make, cs_comments, cl_desc, hand_qty, order_qty
> >
> > where hand_qty and order_qty is the number of records grouped
> > by cs_id, cl_id,
> > and status.
>
> Given the previous, the result for qty would be pretty obvious I think,
> since you would have only 1 record for the combination cs_id,cl_id and
> status.
>
> > I've done the simple part and created a view balances to
> > tally the cons_stock
> > as:
> >
> > create view balances as
> > select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty
> > from cons_stock
> > group by cost_cs_id, cost_cl_id, cost_css_id;
>
> I don't understand where the cost_* fields come from, especially the
> cost_css_id field. Assuming that these fields are the cs_id, cl_id and
> status qty is most likley going to be 1 all the time? Maybe it's worth to
> rethink your database structure, or adding the qty fields to the table
> cons_stock and keeping them up-to-date? (eg. CONS_STOCK (cs_id, cl_id,
> hand_qty, order_qty) PK(cs_id, cl_id) ) that way you simply change the
> quantity fields for each combination of location-consumable according to
> the situation (and sound the alarm if the reach a certain level?).
the cost_ (abrev of cons_stock) is the prefix of the fields on the cons_stock
field. consumables have prefix cs_ and locations have cl_. Therefore when
cons_stock references consumables id field it is called cost_cd_id.
>
> If anyone thinks I'm wrong, please correct me.
I hope my my explanation's cleared up the grey area. I've included all of the
relevent schema below to help show what I want.
create table cons_types (
cst_idserial not null unique,
cst_desc varchar(40),
primary key (cst_id)
);
insert into cons_types (cst_desc) values ('Toner cartridge'); -- 1
insert into cons_types (cst_desc) values ('Ink cartridge'); -- 2;
create table consumables (
cs_id serial not null unique,
cs_make varchar(40),
cs_code varchar(20),
cs_type int4 references cons_types(cst_id) not null,
cs_colour varchar(40),
cs_comments text,
primary key (cs_id)
);
insert into consumables (cs_make, cs_code, cs_type,cs_colour, cs_supp,
cs_comments) values
('HP', 'C4096A', 1, 'BLACK', 5, '2100 2 0');
create table cons_locations (
cl_id serial not null unique,
cl_desc varchar(40),
primary key (cl_id)
);
insert into cons_locations (cl_desc) values ('Leeds Computer Room');-- 1
create table cons_status (
css_idserial not null unique,
css_desc varchar(40),
primary key (css_id)
);
insert into cons_status (css_desc) values ('Ordered'); -- 1
insert into cons_status (css_desc) values ('In Stock'); -- 2
insert into cons_status (css_desc) values ('Issued'); -- 3
create table cons_stock (
cost_id serial not null unique,
cost_cs_idint4 references consumables(cs_id) not null,
cost_css_id int4 references cons_status(css_id) not null,
cost_cl_idint4 references cons_locations(cl_id) not null,
cost_supp int4 references contacts(co_id),
cost_comments text,
primary key (cost_id)
);
-- insert 2 HP 2100 toners in stock at Leeds
insert into cons_stock (cost_cs_id, cost_css_id, cost_cl_id) values
(1, 2, 1);
insert into cons_stock (cost_cs_id, cost_css_id, cost_cl_id) values
(1, 2, 1);
create view balances as
select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty
from cons_stock
group by cost_cs_id, cost_cl_id, cost_css_id;
>
>
> Regards,
>
> Stijn Vanroye
>
> ---(end of broadcast)---
Re: [SQL] Update is very slow on a bigger table
On Sat, 17 Apr 2004, Bruno Wolff III wrote: > On Fri, Apr 16, 2004 at 12:47:58 -0500, > Yudie <[EMAIL PROTECTED]> wrote: > > Hi guys, > > I would like to create a trigger that execute some programs, > > is there a way to create a plpgsql function that execute shell command or > > any other way to run a program? > > Thanks for your help. > > Only untrusted languages can be used to run external programs. perlu might > be something you can use. Also, take a look at plsh if all you need is to run shell functions: http://developer.postgresql.org/~petere/pgplsh/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Prepared Statements and large where-id-in constant blocks?
Howdy: Java middlewares like JBossCMP issue many queries in the general form of: SELECT t1.att1, t1.att2 ... t1.attN FROM t1 WHERE (t1.id = X) or (t1.id = Y) where there may be anywhere between 1 and thousands of "(id = N)" blocks ORed together. These may be transformed to the "WHERE t1.id IN (X, Y, ...)" form for possibly a little performance gain (possibly -- I've not yet checked to see if this plans better than the other, but I could imagine this form being parsed into the hashjoin form as opposed to a huge index filter form). Real performance gains, however, could be attained through being able to ultimately use either the v2 PREPARE / EXECUTE statements or the better v3 protocol's bind / execute commands, but only if the SQL-level form of the query could better represent the fact there are not really N params, but, rather, one single param of type Set (or, heck, Array?). This would let all such queries map onto one single backend prepared statement, regardless of the size of the id set being passed in. I guess that separate preparation for each different cardinality would be okay performance-wise, but if there were some way to get all such queries factored-down into one single planned statement, it could: 1) Make far better use of JBoss's connection-wide LRU cache of PreparedStatements, since only one entry (with much higher cache hit rate) could exist for the entire family of queries. 2) Make better use of backend memory, since it only needed to prepare one such (generic) form, as opposed to one for each cardinality. Problems in implementation: 1) JBoss query engine would need to be educated about the potential to use this form as opposed to the "OR (t1.id=X)" form. Likewise, JBoss could / should well be educated about being able to use the "WHERE t1.id IN (X, Y, ...)" form for databases which support "WHERE .. IN ( .. )", probably an easier sell since this is most likely supported by more DBs than just PG. 2) Does the JDBC spec allow any hooks for passing in a set of ids as one single param? We'd need the SQL-template to be prepared to look something like: SELECT t1.attr1 FROM t1 where t1.id in ( ? ) From memory, perhaps setArray() might could be hacked for the job. I know JBossCMP uses the setObject() call, so perhaps JDBC could be tricked out to handle a java.util.Collection, an arguably cleaner way to do it -- no backward compat issues since could be all-new functionality. JDBC driver could just iterate through the collection contents, calling setObject accordingly. Harder part would be educating JBoss to do this. Hardest part would be convincing someone to commit it into JBoss. 3) Can a cardinality-free plan even be made? I bet I'm assuming a little too much in asserting all such plans are equal, but I suspect that Tom is going to tell me that the query for just one id value would and should be planned differently from the 800-value form, since the 800-value form might well prefer a full sequential scan, since the table might only have 900 live rows in it. Anyone have any insight or opinions? [ crossposted to pgsql-sql for anyone's insight into the pure SQL / planning matters. Apologies in advance ]. James Robinson Socialserve.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] three-way join - solved
On Monday 19 April 2004 4:01 pm, Gary Stainburn wrote:
> On Monday 19 April 2004 3:06 pm, Stijn Vanroye wrote:
> > Gary wrote:
> > > Hi folks,
> > >
> > > here's a straight forward join that I simply can't get my head round.
> > >
> > > I've got
> > >
> > > consumables: cs_id, cs_make, cs_comments
> > > cons_locations: cl_id, cl_desc
> > > cons_stock: cs_id, cl_id, status (1=ordered, 2=in-stock)
> > >
> > > (one stock record per stock item, qty=3 means 3 records)
> >
> > assuming that the PK's are:
> > consumables : cs_id
> > cons_loacations: cl_id
> > cons_stock: cs_id, cl_id
> > You could only have 1 record in cons_stock for each unique combination of
> > consumable and location. If the primary key for cons_stock would also
> > include the field status you could have 2 records for each unique
> > combination of consumable and location, one where status is ordered, and
> > one where status is in-stock.
>
> Sorry for the confusion. For the purpose of simplicity I trimmed the info
> - a little too far it seems.
>
> cons_stock has as it's PK a serial field, cost_id (cost_ is the prefix I
> use for fields on this table, the other fields therefore are cost_cs_id and
> cost_cl_id). I need to be able to track individual items, and thus give it
> a unique id. If I ordered 4 HP 4100 toners, they'd create 4 seperate
> records even though the cost_cs_id and cost_cl_id's would all be the same.
>
> > > I'm struggling to create a quiery to produce
> > >
> > > cs_id, cs_make, cs_comments, cl_desc, hand_qty, order_qty
> > >
> > > where hand_qty and order_qty is the number of records grouped
> > > by cs_id, cl_id,
> > > and status.
> >
> > Given the previous, the result for qty would be pretty obvious I think,
> > since you would have only 1 record for the combination cs_id,cl_id and
> > status.
> >
> > > I've done the simple part and created a view balances to
> > > tally the cons_stock
> > > as:
> > >
> > > create view balances as
> > > select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty
> > > from cons_stock
> > > group by cost_cs_id, cost_cl_id, cost_css_id;
> >
> > I don't understand where the cost_* fields come from, especially the
> > cost_css_id field. Assuming that these fields are the cs_id, cl_id and
> > status qty is most likley going to be 1 all the time? Maybe it's worth to
> > rethink your database structure, or adding the qty fields to the table
> > cons_stock and keeping them up-to-date? (eg. CONS_STOCK (cs_id, cl_id,
> > hand_qty, order_qty) PK(cs_id, cl_id) ) that way you simply change the
> > quantity fields for each combination of location-consumable according to
> > the situation (and sound the alarm if the reach a certain level?).
>
> the cost_ (abrev of cons_stock) is the prefix of the fields on the
> cons_stock field. consumables have prefix cs_ and locations have cl_.
> Therefore when cons_stock references consumables id field it is called
> cost_cd_id.
>
> > If anyone thinks I'm wrong, please correct me.
>
> I hope my my explanation's cleared up the grey area. I've included all of
> the relevent schema below to help show what I want.
>
> create table cons_types (
> cst_id serial not null unique,
> cst_descvarchar(40),
> primary key (cst_id)
> );
> insert into cons_types (cst_desc) values ('Toner cartridge'); -- 1
> insert into cons_types (cst_desc) values ('Ink cartridge'); -- 2;
>
> create table consumables (
> cs_id serial not null unique,
> cs_make varchar(40),
> cs_code varchar(20),
> cs_type int4 references cons_types(cst_id) not null,
> cs_colour varchar(40),
> cs_comments text,
> primary key (cs_id)
> );
>
> insert into consumables (cs_make, cs_code, cs_type,cs_colour, cs_supp,
> cs_comments) values
> ('HP', 'C4096A', 1, 'BLACK', 5, '2100 2 0');
>
> create table cons_locations (
> cl_id serial not null unique,
> cl_desc varchar(40),
> primary key (cl_id)
> );
> insert into cons_locations (cl_desc) values ('Leeds Computer Room'); -- 1
>
> create table cons_status (
> css_id serial not null unique,
> css_descvarchar(40),
> primary key (css_id)
> );
> insert into cons_status (css_desc) values ('Ordered');-- 1
> insert into cons_status (css_desc) values ('In Stock'); -- 2
> insert into cons_status (css_desc) values ('Issued'); -- 3
>
> create table cons_stock (
> cost_id serial not null unique,
> cost_cs_id int4 references consumables(cs_id) not null,
> cost_css_id int4 references cons_status(css_id) not null,
> cost_cl_id int4 references cons_locations(cl_id) not null,
> cost_supp int4 references contacts(co_id),
> cost_comments text,
> primary key (cost_id)
> );
> -- insert 2 HP 2100 toners in stock at Leeds
> insert into cons_stock (cost_cs_id, cost_css_id, cost_cl_id) values
> (1, 2, 1);
> insert into cons_stock (cost_cs_id, cost_css_id, cost_cl_id) values
> (1, 2, 1);
>
> create v
Re: [SQL] Concatenate results of a single column query
hi, Christoph Haller wrote: Interesting feature, but I cannot find function array_append: ERROR: AggregateCreate: function array_append(integer[], integer) does not exist try with pg 7.4 C. ---(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
[SQL] Concatenate results of a single column query
Hi All, I would like to concatenate results of a single column query, something like the futurable (dev docs for 7.5) SELECT p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id) FROM people AS p Any suggestions? Thanks Marco ---(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] Querying From two databases
Hi, I am new to postgres and I need to do a query which joins two tables from different databases. Can you please advice me on how to achieve this in postgres. Thanks! Pallav ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] What is the best way of writing update rule on view with joined tables?
Please, how can I rewrite the rule below so that it works as intended for this update: update v set ad=0, bd=0 where ad=1; As it is now, this will change ad but not bd, presumably because when the rule's first action has updated ad, the "where ad=1" returns 0 rows for the second action. I want this because that is the way MS Access puts data into updates' where clauses and I want updateable forms on joined tables. create table a (k integer primary key, ad integer); create table b (k integer primary key, bd integer); create view v as select a.k, ad, bd from a join b on a.k=b.k; create rule r as on update to v do instead ( update a set ad=new.ad where k=old.k; update b set bd=new.bd where k=old.k; ); insert into a values(1,1); insert into a values(2,2); insert into b values(1,1); insert into b values(2,2); Thank you -- Cornelius ---(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
[SQL] ANN: www.SQL-Scripts.Com
Hello,Announcing the release of a new web site : www.SQL-Scripts.comAt www.SQL-Scripts.Com you can find a collection of SQL Scripts for manydifferent database system. Using our search system you can find scriptsthat you need quickly and simply. If you have scripts that you use why notlodge them on our site. If you lodge your script you could win a prize. Inaddition to scripts there are many documents, news feeds and articles.We have a monthly contest for the first people to lodge 20 scripts in onemonth.Please help to make this site great.Webmaster at www.SQL-Scripts.com
Re: [SQL] Function for numbering rows?
hi,
Tony Reina wrote, On 4/10/2004 18:12:
Simple question:
Is there a function that will allow me to number the returned tuples?
e.g.
CREATE TABLE test (
field1 text
):
INSERT INTO test VALUES ('first');
INSERT INTO test VALUES ('second');
INSERT INTO test VALUES ('third');
INSERT INTO test VALUES ('fourth');
SELECT number(), field1 FROM test;
1 'first'
2 'second'
3 'third'
4 'fourth'
where number() is the function I'm trying to find out about.
a simple way:
create temporary sequence st;
select nextval('st'),field1 from test;
drop sequence st;
C.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] relation X does not exist
Morning, I see lots of references to this error, but currently no fix for my situation. I am converting an INFORMIX ESQL/C system to PostgreSQL embedded C. I have hit several road blocks along the way and running out of time to complete and test this convert - so I am coming to the pros for some help. I have a table defined with only two fields. The create table statement did not have " " around the table name. I can cut and paste the query from my debug statement into psql and get the desired result set back. However, in the .pgc file, the query no matter how I write it (with " " , without, uppercase) I get the relation X does not exist. The embedded query is EXEC SQL select count(*) into :var1 from measurement_type where meas_type = :val2. Both var1 and var2 are within the EXEC SQL DECLARE section (host variables) and valid types. I can see the relation measurement_type within psql when I do \dt and as I stated, putting the exact query into psql gives me my count back. Any ideas? Thanks - Laura ---(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] Database triggers
Do you have access to the SQL Reference Manual: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/db2/rbafzmsthctri gger.htm#HDRHCTRIGGER There's an example of what you want to do: CREATE TRIGGER SAL_ADJ AFTER UPDATE OF SALARY ON EMPLOYEE REFERENCING OLD AS OLD_EMP NEW AS NEW_EMP FOR EACH ROW MODE DB2SQL WHEN (NEW_EMP.SALARY > (OLD_EMP.SALARY *1.20)) BEGIN ATOMIC SIGNAL SQLSTATE ?75001?(?Invalid Salary Increase - Exceeds 20%?); END Since you want a before trigger change "AFTER UPDATE" to "BEFORE UPDATE" Note: You can't have a single trigger that fires before INSERT, UPDATE, and DELETE. At least not when defined via SQL ;-) So you'll have to have 3 separate statements. HTH, Charles In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] says... > I have a lab assignment that I have been struggling with. We are > using oracle sql. Can someone please help me. See the lab below. I > have done ques 1 - 3 thus far and am now stuck on triggers ques 4 - 6. > > THIS IS THE LAB: > > 1. Create a table called QUOTE. > · Give the table an initial and next extent size of 8192 > · Specify a pctincrease of 0 > · Define the following columns using the datatypes and length listed > below. All columns should be mandatory except the COMMENTS column: > o ID NUMBER(4) > o QUOTE_DATE DATE > o SALES_REP_IDNUMBER(4) > o CUST_NBRNUMBER(5) > o PARTVARCHAR2(20) > o QUANTITYNUMBER(4) > o UNIT_COST NUMBER(8,2) > o STATUS CHAR(1) > o COMMENTSVARCHAR2(100) > · Define the ID column as the primary key for the table. You can do > this in the CREATE TABLE statement, or issue an ALTER TABLE statement > afterwards. > > 2. Alter the table above to add some foreign key constraints. Name > these constraints QUOTE_tablename_FK, where tablename is the table > referred to by the foreign key. > > For example, a foreign key on the QUOTE table referring to the PART > table should be called QUOTE_PART_FK. > > · A foreign key on the SALES_REP_ID column should refer to the > EMPLOYEE table. > · A foreign key on the CUST_NBR column should refer to the CUSTOMER > table. > · A foreign key on the PART column should refer to the PART table. > > 3. Create a composite index on the CUST_NBR, PART and QUOTE_DATE > columns. > · Give the index an initial and next extent of 8192 > · Use pctincrease 0 > · Name the index whatever you'd like > > 4. Create a database trigger on the QUOTE table that will fire before > an INSERT, UPDATE or DELETE operation. Name the trigger QUOTE_TRG. > The trigger should enforce the following rules: > > · If INSERTING or UPDATING > o QUOTE_DATE cannot be greater that SYSDATE (the current system date > and time) > o UNIT_COST can't be greater than the UNIT_COST for this part in the > PART table > o If QUANTITY is over 100, the UNIT_COST must be at least 20% less > than the UNIT_COST for this part as listed in the PART TABLE > · If INSERTING, in addition to the rules listed above: > o STATUS must contain a value of P (which stands for pending) > · If UPDATING, in addition to the rules listed earlier: > o A STATUS of P can only be changed to a STATUS of A (which stands for > active) > o A STATUS of A can be changed to P, W, L or C (for pending, won, lost > or cancelled) > o A STATUS of W, L or C can only be changed back to P > · If DELETING > o STATUS must be P or C > > If any of these rules are violated, raise one of the following > exceptions which you will define in the EXCEPTION portion of your > trigger. Raise an application error. Use whatever error numbers you'd > like, and provide meaningful text to describe the error: > > · Quote date can't be a future date > · Quoted price is too high > · New quotes must have a status of P > · Pending status (P) can only be changed to Approved (A) > · Invalid status code > · Won, Lost or Cancelled quotes can only be changed to Pending > > 5. Create a BEFORE UPDATE trigger on the PART table. The trigger > should enforce the following rule: > · If UNIT_COST is being updated > o The new price can't be lower than any of the quoted prices in the > QUOTE table for this part, if the quote status is P or A > o The new price must be at least 20% more than any quoted prices in > the QUOTE table for this part, if the quote is for a quantity > 100 > and the quote status is P or A > > Define a single exception that is raised when either error occurs. The > text of the application error should indicate that the cost is invalid > based upon outstanding quotes on the part. > > 6. Write a series of statements to test your new triggers: > · Try to insert a row into the quote table. For the quote date, > provide a value of SYSDATE+1. This will try to insert a row with > tomorrow's da
Re: [SQL] problem porting MySQL SQL to Postgres
hi, Dan Field wrote: I've stumbled across a query I don't quite understand the error message for. This query is pulled from a working MySQL setup: SELECT DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey WHERE (DEWEY_HUNDREDS = 9) AND (DEWEY_TENS >= 0) AND (DEWEY_TENS <= 9) AND (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND (DEWEY_TYPE = 't') AND (DEWEY_LANG = 'en') ORDER BY DEWEY_TENS However I'm getting the following error: ERROR: Unable to identify an operator '=' for types 'character' and 'boolean' You will have to retype this query using an explicit cast. use OR not ||. C. ---(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
Re: [SQL] SQL challenge--top 10 for each key value?
Troels Arvin wrote: See http://www.acm.org/sigmod/record/issues/0403/index.html#standards for an article which summarizes the news in SQL:2003. This is a very useful page; thank you for creating it and for noting it in this thread! -- (Posted from an account used as a SPAM dump. If you really want to get in touch with me, dump the 'jboes' and substitute 'mur'.) Jeffery Boes <>< [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Database triggers
I have a lab assignment that I have been struggling with. We are using oracle sql. Can someone please help me. See the lab below. I have done ques 1 - 3 thus far and am now stuck on triggers ques 4 - 6. THIS IS THE LAB: 1. Create a table called QUOTE. · Give the table an initial and next extent size of 8192 · Specify a pctincrease of 0 · Define the following columns using the datatypes and length listed below. All columns should be mandatory except the COMMENTS column: o ID NUMBER(4) o QUOTE_DATE DATE o SALES_REP_IDNUMBER(4) o CUST_NBRNUMBER(5) o PARTVARCHAR2(20) o QUANTITYNUMBER(4) o UNIT_COST NUMBER(8,2) o STATUS CHAR(1) o COMMENTSVARCHAR2(100) · Define the ID column as the primary key for the table. You can do this in the CREATE TABLE statement, or issue an ALTER TABLE statement afterwards. 2. Alter the table above to add some foreign key constraints. Name these constraints QUOTE_tablename_FK, where tablename is the table referred to by the foreign key. For example, a foreign key on the QUOTE table referring to the PART table should be called QUOTE_PART_FK. · A foreign key on the SALES_REP_ID column should refer to the EMPLOYEE table. · A foreign key on the CUST_NBR column should refer to the CUSTOMER table. · A foreign key on the PART column should refer to the PART table. 3. Create a composite index on the CUST_NBR, PART and QUOTE_DATE columns. · Give the index an initial and next extent of 8192 · Use pctincrease 0 · Name the index whatever you'd like 4. Create a database trigger on the QUOTE table that will fire before an INSERT, UPDATE or DELETE operation. Name the trigger QUOTE_TRG. The trigger should enforce the following rules: · If INSERTING or UPDATING o QUOTE_DATE cannot be greater that SYSDATE (the current system date and time) o UNIT_COST can't be greater than the UNIT_COST for this part in the PART table o If QUANTITY is over 100, the UNIT_COST must be at least 20% less than the UNIT_COST for this part as listed in the PART TABLE · If INSERTING, in addition to the rules listed above: o STATUS must contain a value of P (which stands for pending) · If UPDATING, in addition to the rules listed earlier: o A STATUS of P can only be changed to a STATUS of A (which stands for active) o A STATUS of A can be changed to P, W, L or C (for pending, won, lost or cancelled) o A STATUS of W, L or C can only be changed back to P · If DELETING o STATUS must be P or C If any of these rules are violated, raise one of the following exceptions which you will define in the EXCEPTION portion of your trigger. Raise an application error. Use whatever error numbers you'd like, and provide meaningful text to describe the error: · Quote date can't be a future date · Quoted price is too high · New quotes must have a status of P · Pending status (P) can only be changed to Approved (A) · Invalid status code · Won, Lost or Cancelled quotes can only be changed to Pending 5. Create a BEFORE UPDATE trigger on the PART table. The trigger should enforce the following rule: · If UNIT_COST is being updated o The new price can't be lower than any of the quoted prices in the QUOTE table for this part, if the quote status is P or A o The new price must be at least 20% more than any quoted prices in the QUOTE table for this part, if the quote is for a quantity > 100 and the quote status is P or A Define a single exception that is raised when either error occurs. The text of the application error should indicate that the cost is invalid based upon outstanding quotes on the part. 6. Write a series of statements to test your new triggers: · Try to insert a row into the quote table. For the quote date, provide a value of SYSDATE+1. This will try to insert a row with tomorrow's date for the quote date. · Try to insert a row into the quote table with a price greater than that listed for the part in the PART table · Try to insert a row into the quote table with a quantity > 100 and a price > 20% off the price in the PART table · Try to INSERT a row with a STATUS other than P · Now insert a valid row so that you can test some UPDATE statements · Issue an UPDATE to modify the price to a price higher than that in the PART table · Issue an UPDATE to modify the quote date to SYSDATE+1 · Issue an UPDATE to modify the quantity to > 100 and the price to something higher than 20% off the price listed in the PART table · Issue an update to modify the status from P to W · Now issue a valid update to change the status to A · Issue a delete to make sure you can't delete a row with status of A · Fina
Re: [SQL] three-way join
On Mon, Apr 19, 2004 at 13:47:28 +0100, Gary Stainburn <[EMAIL PROTECTED]> wrote: > Hi folks, > > here's a straight forward join that I simply can't get my head round. > > I've got > > consumables: cs_id, cs_make, cs_comments > cons_locations: cl_id, cl_desc > cons_stock: cs_id, cl_id, status (1=ordered, 2=in-stock) > > (one stock record per stock item, qty=3 means 3 records) > > I'm struggling to create a quiery to produce > > cs_id, cs_make, cs_comments, cl_desc, hand_qty, order_qty > > where hand_qty and order_qty is the number of records grouped by cs_id, cl_id, > and status. I think you can do something like: SELECT a.cs_id, a.cs_make, a.cs_comments, b.cl_desc, c.hand_qty, d.order_qty FROM consumables a LEFT JOIN cons_locations b ON (a.cs_id = b.cs_id) LEFT JOIN (SELECT cs_id, cl_id, count(*) AS hand_qty FROM cons_stock GROUP BY cs_id, cl_id WHERE status = 2) c ON (a.cs_id = c.cs_id and a.cl_id = c.cl_id) LEFT JOIN (SELECT cs_id, cl_id, count(*) AS order_qty FROM cons_stock GROUP BY cs_id, cl_id WHERE status = 1) d ON (a.cs_id = d.cs_id and a.cl_id = d.cl_id) ; or like: SELECT a.cs_id, a.cs_make, a.cs_comments, b.cl_desc, count(case c.status = 2 THEN 1 END) AS hand_qty, count(case c.status = 1 THEN 1 END) AS order_qty FROM consumables a LEFT JOIN cons_locations b ON (a.cs_id = b.cs_id) LEFT JOIN cons_stock c ON (a.cs_id = c.cs_id and a.cl_id = c.cl_id) GROUP BY a.cs_id, a.cs_make, a.cs_comments, b.cl_desc ; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Database triggers
Thank you, will look at the reference manual. This is how I've done it to this point. It runs and the trigger is created but am not quite sure if its the right thing. CREATE OR REPLACE TRIGGER QUOTE_TRG BEFORE INSERT or DELETE OR UPDATE ON QUOTE FOR EACH ROW DECLARE today_date date; part_cost number(8, 2); current_status char(1); future_date exception; high_cost exception; discount_error exception; invalid_insert exception; invalid_status exception; delete_status exception; BEGIN if inserting or updating then today_date := :new.QUOTE_DATE; if today_date > SYSDATE then raise future_date; end if; select PART.UNIT_COST into part_cost from PART where PART.PART_NBR = :NEW.PART; if part_cost < :NEW.UNIT_COST then raise high_cost; end if; if :NEW.QUANTITY > 100 then if (part_cost * .8) < :NEW.UNIT_COST then raise discount_error; end if; end if; end if; if inserting then if upper(:NEW.STATUS) != 'P' then raise invalid_insert; end if; end if; if updating then if upper(:NEW.STATUS) != 'A' then raise invalid_status; end if; end if; if deleting then select QUOTE.STATUS into current_status from QUOTE where QUOTE.ID = :NEW.ID; if current_status != 'P' and current_status != 'C' then raise delete_status; end if; end if; EXCEPTION when future_date then raise_application_error(-20110, 'Quote date cannot be a future date.'); when high_cost then raise_application_error(-20111, 'Quoted price is too high'); when discount_error then raise_application_error(-20112, 'Quoted discount price is too high'); when invalid_insert then raise_application_error(-20113, 'New quotes must have a status of P'); when invalid_status then raise_application_error(-20114, 'Pending status (P) con only be changed to Approved (A)'); when delete_status then raise_application_error(-20115, 'Status must be (P) Pending or (C) Cancelled to be deleted'); END; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Update is very slow on a bigger table
hi, Dimitar Georgievski wrote, On 4/15/2004 23:07: hi, i'm running an update procedure on a table with approximately 100,000 records. The problem is the procedure is running endlessly even when the update is very simple like in the following example: update mytable set client_id = 1 where mytable_id = 1 mytable_id is the primary key of mytable. the process under which the update is running reports among the usual process data "UPDATE waiting". I'm using PostgreSQL 7.3.4 on SuSE 9.0. From what I have seen it doesn't look like that the host computer has problem with the resources. Any idea why is this happening? could be, after you send some info: your table schema, your explain analyze result, triggers, or any other info. Any other transaction running again mytable while you try to update? C. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Querying From two databases
Pallav Kalva wrote: > Hi, > > I am new to postgres and I need to do a query which joins two tables > from different databases. Can you please advice me on how to achieve > this in postgres. Sure, see /contrib/dblink in the source distribution. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] SQL script
good day toall.. imm running postgreSQL on linux. my main goal is to create a script that will dump a data from our server too my local PC. the content of the script would be execute sql command in one shot: 1. dump data 2. update and insert some rows to selected table. how can i accommplish this task and also how can i run this script? is there any one ccan help me. any idea will appreciate very much. thanks. -- _ Web-based SMS services available at http://www.operamail.com. >From your mailbox to local or overseas cell phones. Powered by Outblaze ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL script
On Thu, 15 Apr 2004 [EMAIL PROTECTED] wrote: > good day toall.. > imm running postgreSQL on linux. > my main goal is to create a script that will dump a data from our server too my > local PC. the content of the script would be execute sql command in one shot: > 1. dump data > 2. update and insert some rows to selected table. > > how can i accommplish this task and also how can i run this script? is there any one > ccan help me. any idea will appreciate very much. Just FYI, this might get better response on the admin mailing list... So, what is your local PC running? Linux, BSD, Solaris, Windows? If NOT windows, then just install a copy of postgresql on it real quick to get access to the pg_dump utilities et. al. and use that to remotely dump the data out of the server onto your local machine. Are you updating / inserting back into the big server, or locally? either way, the easiest way I've found to do it is to use a scripting language I'm familiar with (perl, PHP, python, tcl, etc...) and massage the data in whatever ways you need to and then insert it into the database with that scripting languages pg interface package. Other ways of doing it would be to get it into a SQL dump format and use psql to insert it. ---(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] Prepared Statements and large where-id-in constant blocks?
James Robinson <[EMAIL PROTECTED]> writes: > where there may be anywhere between 1 and thousands of "(id = N)" > blocks ORed together. These may be transformed to the "WHERE t1.id IN > (X, Y, ...)" form for possibly a little performance gain (possibly -- > I've not yet checked to see if this plans better than the other, but I > could imagine this form being parsed into the hashjoin form as opposed > to a huge index filter form). There is *no difference whatever*; in fact the PG parser expands an IN clause into an OR'd list. Possibly this is something to improve someday, but there's surely no percentage in doing lots of work in the JDBC driver to prefer the IN form at the moment. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] relation X does not exist
Laura Scott <[EMAIL PROTECTED]> writes: > I have a table defined with only two fields. The create table statement > did not have " " around the table name. I can cut and paste the query > from my debug statement into psql and get the desired result set back. > However, in the .pgc file, the query no matter how I write it (with " " > , without, uppercase) I get the relation X does not exist. Are you sure your ecpg program is connecting to the same database as you're using manually? Perhaps it's connecting as a different userid that has a different search path? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
