[SQL] Can I get this all in one query?

[Richard Rowell]

I'm designing a database/website that will allow students to "grade" the 
professors/classes the students attend.
There are eight different "factors" that the students assign grades on.  Until tonight 
I had one table that kept the
scores assigned by students.  This table name REVIEW had a field named for each 
factor.  IE:

CREATE TABLE review(
class_uid INTEGER   REFERENCES class,
student   VARCHAR(20)   REFERENCES student,
snooze   INTEGER, 
professional INTEGER,
personality   INTEGER,
gradingINTEGER,
effectivenessINTEGER,
knowledge   INTEGER,
accentINTEGER,
enthusiasismINTEGER,

uid SERIAL  PRIMARY KEY,
UNIQUE(class_uid,student)
)

THis allowed me to write simple queries that would pull averages for a 
college,department, a course, a professor,
etc.  Example query:

//Returns averages in all 8 factors of every department
SELECT crs.dept AS department,
   ROUND(AVG(CAST(rvw.snoozeAS FLOAT)) ,2) AS snooze,
   ROUND(AVG(CAST(rvw.professional  AS FLOAT)) ,2) AS professional,
   ROUND(AVG(CAST(rvw.personality   AS FLOAT)) ,2) AS personality,
   ROUND(AVG(CAST(rvw.grading   AS FLOAT)) ,2) AS grading,
   ROUND(AVG(CAST(rvw.effectiveness AS FLOAT)) ,2) AS effectiveness,
   ROUND(AVG(CAST(rvw.knowledge AS FLOAT)) ,2) AS knowledge,
   ROUND(AVG(CAST(rvw.accentAS FLOAT)) ,2) AS accent,
   ROUND(AVG(CAST(rvw.enthusiasism  AS FLOAT)) ,2) AS enthusiasism
  FROM review rvw, class cls, course crs
  WHERE rvw.class_uid=cls.uid AND cls.course_uid=crs.uid
  GROUP BY crs.dept;

However, in a developer meating tonight it was decided that the factors (which are in 
another table in the database)
should not be "hard coded" into the review table, but rather a new table should be 
created implementin the many to
many relationship.  The revised review table(s) looks like so:

CREATE TABLE review
(
  class_uid INTEGER   REFERENCES class,
  student   VARCHAR(30)   REFERENCES student,
  comments  TEXT,

  uid   SERIALPRIMARY KEY,
  UNIQUE(class_uid,student)
);

CREATE TABLE review_scores
(
  review_uidINTEGER   REFERENCES review,
  factorVARCHAR(30)   REFERENCES factor,
  score INTEGER,

  uid   SERIALPRIMARY KEY,
  UNIQUE(review_uid,factor)
);

My problem is now I do not know how to write a single query that can pull the average 
of all 8 factors at once,
grouped my department as above.  If it is a specific department it is trivial, but for 
all departments at once I would
need to use two group by statements in the same SQL query.

I'm sure there is a simple solution, possibly using sub-queries which I'm not very 
farmiliar with.  I am attaching the
database schema below in case it would make things clearer.

TIA!

CREATE TABLE term
(
  name  VARCHAR(30)   PRIMARY KEY
);

CREATE TABLE semester
(
  term  VARCHAR(30)   REFERENCES term ON UPDATE 
CASCADE,
  year  INTEGER,
  lockedBOOLEAN   DEFAULT TRUE,

  uid   SERIALPRIMARY KEY,
  UNIQUE(term,year)
);

CREATE TABLE college
(
  name  VARCHAR(30)   PRIMARY KEY
);

CREATE TABLE department
(
  department_id VARCHAR(4)PRIMARY KEY,
  name  VARCHAR(30)   NOT NULL,
  college   VARCHAR(30)   REFERENCES college   ON UPDATE 
CASCADE
);

CREATE TABLE degree
(
  name  VARCHAR(30)   PRIMARY KEY
);

CREATE TABLE professor
(
  first_nameVARCHAR(30)   NOT NULL,
  last_name VARCHAR(30)   NOT NULL,
  email_address VARCHAR(30),
  degree_type   VARCHAR(30)   REFERENCES degree,
  undergrad_univVARCHAR(50),
  grad_univ VARCHAR(50),
  major VARCHAR(20),
  comment   TEXT,

  uid   SERIALPRIMARY KEY
);

CREATE TABLE professor_department_link
(
  prof_uid  INTEGER   REFERENCES professor,
  dept  VARCHAR(4)REFERENCES department,
  UNIQUE(prof_uid,dept)
);

CREATE TABLE email_domain
(
  domainVARCHAR(20)   PRIMARY KEY
);

CREATE TABLE student
(
  passwdVARCHAR(30)   NOT NULL,
  email_prefix  VARCHAR(30)   NOT NULL,
  email_domain  VARCHAR(20)   REFERENCES email_domain ON UPDATE 
CASCADE,
  authenticated BOOLEAN   DEFAULT FALSE,

  screen_name   VARCHAR(20)   PRIMARY KEY,
  UNIQUE(email_prefix,email_domain)
);

CREATE TABLE course
(
  deptVARCHAR(4)REFERENCES departmentON UPDATE CASCADE,
  cours

[SQL] Re: Auto increment

[Richard Rowell]

In article , "Mads Jensen"
<[EMAIL PROTECTED]> wrote:
> Hi
> 
> I'm a newbiw with pgsql:
> 
> 1: Haven't been able to find the officiel manual to pgsql. What's the
> complete URL?

Hmm, I don't know, I use the book which you can find with all the rest of the docs at:
http://www.postgresql.org/docs/index.html


> 2: How can I make an auto increment with PostGreSQL? is it "inherit"?

One of two ways.  Either make the data type "serial" or you can create a sequence and 
set the default value of the field to curval(sequence).

[SQL] Outer Join Syntax

[Richard Rowell]

I'm doing a feasability study on porting our flagship product to Postgres
(from MS_SQL).  I have run across a few snags, the largest of which is the
outer join syntax.  MS has some nice syntactical sugar with the *=/=*
operators that Postgres dosen't seem to support.  I am confused on how
to replicate the behavior however. We often link together many tables via
outer joins to form a view such as:
SELECT Assessment_medical_id, a.Readonly_agency, a.Date_added, ag.name as
'AgencyName',
   y1.Yesno_descrip as 'healthprob', y2.Yesno_descrip as
'MentalIllness', y3.Yesno_descrip as 'MentalTreatment',
   y4.Yesno_descrip as 'drugabuse', d1.Drug_abuse_type_descrip
as 'drug1', d2.Drug_abuse_type_descrip as 'drug2',
   d3.Drug_abuse_type_descrip as 'drug3',
d4.Drug_abuse_type_descrip as 'drug4', d5.Drug_abuse_type_descrip as
'drug5'
   FROM ASSESSMENT_MEDICAL a, AGENCIES ag, YESNO_TYPES02 y1,
YESNO_TYPES02 y2, YESNO_TYPES02 y3, YESNO_TYPES02 y4,
   DRUG_ABUSE_TYPES d1, DRUG_ABUSE_TYPES d2, DRUG_ABUSE_TYPES
d3, DRUG_ABUSE_TYPES d4, DRUG_ABUSE_TYPES d5
   WHERE a.inactive != 'Y'
   AND a.Client_id = $Edit_Client_id
   AND a.Agency_creating *= ag.Agency_id
   AND a.Health_prob *= y1.Yesno_code
   AND a.EmoMental_illness *= y2.Yesno_code
   AND a.Treatment_for_emomental *= y3.Yesno_code
   AND a.AlchoholDrug_abuse *= y4.Yesno_code
   AND a.AlchoholDrug_abuse_type1 *= d1.Drug_abuse_type_id
   AND a.AlchoholDrug_abuse_type2 *= d2.Drug_abuse_type_id
   AND a.AlchoholDrug_abuse_type3 *= d3.Drug_abuse_type_id
   AND a.AlchoholDrug_abuse_type4 *= d4.Drug_abuse_type_id
   AND a.AlchoholDrug_abuse_type5 *= d5.Drug_abuse_type_id


I'm just not grasping how one would accomplish the same using the SQL-92
syntax.

TIA





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

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

[SQL] to_date() confusion

[Richard Rowell]

I'm confused.  How do I massage the integer 10102 into the date
2002-01-01?

cmi=> select to_date('010102','MMDDYY');
  to_date   

 2002-01-01
(1 row)

cmi=> select to_char(10102,'00');
 to_char 
-
  010102
(1 row)

cmi=> select to_date(to_char(10102,'00'),'MMDDYY');
  to_date   

 2010-01-10
(1 row)

TIA!

-- 
Richard Rowell <[EMAIL PROTECTED]>


---(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] to_date confusion

[Richard Rowell]

I'm confused.  How do I get the integer 10102 to come in as the date
2002-01-01?

cmi=> select to_date('010102','MMDDYY');
  to_date   

 2002-01-01
(1 row)

cmi=> select to_char(10102,'00');
 to_char 
-
  010102
(1 row)

cmi=> select to_date(to_char(10102,'00'),'MMDDYY');
  to_date   

 2010-01-10
(1 row)

TIA!


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

[SQL] Database Upgrade scripts (AKA Conditional SQL execution)

[Richard Rowell]

Moving to Postgres from MS SQL server wherever I can.  When writing
database upgrade scripts, it is nice to construct the script so it will
run correctly even if run twice.  In MS-SQL's TSQL I would do something
like this:

IF( SELECT COUNT(*) FROM sysobjects WHERE name = 'foo' AND type ='U' ) <
1 THEN
CREATE TABLE foo()
END

Can I accomplish this with postgresql without involving an external
process (like say perl)?  I guess I could put the upgrade stuff into
PL/SQL functions and just drop the functions when I'm done, but I was
hoping for something a little "cleaner".

-- 
Richard Rowell <[EMAIL PROTECTED]>


signature.asc
Description: This is a digitally signed message part

[SQL] Recursive SETOF function

[Richard Rowell]

I'm trying to port some TSQL to PLPGSQL.  The DB has a table with a
recursive foreign key that represents a tree hierarchy.  I'm trying to
re-create a TSQL function that pulls out all the ancestors of a given
node in the hierarchy.

I'm rather new to PLSQL and I have several questions.

1.  In TSQL, I can assign a scalar to the result of query like so:
  SET @var1 = (SELECT foo FROM bar WHERE [EMAIL PROTECTED])

How would I do this in PLSQL?

2.  In TSQL the "result table" can be inserted into manually.  IE:

CREATE FUNCTION foo () RETURNS @ttable TABLE( uid INTEGER) AS BEGIN 
INSERT @ttable VALUES (1)
RETURN
END

Is there a way to manually insert rows into the result table in PLSQL?


What follows is my TSQL function if that helps give context.  

CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER)
RETURNS @provider_ids TABLE ( uid INTEGER )
AS
BEGIN
DECLARE @cid AS INTEGER
IF (SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0
BEGIN
SET @cid = @child_provider
WHILE @cid IS NOT NULL
BEGIN
INSERT @provider_ids VALUES (@cid)
SET @cid = (SELECT parent_id FROM providers WHERE [EMAIL PROTECTED])
END
END
RETURN
END

-- 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Recursive SETOF function

[Richard Rowell]

I have been fiddling with what you sent.  I have it working mostly, save
for I keep getting syntax errors on the "RETURN NEXT cid;" line.  If I
remove this line then the function works ( but returns nothing of
course).  Any ideas on why the RETURN NEXT doesn't like the variable as
a parameter?

sp_demo_505=# CREATE OR REPLACE FUNCTION svp_getparentproviderids
(INTEGER)
   RETURNS SETOF svp_getparentproviderids_uid_type
   AS '
DECLARE
  child_provider ALIAS FOR $1;
  cid INTEGER;
BEGIN
SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
IF cid = 0 THEN
RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
RETURN;
END IF;
cid := child_provider;
LOOP
EXIT WHEN cid IS NULL;
RETURN NEXT cid;
SELECT INTO cid parent_id FROM providers WHERE uid=cid;
END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';
CREATE FUNCTION
sp_demo_505=# select * from svp_getparentproviderids(21112);
ERROR:  incorrect argument to RETURN NEXT at or near "cid"
CONTEXT:  compile of PL/pgSQL function "svp_getparentproviderids" near
line 13



---(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: [postgres] Re: [SQL] Recursive SETOF function

[Richard Rowell]

I had to fiddle a bit more, but I did finally get it to work.  Thanks
Mike

CREATE OR REPLACE FUNCTION svp_getparentproviderids (INTEGER)
  RETURNS SETOF INTEGER
  AS '
DECLARE
 child_provider ALIAS FOR $1;
 cid INTEGER;
BEGIN
   SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
   IF cid = 0 THEN
   RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
   RETURN;
   END IF;
   cid := child_provider;
   LOOP
   EXIT WHEN cid IS NULL;
   RAISE WARNING ''LOOP: Adding (%) to results'', cid;
   RETURN NEXT cid;
   SELECT INTO cid parent_id FROM providers WHERE uid=cid;
   END LOOP;
   RETURN;
END;' LANGUAGE 'plpgsql';


On Mon, 2004-11-22 at 14:39 -0500, Mike Rylander wrote:
> Sorry about that... try this:
> 
> CREATE OR REPLACE FUNCTION svp_getparentproviderids
> (INTEGER)
>   RETURNS SETOF svp_getparentproviderids_uid_type
>   AS '
> DECLARE
>  child_provider ALIAS FOR $1;
>  cid svp_getparentproviderids_uid_type%ROWTYPE;
>  tmp_cid INTEGER;
> BEGIN
>SELECT INTO tmp_cid count(*) FROM providers WHERE uid =child_provider;
>IF tmp_cid = 0 THEN
>RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
>RETURN;
>END IF;
>cid.uid := child_provider;
>LOOP
>EXIT WHEN tmp_cid IS NULL;
>RETURN NEXT cid;
>SELECT INTO tmp_cid parent_id FROM providers WHERE uid=cid.uid;
>END LOOP;
>RETURN;
> END;' LANGUAGE 'plpgsql';
> 
> On Mon, 22 Nov 2004 12:51:41 -0600, Richard Rowell
> <[EMAIL PROTECTED]> wrote:
> > I have been fiddling with what you sent.  I have it working mostly, save
> > for I keep getting syntax errors on the "RETURN NEXT cid;" line.  If I
> > remove this line then the function works ( but returns nothing of
> > course).  Any ideas?
> > 
> > sp_demo_505=# CREATE OR REPLACE FUNCTION svp_getparentproviderids
> > (INTEGER)
> >RETURNS SETOF svp_getparentproviderids_uid_type
> >AS '
> > DECLARE
> >   child_provider ALIAS FOR $1;
> >   cid INTEGER;
> > BEGIN
> > SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
> > IF cid = 0 THEN
> > RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
> > RETURN;
> > END IF;
> > cid := child_provider;
> > LOOP
> > EXIT WHEN cid IS NULL;
> > RETURN NEXT cid;
> > SELECT INTO cid parent_id FROM providers WHERE uid=cid;
> > END LOOP;
> > RETURN;
> > END;' LANGUAGE 'plpgsql';
> > CREATE FUNCTION
> > sp_demo_505=# select * from svp_getparentproviderids(21112);
> > ERROR:  incorrect argument to RETURN NEXT at or near "cid"
> > CONTEXT:  compile of PL/pgSQL function "svp_getparentproviderids" near
> > line 13
> > 
> > 
> 
> 
-- 


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

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Breadth first traversal in PLSQL (How to implement Queue?)

[Richard Rowell]

I have a table with a unary (recursive) relationship that represents a
hierarchy.  With the gracious help of Mike Rylander I was able to port a
TSQL function that would traverse "up" the hierarchy to PL/SQL.  Now I
need help porting the "down" the hierarchy function.  

As implemented in TSQL I utilized a simple breadth first tree traversal.
I'm not sure how to replicate this in PL/SQL as I haven't figured out
how to implement the queue required for the breadth first algorithm.  My
queue is declared "queue SETOF INTEGER" and I'm able to "SELECT INTO"
this variable.  However when I try to delete the "current" value, I get
a syntax error.  If I comment the delete out, I also get an error when I
try to fetch the "next" value from the front of the queue.

Below is the function, followed by the psql output:

CREATE OR REPLACE FUNCTION svp_getchildproviderids (INTEGER)
  RETURNS SETOF INTEGER
  AS '
DECLARE
 parent_provider ALIAS FOR $1;
 cid INTEGER;
 queue SETOF INTEGER;
BEGIN
   SELECT INTO cid count(*) FROM providers WHERE uid =parent_provider;
   IF cid = 0 THEN
   RAISE EXCEPTION ''Inexistent ID --> %'', parent_provider;
   RETURN;
   END IF;
   cid := parent_provider;
   LOOP
   EXIT WHEN cid IS NULL;
   RETURN NEXT cid;
   SELECT INTO queue uid FROM providers WHERE parent_id = cid;
   DELETE FROM queue WHERE queue.queue = cid;
   SELECT INTO cid * FROM queue LIMIT 1;
   END LOOP;
   RETURN;
END;' LANGUAGE 'plpgsql';

sp_demo_505=# select * from svp_getchildproviderids(1);
ERROR:  syntax error at or near "$1" at character 14
CONTEXT:  PL/pgSQL function "svp_getchildproviderids" line 16 at SQL
statement


-- 


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

   http://archives.postgresql.org