Re: [SQL] " Adding missing FROM-clause entry for table .... " problem.
Hmmm i forgot to follow up. Thanks for pointing out the relevent Docs. Regds Mallah. On Tuesday 18 February 2003 04:04 pm, Christoph Haller wrote: > > We find that if we alias a tablename and refer to that tablename in > > where cluase instead of reffering > > > to the alias it produces wrond results. > > > > EG: > > select to_char(a.generated, 'DD/Mon/' ) ,userid,email,descr from > > membership_invoice a join payment_classes using( > payment_class) > > > join users using(userid) join membership_status using(userid) where > > membership_invoice.status='a' and granted is fa > lse and > > > membership_invoice.last_reminder is null and current_date - > > date(a.generated) > 4 limit 10 ; > > > NOTICE: Adding missing FROM-clause entry for table > > "membership_invoice" > > > Where as merely rewriting the quer to use defined aliases gives the > > correct results. > > > select to_char(a.generated, 'DD/Mon/' ) ,userid,email,descr from > > membership_invoice a join payment_classes > > > using(payment_class) join users using(userid) join membership_status > > using(userid) where a.status='a' and granted i > s > > > false and a.last_reminder is null and current_date - date(a.generated) > > 4 ; > > > > Can Anyone please explain if its a BUG or problem in my understanding > > I think it's a problem in understanding. The documentation (7.2.1) > states (as the NOTICE: does) > > 2.2.1.3. Table and Column Aliases > > A temporary name can be given to tables and complex table references to > be used for references to the derived table in further > processing. This is called a table alias. > > FROM table_reference AS alias > > Here, alias can be any regular identifier. The alias becomes the new > name of the table reference for the current query -- it is no > longer possible to refer to the table by the original name. Thus > > SELECT * FROM my_table AS m WHERE my_table.a > 5; > > is not valid SQL syntax. What will actually happen (this is a PostgreSQL > extension to the standard) is that an implicit table > reference is added to the FROM clause, so the query is processed as if > it were written as > > SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5; > > Regards, Christoph > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] 7.3 "group by" issue
Hi folks, This query: SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id AND watch_list.user_id = 1 GROUP BY watch_list_element.element_id gives this error: ERROR: Attribute unnamed_join.element_id must be GROUPed or used in an aggregate function Note that in the select the table name is not mentioned but it is in the GROUP BY. To solve the problem, you either have to name the table in both locations or not name it in either location. Why? -- Dan Langille : http://www.langille.org/ ---(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] 7.3 "group by" issue
Dan, Chad, > I see the distinction you are making. > > Maybe Tom or Josh could throw out a better answer, but I think that youve > called it one thing in your select and tried to group by it using a > syntaticly different name. This looks like a bug to me. Please write it up and send it to BUGS. 7.3.2, I assume? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 7.3 "group by" issue
On Fri, 21 Feb 2003, Josh Berkus wrote: > Dan, Chad, > > > I see the distinction you are making. > > > > Maybe Tom or Josh could throw out a better answer, but I think that youve > > called it one thing in your select and tried to group by it using a > > syntaticly different name. > > This looks like a bug to me. Please write it up and send it to BUGS. Will do. > 7.3.2, I assume? Yes. FWIW, I'm upgrading FreshPorts.org from 7.2.3. ---(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] function defination help ..
Rajesh Kumar Mallah writes: > is it possible to get the function creation defination as produced by pg_dump > by some SQL queries on system catalogs? > > pg_func stores procsrc but i am trying to get RETURNS and the arg part also. You will need to reconstruct what pg_dump does. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [BUGS] 7.3 GROUP BY differs from 7.2
Dan Langille <[EMAIL PROTECTED]> writes: > This is the query in question: > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element >ON watch_list.id = watch_list_element.watch_list_id > WHERE watch_list.user_id = 1 > GROUP BY watch_list_element.element_id; > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in an > aggregate function The parser currently considers an output column of a JOIN to be a different variable from the corresponding column of the input table. Thus the above error message. While the distinction is without content in this example, it is extremely real in some nearby cases --- in particular, in NATURAL or USING full outer joins it's possible for one to be null when the other isn't. (And no, I don't think 7.2 got this right.) I'm having a hard time finding anything in the SQL spec that addresses this point specifically --- but I also cannot find anything that suggests that the name scope rules differ between outer and inner joins. So it would be difficult for them to assert that element_id and watch_list_element.element_id must be treated as equivalent here, when they are clearly not equivalent in related cases. Anyone care to offer a gloss on the spec to prove that this behavior is correct or not correct? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 7.3 "group by" issue
Dan Langille writes: > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > >FROM watch_list JOIN watch_list_element > > ON watch_list.id = watch_list_element.watch_list_id > > WHERE > >watch_list.user_id = 1 > > GROUP BY wle_element_id This works because the first select list item is mentioned in the GROUP BY clause (using its output label, this is a PostgreSQL extension). > Yes, that works. But so do these. > > SELECT watch_list_element.element_id as wle_element_id, > COUNT(watch_list_id) >FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE >watch_list.user_id = 1 > GROUP BY watch_list_element.element_id This works because the first select list item is mentioned in the GROUP BY clause. > SELECT element_id as wle_element_id, COUNT(watch_list_id) >FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE >watch_list.user_id = 1 > GROUP BY element_id This works because the first select list item is mentioned in the GROUP BY clause. > The original situation which did not work is: > > SELECT watch_list_element.element_id as wle_element_id, > COUNT(watch_list_id) >FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE >watch_list.user_id = 1 > GROUP BY element_id This does not work because the first select list item references a column inside a join, which is not (necessarily) mathematically identical to the column that arrives outside of the join and is in the GROUP BY clause. (Think of an outer join: the column outside the join might contain added null values. Of course you are using an inner join, but the constructs work the same either way.) -- Peter Eisentraut [EMAIL PROTECTED] ---(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] 7.3 "group by" issue
- Original Message - From: "Dan Langille" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 21, 2003 7:01 PM Subject: [SQL] 7.3 "group by" issue > Hi folks, > > This query: > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id >AND watch_list.user_id = 1 > GROUP BY watch_list_element.element_id Try: SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY watch_list_element.element_id Ciao Gaetano ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 7.3 "group by" issue
On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > Hi folks, > > > > This query: > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > FROM watch_list JOIN watch_list_element > > ON watch_list.id = watch_list_element.watch_list_id > >AND watch_list.user_id = 1 > > GROUP BY watch_list_element.element_id > > Try: > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE > watch_list.user_id = 1 >GROUP BY watch_list_element.element_id ERROR: Attribute unnamed_join.element_id must be GROUPed or used in an aggregate function -- Dan Langille : http://www.langille.org/ ---(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] 7.3 "group by" issue
> On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > > > Hi folks, > > > > > > This query: > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > FROM watch_list JOIN watch_list_element > > > ON watch_list.id = watch_list_element.watch_list_id > > >AND watch_list.user_id = 1 > > > GROUP BY watch_list_element.element_id > > > > Try: > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > FROM watch_list JOIN watch_list_element > > ON watch_list.id = watch_list_element.watch_list_id > > WHERE > > watch_list.user_id = 1 > >GROUP BY watch_list_element.element_id > > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in > an aggregate function > I think that the wrong problem was solved here. Items in the order by clause must be in the target list. heres what it says in the docs *The ORDER BY clause specifies the sort order: *SELECT select_list * FROM table_expression * ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...] *column1, etc., refer to select list columns. These can be either the output name of a column (see Section 4.3.2) or the number of a column. Some examples: Note that "column1, etc., refer to select list" HTH Chad ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] How do I view triggers
Hi All, I am slightly confused as to how I view what triggers I have on a certain table. select * from pg_trigger doesn't show me the trigger I have just created, but its definitely there because when I try and create it, it gives an error that it already exists. TIA Graham ---(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] Fw:
Your ipc-daemon-version is to old. Download the newest version first. (1.13) Daniel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 7.3 "group by" issue
On 21 Feb 2003 at 13:00, Chad Thompson wrote: > > On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > > > > > Hi folks, > > > > > > > > This query: > > > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > > FROM watch_list JOIN watch_list_element > > > > ON watch_list.id = watch_list_element.watch_list_id > > > >AND watch_list.user_id = 1 > > > > GROUP BY watch_list_element.element_id > > > > > > Try: > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > FROM watch_list JOIN watch_list_element > > > ON watch_list.id = watch_list_element.watch_list_id > > > WHERE > > > watch_list.user_id = 1 > > >GROUP BY watch_list_element.element_id > > > > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in > > an aggregate function > > > > I think that the wrong problem was solved here. Items in the order by > clause must be in the target list. > > heres what it says in the docs > *The ORDER BY clause specifies the sort order: > > *SELECT select_list > * FROM table_expression > * ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...] > *column1, etc., refer to select list columns. These can be either the output > name of a column (see Section 4.3.2) or the number of a column. Some > examples: > > Note that "column1, etc., refer to select list" I don't see how ORDER BY enters into this situation. It's not used. What are you saying? -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 7.3 "group by" issue
> On 21 Feb 2003 at 13:00, Chad Thompson wrote: > > > > > On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > > > > > > > Hi folks, > > > > > > > > > > This query: > > > > > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > > > FROM watch_list JOIN watch_list_element > > > > > ON watch_list.id = watch_list_element.watch_list_id > > > > >AND watch_list.user_id = 1 > > > > > GROUP BY watch_list_element.element_id > > > > > > > > Try: > > > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > > FROM watch_list JOIN watch_list_element > > > > ON watch_list.id = watch_list_element.watch_list_id > > > > WHERE > > > > watch_list.user_id = 1 > > > >GROUP BY watch_list_element.element_id > > > > > > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in > > > an aggregate function > > > > > > > I think that the wrong problem was solved here. Items in the order by > > clause must be in the target list. > > > > heres what it says in the docs > > *The ORDER BY clause specifies the sort order: > > > > *SELECT select_list > > * FROM table_expression > > * ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...] > > *column1, etc., refer to select list columns. These can be either the output > > name of a column (see Section 4.3.2) or the number of a column. Some > > examples: > > > > Note that "column1, etc., refer to select list" > > I don't see how ORDER BY enters into this situation. It's not used. > What are you saying? > -- The same applies to group by... Sorry for the confusion. If the column is not in the select section of the statement, it cant group by it. Try this. SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY wle_element_id ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] good style?
hi, I have 8 tables and this query: select u.users_id, m.name as mandant_name, u.login_name, u.password, u.first_name, u.last_name, u.creation_date, g.name as groups_name, ae.acl_entry_id, a.name as acl_name, p.name as permission_name from mandant m, users_2_groups u2g, groups g, users u, permission p, acl a, acl_entry ae, groups_2_acl_entry g2ae where m.mandant_id = u.mandant_id and u2g.groups_id = g.groups_id and u2g.users_id = u.users_id and g2ae.groups_id = g.groups_id and g2ae.acl_entry_id = ae.acl_entry_id and ae.acl_id = a.acl_id and ae.permission_id = p.permission_id I'm not using JOIN for get this information. would be JOIN a better sql programming style? faster? Best Regards, Rafal sql script: DROP TABLE groups_2_acl_entry; DROP TABLE users_2_groups; DROP TABLE groups; DROP TABLE users; DROP TABLE acl_entry; DROP TABLE permission; DROP TABLE acl; DROP TABLE language; DROP TABLE mandant; DROP TABLE license; DROP TABLE firm; CREATE TABLE firm ( firm_id numeric(20, 0) NOT NULL, name varchar(40) NOT NULL, CONSTRAINT firm_pkey PRIMARY KEY (firm_id) ) WITH OIDS; INSERT INTO firm VALUES (1, 'polonium'); CREATE TABLE license ( license_id numeric(20, 0) NOT NULL, key varchar(100) NOT NULL, creation_date timestamp NOT NULL, valid_from timestamp NOT NULL, expired timestamp, CONSTRAINT license_pkey PRIMARY KEY (license_id) ) WITH OIDS; INSERT INTO license VALUES (1, 'NOT AT THIS TIME - SHOULD BE GENERATED', now(), now(), NULL); CREATE TABLE mandant ( mandant_id numeric(20, 0) NOT NULL, firm_id numeric(20, 0) NOT NULL, license_id numeric(20, 0) NOT NULL, parent_id numeric(20, 0), name varchar(20) NOT NULL, creation_date timestamp NOT NULL, CONSTRAINT mandant_pkey PRIMARY KEY (mandant_id), CONSTRAINT fk_firm FOREIGN KEY (firm_id) REFERENCES firm (firm_id), CONSTRAINT fk_license FOREIGN KEY (license_id) REFERENCES license (license_id), CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES mandant (mandant_id) ) WITH OIDS; INSERT INTO mandant VALUES (1, 1, 1, NULL, 'polonium', now()); CREATE TABLE language ( language_id int2 NOT NULL, lang_short char(2) NOT NULL, lang_long varchar(20) NOT NULL, CONSTRAINT language_pkey PRIMARY KEY (language_id) ) WITH OIDS; CREATE UNIQUE INDEX language_lang_short_idx ON language (lang_short); CREATE UNIQUE INDEX language_lang_idx ON language (lang_short, lang_long); INSERT INTO language VALUES (1, 'de', 'deutsch'); INSERT INTO language VALUES (2, 'en', 'english'); CREATE TABLE acl ( acl_id int2 NOT NULL, name varchar(20) NOT NULL, description varchar(200), CONSTRAINT acl_pkey PRIMARY KEY (acl_id) ) WITH OIDS; CREATE UNIQUE INDEX acl_name_idx ON acl (name); INSERT INTO acl VALUES (1, 'mmcms.access', 'acl for login module'); INSERT INTO acl VALUES (2, 'mmcms.system', 'acl for system module'); INSERT INTO acl VALUES (3, 'mmcms.admin', 'acl for admin module'); INSERT INTO acl VALUES (4, 'mmcms.category', 'acl for category module'); INSERT INTO acl VALUES (5, 'mmcms.context', 'acl for context module'); CREATE TABLE permission ( permission_id int2 NOT NULL, name varchar(20) NOT NULL, description varchar(200), CONSTRAINT permission_pkey PRIMARY KEY (permission_id) ) WITH OIDS; CREATE UNIQUE INDEX permission_name_idx ON permission (name); INSERT INTO permission VALUES (1, 'access', 'access permission'); INSERT INTO permission VALUES (2, 'read', 'read permission'); INSERT INTO permission VALUES (3, 'write', 'write permission'); INSERT INTO permission VALUES (4, 'execute', 'execute permission'); INSERT INTO permission VALUES (5, 'modify', 'modify permission'); INSERT INTO permission VALUES (6, 'list', 'list permission'); CREATE TABLE acl_entry ( acl_entry_id int2 NOT NULL, acl_id int2 NOT NULL, permission_id int2 NOT NULL, CONSTRAINT acl_entry_pkey PRIMARY KEY (acl_entry_id), CONSTRAINT fk_acl FOREIGN KEY (acl_id) REFERENCES acl (acl_id), CONSTRAINT fk_permission FOREIGN KEY (permission_id) REFERENCES permission (permission_id) ) WITH OIDS; -- acl_entry for mmcms.access acl with access permission -- acl 'mmcms.access' has 'access' permission INSERT INTO acl_entry VALUES (1, 1, 1); -- acl 'mmcms.system' has 'read' permission INSERT INTO acl_entry VALUES (2, 2, 2); -- acl 'mmcms.system' has 'write' permission INSERT INTO acl_entry VALUES (3, 2, 3); -- acl 'mmcms.admin' has 'read' permission INSERT INTO acl_entry VALUES (4, 3, 2); -- acl 'mmcms.admin' has 'write' permission INSERT INTO acl_entry VALUES (5, 3, 3); CREATE TABLE users ( users_id numeric(20, 0) NOT NULL, mandant_id numeric(20, 0) NOT NULL, language_id int2 NOT NULL, login_name varchar(50) NOT NULL, password varchar(15) NOT NULL, first_name varchar(20) NOT NULL, last_name varchar(20) NOT NULL, creation_date timestamp NOT NULL, last_login_date timestamp, status int2 NOT NULL, CONSTRAINT users_pkey PRIMARY KEY (users_id), CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFE
Re: [SQL] good style?
this kind of joining has been termed "explicit JOIN syntax" by pgsql 7.3 docs. I personally feel it makes ur SQL look uglier and complicated. i feel the WHERE caluse shud contain the genuine filters of result set not the ones which could be a part of JOIN syntax itself. (its personal view though) you may refer to the DOCs below on postgresqls' website. http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=explicit-joins.html . excerpt from the docs. When a query only involves two or three tables, there aren't many join orders to worry about. But the number of possible join orders grows exponentially as the number of tables expands. Beyond ten or so input tables it's no longer practical to do an exhaustive search of all the possibilities, and even for six or seven tables planning may take an annoyingly long time. When there are too many input tables, the PostgreSQL planner will switch from exhaustive search to a genetic probabilistic search through a limited number of possibilities. (The switch-over threshold is set by the GEQO_THRESHOLD run-time parameter described in the PostgreSQL 7.3 Administrator's Guide.) The genetic search takes less time, but it won't necessarily find the best possible plan. regds mallah. On Friday 21 February 2003 07:00 pm, Rafal Kedziorski wrote: > hi, > > I have 8 tables and this query: > > select u.users_id, m.name as mandant_name, u.login_name, u.password, > u.first_name, u.last_name, u.creation_date, g.name as groups_name, > ae.acl_entry_id, a.name as acl_name, p.name as permission_name > from mandant m, users_2_groups u2g, groups g, users u, permission p, > acl a, acl_entry ae, groups_2_acl_entry g2ae > where m.mandant_id = u.mandant_id and > u2g.groups_id = g.groups_id and > u2g.users_id = u.users_id and > g2ae.groups_id = g.groups_id and > g2ae.acl_entry_id = ae.acl_entry_id and > ae.acl_id = a.acl_id and > ae.permission_id = p.permission_id > > I'm not using JOIN for get this information. would be JOIN a better sql > programming style? faster? > > > Best Regards, > Rafal > > > sql script: > > DROP TABLE groups_2_acl_entry; > DROP TABLE users_2_groups; > DROP TABLE groups; > DROP TABLE users; > DROP TABLE acl_entry; > DROP TABLE permission; > DROP TABLE acl; > DROP TABLE language; > DROP TABLE mandant; > DROP TABLE license; > DROP TABLE firm; > > > > CREATE TABLE firm ( > firm_id numeric(20, 0) NOT NULL, > name varchar(40) NOT NULL, > CONSTRAINT firm_pkey PRIMARY KEY (firm_id) > ) WITH OIDS; > > INSERT INTO firm VALUES (1, 'polonium'); > > > > CREATE TABLE license ( > license_id numeric(20, 0) NOT NULL, > key varchar(100) NOT NULL, > creation_date timestamp NOT NULL, > valid_from timestamp NOT NULL, > expired timestamp, > CONSTRAINT license_pkey PRIMARY KEY (license_id) > ) WITH OIDS; > > INSERT INTO license VALUES (1, 'NOT AT THIS TIME - SHOULD BE GENERATED', > now(), now(), NULL); > > > > CREATE TABLE mandant ( > mandant_id numeric(20, 0) NOT NULL, > firm_id numeric(20, 0) NOT NULL, > license_id numeric(20, 0) NOT NULL, > parent_id numeric(20, 0), > name varchar(20) NOT NULL, > creation_date timestamp NOT NULL, > CONSTRAINT mandant_pkey PRIMARY KEY (mandant_id), > CONSTRAINT fk_firm FOREIGN KEY (firm_id) REFERENCES firm (firm_id), > CONSTRAINT fk_license FOREIGN KEY (license_id) REFERENCES license > (license_id), > CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES mandant > (mandant_id) > ) WITH OIDS; > > INSERT INTO mandant VALUES (1, 1, 1, NULL, 'polonium', now()); > > > > CREATE TABLE language ( > language_id int2 NOT NULL, > lang_short char(2) NOT NULL, > lang_long varchar(20) NOT NULL, > CONSTRAINT language_pkey PRIMARY KEY (language_id) > ) WITH OIDS; > > CREATE UNIQUE INDEX language_lang_short_idx ON language (lang_short); > CREATE UNIQUE INDEX language_lang_idx ON language (lang_short, lang_long); > > INSERT INTO language VALUES (1, 'de', 'deutsch'); > INSERT INTO language VALUES (2, 'en', 'english'); > > > > CREATE TABLE acl ( > acl_id int2 NOT NULL, > name varchar(20) NOT NULL, > description varchar(200), > CONSTRAINT acl_pkey PRIMARY KEY (acl_id) > ) WITH OIDS; > > CREATE UNIQUE INDEX acl_name_idx ON acl (name); > > INSERT INTO acl VALUES (1, 'mmcms.access', 'acl for login module'); > INSERT INTO acl VALUES (2, 'mmcms.system', 'acl for system module'); > INSERT INTO acl VALUES (3, 'mmcms.admin', 'acl for admin module'); > INSERT INTO acl VALUES (4, 'mmcms.category', 'acl for category module'); > INSERT INTO acl VALUES (5, 'mmcms.context', 'acl for context module'); > > > > CREATE TABLE permission ( > permission_id int2 NOT NULL, > name varchar(20) NOT NULL, > description varchar(200), > CONSTRAINT permission_pkey PRIMARY KEY (permission_id) > ) WITH OIDS; > > CREATE UNIQUE INDEX permission_name_idx ON permission (name); > > INSERT INTO permission VALUES (
[SQL] function defination help ..
Hi, is it possible to get the function creation defination as produced by pg_dump by some SQL queries on system catalogs? pg_func stores procsrc but i am trying to get RETURNS and the arg part also. -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] good style?
In article <[EMAIL PROTECTED]>, Rafal Kedziorski wrote: > hi, > > I have 8 tables and this query: > > select u.users_id, m.name as mandant_name, u.login_name, u.password, > u.first_name, u.last_name, u.creation_date, g.name as groups_name, > ae.acl_entry_id, a.name as acl_name, p.name as permission_name > from mandant m, users_2_groups u2g, groups g, users u, permission p, > acl a, acl_entry ae, groups_2_acl_entry g2ae > where m.mandant_id = u.mandant_id and > u2g.groups_id = g.groups_id and > u2g.users_id = u.users_id and > g2ae.groups_id = g.groups_id and > g2ae.acl_entry_id = ae.acl_entry_id and > ae.acl_id = a.acl_id and > ae.permission_id = p.permission_id > > I'm not using JOIN for get this information. would be JOIN a better sql > programming style? faster? Better style, yes. Whitespace would help also. Faster, maybe. If you use join clauses you will be able to take control over your query, specifying what gets joined when. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] good style?
> -Original Message- > From: Rafal Kedziorski [mailto:[EMAIL PROTECTED] > Sent: Friday, February 21, 2003 3:30 PM > To: [EMAIL PROTECTED] > Subject: [SQL] good style? > > > hi, > > I have 8 tables and this query: > > select u.users_id, m.name as mandant_name, u.login_name, u.password, > u.first_name, u.last_name, u.creation_date, g.name as groups_name, > ae.acl_entry_id, a.name as acl_name, p.name as permission_name > from mandant m, users_2_groups u2g, groups g, users u, > permission p, > acl a, acl_entry ae, groups_2_acl_entry g2ae > where m.mandant_id = u.mandant_id and > u2g.groups_id = g.groups_id and > u2g.users_id = u.users_id and > g2ae.groups_id = g.groups_id and > g2ae.acl_entry_id = ae.acl_entry_id and > ae.acl_id = a.acl_id and > ae.permission_id = p.permission_id > > I'm not using JOIN for get this information. would be JOIN a > better sql > programming style? faster? > As there is no outer join syntax to use in WHERE, you need to write LEFT JOINs anyway. And in this case it looks better if you write all joins as JOIN clauses. When using JOIN you are directing Postgres to use exactly this join order. I found it preferrable over letting query optimizer to decide. Generally you know better what tables will contain more rows and what less. It's more important in development phase, because there is usually not much test data and all tables look the same to optimizer. There are few cases, when it's better to join in WHERE. For example when you have 3 tables, all joined sequentially, and you sometimes filter by field in table1, sometimes by field in table3. When you fix join order by using JOINS then one of the queries may perform bad. When you join tables in WHERE, the optimizer chooses whether it should join table1 and table2 first or table3 and table2 first. The former is better when filtering by field in table1, the latter is better when filtering by field in table3. Tambet ---(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] good style?
Tambet Matiisen wrote: -Original Message- From: Rafal Kedziorski [mailto:[EMAIL PROTECTED] Sent: Friday, February 21, 2003 3:30 PM To: [EMAIL PROTECTED] Subject: [SQL] good style? hi, I have 8 tables and this query: select u.users_id, m.name as mandant_name, u.login_name, u.password, u.first_name, u.last_name, u.creation_date, g.name as groups_name, ae.acl_entry_id, a.name as acl_name, p.name as permission_name from mandant m, users_2_groups u2g, groups g, users u, permission p, acl a, acl_entry ae, groups_2_acl_entry g2ae where m.mandant_id = u.mandant_id and u2g.groups_id = g.groups_id and u2g.users_id = u.users_id and g2ae.groups_id = g.groups_id and g2ae.acl_entry_id = ae.acl_entry_id and ae.acl_id = a.acl_id and ae.permission_id = p.permission_id I'm not using JOIN for get this information. would be JOIN a better sql programming style? faster? As there is no outer join syntax to use in WHERE, you need to write LEFT JOINs anyway. And in this case it looks better if you write all joins as JOIN clauses. When using JOIN you are directing Postgres to use exactly this join order. I found it preferrable over letting query optimizer to decide. Generally you know better what tables will contain more rows and what less. It's more important in development phase, because there is usually not much test data and all tables look the same to optimizer. I would genarete more test data and compare my first version with the new version. Rafal ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 7.3 "group by" issue
On 21 Feb 2003 at 13:30, Chad Thompson wrote: > The same applies to group by... Sorry for the confusion. > > If the column is not in the select section of the statement, it cant group > by it. > Try this. > > SELECT element_id as wle_element_id, COUNT(watch_list_id) >FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE >watch_list.user_id = 1 > GROUP BY wle_element_id Yes, that works. But so do these. SELECT watch_list_element.element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY watch_list_element.element_id SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY element_id The original situation which did not work is: SELECT watch_list_element.element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY element_id My question: why should it not work? It's referring to the same column as the previous two examples which do work. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
