[SQL] Software for database-visualisation
Another question: Which software are you using to visualize your database-structur. We're doing it with Quark, but are not very happy with this. Thanks Kai... -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Viele, die die schaendlichsten Handlungen begehen, fuehren hoechst vernuenftige Reden. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Advanced SELECT
Hi Folks, I hope to have found the right group for my question. I have difficult sql-task. I try to describe it simple: We have a table 'company' with a cid and a table 'sector' with a sid. They are connected m:n via a third table 'company_sector' which contians csid, cid and sid. The normal clause would look like: SELECT c.companyname, s.sectorname FROM company c, sector s, company_sector cs WHERE cs.cid = c.cid AND cs.sid = s.sid ORDER BY c.companyname; This gives a result looking like this: c.companyname | s.sector ---+ company1 | sectora company1 | sectorb company2 | sectora company2 | sectorb company2 | sectorc company3 | sectora company4 | sectorc instead of this I want to have a listing like: c.companyname | ??? (sectors) ---+ company1 | sectora, sectorb company2 | sectora, sectorb, sectorc company3 | sectora company4 | sectorc But I have no idea, how to write a SELECT-command that gives a listing like this :( Maybe anyone can help *please* *Thanks and greetings* Kai... -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Unzeitige Genuesse erzeugen Ekel. (Demokrit, um 460 v. Chr.) ---(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] Advanced SELECT
Richard Huxton schrieb: > Search the mailing-list archives for "custom aggregate concat" and > you'll quickly find an example of how to write your own custom aggregate > (like SUM()). > > Warning - I don't think you can guarantee the order of elements in the > aggregated sectors. Thank you very much. This was the right hint where to search. For the order I'll try to find some possibilities to write a function, that does a sort on this ;) We'll see... -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Wer Unrecht tut ist ungluecklicher als wer unrecht leidet. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Software for database-visualisation
Sean Davis schrieb: > If you mean literally visualizing the ERD, you can look at > SQL::Translator (on cpan) which can draw fairly complex ERDs and output > as graphics (I forget the supported formats) I'll have a look at it. Thank you both! -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Wer Unrecht tut ist ungluecklicher als wer unrecht leidet. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Advanced SELECT
Hi, Tom Lane schrieb: > [ experiments... ] This works reliably in 7.4 and up. Before that, > the optimizer didn't make the connection between the sort ordering of > the inner query and that needed by the outer, so it would repeat the > sort step using only key1 and very possibly destroy the key2 ordering. *Thanks* this was the same idea, that I had. Combined with a DISTINCT there are no double entries. It works here perfect with 7.39. If someone is interested, this is the full SELECT: - SELECT case when lower(substring(f.marketingid,1,1))='d' then 'Deutschland' when lower(substring(f.marketingid,1,1))='s' then 'Schweiz' else '' end AS bereich, f.fid AS fid, f.marketingid AS marketingid, f.firmalang AS unternehmen, case when fp.www IS NULL then '' else fp.www end AS url, case when fp.jobwww IS NULL then '' else fp.jobwww end AS joburl, case when b.branchen IS NULL then '' else b.branchen end AS branchen, case when j.jobtypen IS NULL then '' else j.jobtypen end AS gesuchte_jobtypen, case when g.funktionen IS NULL then '' else g.funktionen end AS gesuchte_funktionen, case when a.faecher IS NULL then '' else a.faecher end AS gesuchte_fachrichtungen, case when s.zusatz IS NULL then '' else s.zusatz end AS adresse_zusatz, case when s.strasse IS NULL then '' else s.strasse end AS strasse, case when s.plz IS NULL then '' else s.plz end AS plz, case when s.ort IS NULL then '' else s.ort end AS ort, case when s.land IS NULL then '' else s.land end AS land, case when m.ansprechpartner IS NULL then '' else m.ansprechpartner end AS ansprechpartner FROM firma f JOIN ( SELECT js.fid, group_concat(js.jobtyp) AS jobtypen FROM ( SELECT DISTINCT f.fid, jt.jobtyp FROM firma f LEFT OUTER JOIN (jobtext j JOIN jobtyp jt ON (j.jobtypid=jt.jobtypid)) ON (f.fid=j.fid) WHERE (j.status>-1 OR j.status IS NULL) AND (jt.status>-1 OR jt.status IS NULL) AND f.status>-1 ORDER BY jobtyp ) js GROUP BY js.fid ) j ON (f.fid=j.fid) JOIN ( SELECT bs.fid, group_concat(bs.fbranche) AS branchen FROM ( SELECT DISTINCT f.fid, b.fbranche FROM firma f LEFT OUTER JOIN (firma_fbranche fb JOIN fbranche b ON (fb.fbrancheid=b.fbrancheid)) ON (f.fid=fb.fid) WHERE (b.status>-1 OR b.status IS NULL) AND f.status>-1 ORDER BY fbranche ) bs GROUP BY bs.fid ) b ON (f.fid=b.fid) JOIN ( SELECT gs.fid, group_concat(gs.bfunktion) AS funktionen FROM ( SELECT DISTINCT f.fid, b.bfunktion FROM firma f LEFT OUTER JOIN (firma_bfunktion bf JOIN bfunktion b ON (bf.bfunktionid=b.bfunktionid)) ON (f.fid=bf.fid) WHERE (b.status>-1 OR b.status IS NULL) AND f.status>-1 ORDER BY b.bfunktion ) gs GROUP BY gs.fid ) g ON (f.fid=g.fid) JOIN ( SELECT au.fid, group_concat(au.fach) AS faecher FROM ( SELECT DISTINCT f.fid, a.fach FROM firma f LEFT OUTER JOIN (firma_fach fa JOIN fach a ON (fa.fachid=a.fachid)) ON (f.fid=fa.fid) WHERE (a.status>-1 OR a.status IS NULL) AND f.status>-1 ORDER BY a.fach ) au GROUP BY au.fid ) a ON (f.fid=a.fid) JOIN ( SELECT ms.fid, group_concat(ms.ansprechpartner) AS ansprechpartner FROM ( SELECT DISTINCT f.fid, CASE WHEN (m.titel IS NULL OR length(m.titel)<2) THEN '' ELSE m.titel || ' ' END || CASE WHEN m.vname IS NULL THEN '' ELSE m.vname || ' ' END || CASE WHEN m.nname IS NULL THEN '' ELSE m.nname END AS ansprechpartner FROM firma f LEFT OUTER JOIN ( sitz s JOIN (SELECT * FROM mitarb WHERE ffunktionid=1 AND status>-1) m ON (s.sitzid=m.sitzid)) ON (f.fid=s.fid) WHERE (s.status>-1 OR s.status IS NULL) AND f.status>-1 ORDER BY ansprechpartner ) AS ms GROUP BY ms.fid ) m ON (f.fid=m.fid) LEFT OUTER JOIN ( SELECT f.fid, group_concat(s.zusatz) AS zusatz, group_concat(s.strasse) AS strasse, group_concat(s.plz) AS plz, group_concat(s.ort) AS ort, group_concat(l.land) AS land FROM firma f JOIN sitz s ON (f.fid=s.fid) JOIN land l ON s.landid=l.landid WHERE s.status>-1 AND s.hauptsitz=1 AND f.status>-1 GROUP BY f.fid ) s ON (f.fid=s.fid) LEFT OUTER JOIN fplus fp ON (f.fid=fp.fid) ORDER BY lower(substring(f.marketingid,1,1)), lower(f.firmalang); -- *greets* Kai -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Gut ist nicht Nichtfreveln, sondern nicht einmal freveln wollen. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Problem with SQL_ASCII
I have a little problem in PostgreSQL 7.39 (and previous). Our database is in 'SQL_ASCII'-Format. When doing SQL-Selects all special Characters (e.g. äöüß, etc...) are ASCII encoded (sure they are). Is there any function to change the encoding to - let's say - LATIN1 (reverse function for to_ascii($text, 'LATIN1'))? Otherwise, is it possible to write a function which just uses a character replacement? I can image a select like: Name in Table contains 'Ernst & Young AG', select is: SELECT from_ascii(name, 'LATIN1') FROM table; And output should be 'Ernst & Young AG' We do this now in a second step with a find/replace tool... Anyone any idea? Thanks and *greets* Kai... -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Gut ist nicht Nichtfreveln, sondern nicht einmal freveln wollen. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] can a function return a virtual table?
This is the question i'm telling myself. It is because we don't really delete table entries, just setting a status field to '-1'. So a valid select would look like: SELECT xyz, abc FROM (SELECT * FROM tablex WHERE status > -1); It would be much nicer to have to write something like: SELECT xyz, abc FROM active(tablex); where the function 'active(x)' returns a virtual table with all entries from table x where status is > -1. But sadly I have no idea how write such a function. Good old O'reilly can't help (or i'm to dumb *g*). -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Bei seinen Handlungen ist vorzubedenken besser als nachzubedenken. (Demokrit, um 460 v. Chr.) ---(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] can a function return a virtual table?
Hi, and thanks for the answer ;) (*upps* just noticed, that I sent the answer accidently direct to poster^^ *sorry*) Michael Fuhr schrieb: >> I'll pick a nit and point out that the above isn't a valid query: >> >> test=> SELECT xyz, abc FROM (SELECT * FROM tablex WHERE status > -1); >> ERROR: subquery in FROM must have an alias >> HINT: For example, FROM (SELECT ...) [AS] foo. *yeah* sure you're right. I shortened it to much^^ >> In this simple example you could omit the subquery altogether: >> SELECT xyz, abc FROM tablex WHERE status > -1; It is not about such a simple example. When joining tables (especially with outer joins) it isn't trivial ;) >> See the documentation for writing set-returning functions (SRFs). >> The following links should get you started (if you're using a version >> of PostgreSQL older than 8.0, then see the documentation for that >> version): Thanks, I think this is what I've searched for (btw. we use 7.3). But what I want to do is: CREATE FUNCTION active(char) RETURNS setof $1 AS ' SELECT * FROM $1 WHERE status>-1; ' LANGUAGE SQL; But this does not work. So I'll use views instead ;) It also should be more performant. Thanks... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
