Re: [SQL] Need help in composing PostgreSQL query
"Vladimir V. Zolotych" wrote:
>Hi
>
>Please help me compose the query in PostgreSQL.
>Using PostgreSQL 7.1.2.
>
>Suppose relations A and B have columns:
> {X1, X2, ..., Xm, Y1, Y2, ..., Yn}
>and
> {Y1, Y2, ..., Yn}
>Attributes Y1, Y2, ..., Yn are common for both relations
>and have the same type in both.
>
>How can I define in PostgreSQL the query producing
>relation with columns X1,X2,...,Xm containing all those tuples
>satisfying conditon: relation A contains tupple
> {x1,x2,...xm,y1,y2,...,yn}
>for _each_ tupple
> {y1,y2,...,yn}
>in relation B ? Where x1 denotes particular value of
>colum X1 etc.
You seem to be talking about a natural join:
SELECT *
FROM a,b
WHERE a.y1 = b.y1 AND a.y2 = b.y2 AND ... AND a.yn = b.yn;
>For example: consider two tables DEND and DOR.
>
>DEND DOR
>
> s | p p
>+
> s1 | p1 p1
> s1 | p2 p2
> s1 | p3 p3
> s1 | p4 p4
> s1 | p5 p5
> s1 | p6 p5
> s2 | p1 (6 rows)
> s2 | p2
> s3 | p2
> s4 | p2
> s4 | p4
> s4 | p5
>(12 rows)
>
>For such tables our desired query should return:
>
> s
>
> s1
SELECT DOR.s
FROM DEND,DOR
WHERE DOR.p = DEND.p;
--
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
"If any of you lack wisdom, let him ask of God, who
gives to all men generously and without reproach, and
it will be given to him." James 1:5
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] GRANT ALL ON TO GROUP failure
You need to user is a reserved word so you need to quote it (ie: "user" rather than user) whenever you reference it.Does group reader exist? If it does then it might be a case issue as presumably you must have quoted user user to create it which also preserves case, so if you did the same for group reader and included any caps reader will need to be quoted and in the same case. - Stuart > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Monday, September 03, 2001 5:35 PM > To: [EMAIL PROTECTED] > Subject: GRANT ALL ON TO GROUP failure > > > Can anyone suggest, what is wrong with the following > sql file? SOmehow the semicolon causes error? > > Jari > > class=# \i pg-def-group-grant.sql > Granting ALL to ROOT > CHANGE > Granting ALL to USER > psql:pg-def-group-grant.sql:48: ERROR: parser: parse error at or near > "user" > Granting SELECT to READER > psql:pg-def-group-grant.sql:69: ERROR: non-existent group "reader" > > 01:-- Postgres create table rights > 02:-- $Id: pg-def-group-grant.sql,v 1.1 2001/08/23 19:26:16 jaalto Exp $ > 03:-- > 04:-- > 05:-- GRANT allows the creator of an object to give specific permissions > to > 06:-- all users (PUBLIC) or to a certain user or group. Users other than > the > 07:-- creator don't have any access permission unless the creator GRANTs > 08:-- permissions, after the object is created. > 09: > 10:\echo Granting ALL to ROOT > 11: > 12:GRANT ALL ON > 13: bonus > 14: , custid > 15: , customer > 16: , dept > 17: , dual > 18: , dummy > 19: , emp > 20: , item > 21: , ordid > 22: , ordx > 23: , price > 24: , prodid > 25: , product > 26: , sales > 27: , salgrade > 28: TO GROUP root; > 29: > 30:\echo Granting ALL to USER > 31: > 32:GRANT ALL ON > 33: bonus > 34: , custid > 35: , customer > 36: , dept > 37: , dual > 38: , dummy > 39: , emp > 40: , item > 41: , ordid > 42: , ordx > 43: , price > 44: , prodid > 45: , product > 46: , sales > 47: , salgrade > 48: TO GROUP user; > 49: > 50: > 51:\echo Granting SELECT to READER > 52: > 53:GRANT SELECT ON > 54: bonus > 55: , custid > 56: , customer > 57: , dept > 58: , dual > 59: , dummy > 60: , emp > 61: , item > 62: , ordid > 63: , ordx > 64: , price > 65: , prodid > 66: , product > 67: , sales > 68: , salgrade > 69: TO GROUP reader; > 70: > 71:-- End of file > > -- > > > -- > http://tiny-tools.sourceforge.net/ > Swatch @time http://www.ryanthiessen.com/swatch/resources.htm > Convert @time http://www.mir.com.my/iTime/itime.htm > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] 2 tables, joins and same name...
I learned SQL from Sam's "Teach Yourself SQL in 21 Days", and am happy to recommend it. Which book is "best" is very subjective and situation dependent; all I can say is that this one did the job for me. Pros: clearly written; knowledgable authors; good coverage Cons: all due to space limitations. Many advanced features are touched on, without any really useful explanation of how to use them or even why they exist. Can be a good starting point though (The pl/pgsql docs made no sense at all to me, until I had read the Oracle pl/sql coverage in this book). - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "Ross J. Reedstrom" <[EMAIL PROTECTED]>; "Josh Berkus" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, September 01, 2001 11:44 AM Subject: Re: 2 tables, joins and same name... > Ross, > > > Have you seen "Database Design for Mere Mortals" by Michael > > Hernandez? > > Yeah, that's one I've been loaning out a lot. However, while it does > cover a lot of good stuff about how to design a database, it never gets > past the most elementary SQL ... really, no further than Bruce gets. > And if I recommend Hernandez together with "SQL for Smarties", well, > that's over 600 pages combined ... > > What I'd really love to see, I guess, would be a 200 page "Elements of > SQL" book organized into "lessons" for the beginner. Maybe with an > additional 75 pages of Q&A examples at the back. > > Maybe I should write one. > > > And there's another one, that I can only remember as 'the pink book', > > I can never remember the title! that struck me as a reasonably good > > intro to intermediate level book. > > Oh, that'll make me friends at Stacy's Bookstore. "I'm not sure of the > title, and I don't know the author or publisher, but it's about > databases and it's pink." ;-P > > -Josh > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [HACKERS] [GENERAL] getting the oid for a new tuple in a BEFORE
> > we need to control database changes within BEFORE triggers. > > There is no problem with triggers called by update, but there is > > a problem with triggers called by insert. > > > > We strongly need to know the oid of a newly inserted tuple. > > In this case, we use tg_newtuple of the TriggerData structure > > passed to thetrigger function, and its t_data -> t_oid will > > have the value '0'. > > > > Using BEFORE and AFTER triggers would make our lives much harder. > > > > Is there any way (even hack) to get the oid the newly > > inserted tuple will receive? > > Just set t_data->t_oid = newoid() - this is what backend does > in heapam.c:heap_insert(). Does that work? Doesn't that get overwritten when the actual INSERT happens? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
