Re: [SQL] One-2-many relation - need distinct counts
On Tuesday 26 August 2003 14:54, PS PS wrote: > Select Count(Distinct(account_no)) > from A, B > where A.Account_no = B.Account_no > > I get the correct count. If I do this: > Select Count(Distinct(account_no)), B.Account_type > from A, B > where A.Account_no = B.Account_no > group by B.Account_type > > I get wrong counts because there some are duplicated. > I tried everything that I can think of - subquery, sub > table etc. I would appreciate some help in writing > the query. Thanks in advance. I'm not sure the query is well formed. If you have the following in B: Acct_type | Acct_no alpha | 0001 beta | 0002 alpha | 0003 beta | 0003 I think you're saying you get: alpha 2 beta 2 Are you saying you want alpha 2 beta 1 or: alpha 1 beta 2 If you're not sure which you want, that's the route of your problem. If you want the first try something like SELECT account_no, min(account_type) FROM B GROUP BY account_no -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] length of array
Hello Is there a function in postgres to return the length of an array field ? I have seen array_dims(array) but this returns a character value. Ideally, I'd like something numeric returned. Thanks Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to return a record set from function.
Kumar wrote: Create table t1 (c1 int, c2 varchar, c3 varchar); Create or Replace function sel_t1 () returns setof records as ' select c1, c2, c3 from t1; ' Language SQL; It was fine and created a function. while i execute it as select sel_t1; I got the following error. ERROR: Cannot display a value of type RECORD I see three problems. 1) you need parenthesis on the function call, i.e. "sel_t1()" as compared with "sel_t1" 2) when returning setof record, the "sel_t1()" must be in the FROM clause of the statement 3) when the function is declared as returning "record" as compared to a named complex type such as "t1", you need to include a column definition list in the SQL statement So, putting it all together, try something like this: SELECT f1, f2, f3 FROM sel_t1() AS (f1 int, f2 varchar, f3 varchar); See: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=xfunc-tablefunctions.html and http://techdocs.postgresql.org/guides/SetReturningFunctions HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to optimize this query ?
On Wed, 27 Aug 2003, ProgHome wrote: > You were right, Stephan ! > The query below is still not correct ... because the second line > shouldn't be shown ! > Now I really don't know how I could rewrite this without a subquery > because it doesn't seem to be possible with some LEFT or INNER joins ! > Do you have an idea ? The only ways I can think of are through a subquery (*) or possibly there might be a way to do it with EXCEPT, but on first blush that seems like it might be difficult and probably not any better speed wise. (*) - You were using IN (subquery) which is known to be poorly optimized for 7.3 and earlier. You might want to see how it performs on your data under 7.4beta for forward looking, and/or consider converting into a form using EXISTS rather than IN. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] length of array
Chris Faulkner wrote: Is there a function in postgres to return the length of an array field ? I have seen array_dims(array) but this returns a character value. Ideally, I'd like something numeric returned. Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do this (for a one-dimensional array at least): SELECT replace(split_part(array_dims(array_fld),':',1),'[','')::int as low FROM tbl; SELECT replace(split_part(array_dims(array_fld),':',2),']','')::int as high FROM tbl; In 7.4 (now in beta) there are two new functions, array_lower() and array_upper() that do what you're looking for: regression=# select array_lower(array_fld, 1) from tbl; array_lower - 1 (1 row) regression=# select array_upper(array_fld, 1) from tbl; array_upper - 2 (1 row) See the following links for more on 7.4's array support: http://developer.postgresql.org/docs/postgres/arrays.html http://developer.postgresql.org/docs/postgres/functions-array.html http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS HTH, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Canceling other backend's query
On Wed, 27 Aug 2003, daq wrote: > Hi, > > Can i cancel querys runing on other backends, or disconnect a client > from the server? I can kill the backend process, but sometimes this > causing shared memory troubles. If you kill -9 a backend, you will cause the shared memory problem. Try just a plain kill . That should work without causing shared memory to dump. ---(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] How to return a record set from function.
On Wed, 27 Aug 2003, Kumar wrote: > Dear Friends, > > I am newbie to Postgres. I am running 7.3.4 on Linux 7.3. I am using > Pgadmin tool. I need to return the table rows via record set. > > Create table t1 (c1 int, c2 varchar, c3 varchar); > > Create or Replace function sel_t1 () returns setof records as ' Why not setof t1? > select c1, c2, c3 from t1; > ' Language SQL; > > It was fine and created a function. while i execute it as > > select sel_t1; You probably want select * from sel_t1() as tab(c1 int, c2 varchar, c3 varchar) (if you return setof record) or select * from sel_t1(); (if you return setof t1) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Canceling other backend's query
daq wrote: > Hi, > > Can i cancel querys runing on other backends, or disconnect a client > from the server? I can kill the backend process, but sometimes this > causing shared memory troubles. See the 'postgres' manual page for a list of signals and their effects. -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to return a record set from function.
On Wednesday 27 August 2003 08:18, Kumar wrote: > Dear Friends, > > I am newbie to Postgres. I am running 7.3.4 on Linux 7.3. I am using > Pgadmin tool. I need to return the table rows via record set. > > Create table t1 (c1 int, c2 varchar, c3 varchar); > > Create or Replace function sel_t1 () returns setof records as ' > select c1, c2, c3 from t1; > ' Language SQL; > > It was fine and created a function. while i execute it as > > select sel_t1; > > I got the following error. > > ERROR: Cannot display a value of type RECORD You probably want to return "setof t1" and then do: SELECT * FROM sel_t1(); -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] script to create a sample database
Hello Guys, Does any of you have a script for creating a sample database witch will include all kinds of objects that postgres suports ? I will be very glad if someone can help me! Thanks, /Marian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Restore deleted records
> Konstantin Petrenko wrote, On 8/26/2003 11:37 AM: > > >Hello. > > > >I accidentally deleted some recordes from my table. How can I restore > >them? Is it possible in 7.3.3? > if you have a dump, or you are still in a transaction, you can rollback. I avoided responding earlier, hoping you could get some more detailed help. Besides recovering from backup (or not committing the transaction if by some chance you haven't already) you can probably get back data from the deleted rows if you haven't run a vacuum since committing the delete. I don't know the specifics of how to do this, but the initial step will be to shut the database cluster down and make a copy of it before running vacuum. Once vacuum has been run, the old tuples will start being written over and you won't have a reliable way to recover the data. If you have alrerady done a vacuum full, you probably won't be able to much of anything back. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] [OT?]*_fsm_* parameters in postgresql.conf: which versions supports them?
Hi, hope this is not too off topic: I run postgresql 7.1.3 on Solaris 8; I've read on http://cbbrowne.com/info/postgresql.html that 'You will only get effective nonblocking VACUUM queries if the dead tuples can be listed in the Free Space Map': this could be done '[increasing], in postgresql.conf, the value of max_fsm_pages and max_fsm_relations'. I didn't found any of these parameter in my postgresql.conf so I suppose that my version of postgresql is too old. Which versions supports these parameters? TIA Marco -- Marco Vezzoli tel. +39 039 603 6852 STMicroelectronics fax. +39 039 603 5055 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] postgres 7.1.3: why does the query plan ignore indexes?
"Tom Lane" <[EMAIL PROTECTED]> wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Should we consider adding some warning when someone creates an index on > > an int2 column? > > I don't think so. Better to expend our energy on solving the > fundamental problem. In the mean time that the fundamental problem is solved may be a warning is usefull. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] smallfloat with postgresql
sorry again (I am not smoking crack, only drinking some good ceres...) smallint is not right (it is right for postgresql as a data type as read from the guide)... The real data type is "smallfloat" (found in my informix tables) so, Can I safely use NUMERIC as a data type for monetary computation instead of smallfloat ??? alessandro depetro -- Coop Service Noncello s.c.a r.l. onlus http://www.mamcoop.it/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] smallint with postgresql
ooops NUMBER is NUMERIC excuse my french :) alessandro -- Coop Service Noncello s.c.a r.l. onlus http://www.mamcoop.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
[SQL] smallint with postgresql
hello folks! I havo to migrate some tables from Informix to Postgresql and I I'm in doubt if I can safely use NUMBER (as suggested for monetary computation in PGSQL guide) as a data type for smallint. TIA and gby Alessandro Depetro -- Coop Service Noncello s.c.a r.l. onlus http://www.mamcoop.it/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [OT?]*_fsm_* parameters in postgresql.conf: which versions supports them?
On Wed, Aug 27, 2003 at 13:47:08 +0200, Marco Vezzoli <[EMAIL PROTECTED]> wrote: > Hi, > hope this is not too off topic: I run postgresql 7.1.3 on Solaris 8; > I've read on http://cbbrowne.com/info/postgresql.html that 'You will > only get effective nonblocking VACUUM queries if the dead tuples can be > listed in the Free Space Map': this could be done '[increasing], in > postgresql.conf, the value of max_fsm_pages and max_fsm_relations'. > I didn't found any of these parameter in my postgresql.conf so I suppose > that my version of postgresql is too old. Yes, your version is too old. > Which versions supports these parameters? You probably want to upgrade to 7.3.4. ---(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] smallfloat with postgresql
On Wed, Aug 27, 2003 at 01:31:23 +0200, Alessandro Depetro <[EMAIL PROTECTED]> wrote: > > so, Can I safely use NUMERIC as a data type for monetary computation instead > of smallfloat ??? NUMERIC represents decimal numbers exactly so is suitable for monetary types using even decimal fractions. ---(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] Q: Multicolumn lookup, Join or Sub-query ?
I have a table T with many columns whose values are are lookup keys id_1, id_2, id_3, id_4, id_5, ..., id_26 The values corresponding to the keys live in table L, the lookup table: id, id_value T might have 100K rows and L 500K rows. I am wondering what would be the best view (performance-wise) to see the values? - Method one - join create view V1 as select a.id_value as v_1, ... z.id_value as v_26 from T, L as a, L as b, ..., L as z where a.id = T.id_1 and b.id = T.id_2 ... and z.id = T.id_26 - Method two - sub-query create view V2 as select (select id_value from L where id = id_1) as v_1 , (select id_value from L where id = id_2) as v_2 ... , (select id_value from L where id = id_26) as v_26 TIA, Richard ---(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] How to optimize this query ?
You were right, Stephan ! The query below is still not correct ... because the second line shouldn't be shown ! Now I really don't know how I could rewrite this without a subquery because it doesn't seem to be possible with some LEFT or INNER joins ! Do you have an idea ? -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 2:29 PM To: ProgHome Cc: 'Franco Bruno Borghesi'; [EMAIL PROTECTED] Subject: RE: [SQL] How to optimize this query ? On Wed, 13 Aug 2003, ProgHome wrote: > I tried with some LEFT JOINS, which give me the possibility to keep > the information of the right table. > > I have now the following query, which is 10 times faster !!! (from 16s > to 1.6s) > But I'd like to remove the last subquery, to see if it faster ;) > > > - > > SELECT lead. * > FROM lead > LEFT JOIN purchase ON ( lead.id = purchase.lead_id ) > LEFT JOIN affiliate_lockout ON ( lead.affiliate_id = > affiliate_lockout.affiliate_locked_id ) > WHERE ( > exclusive IS NULL OR ( > exclusive = 0 AND nb_purchases < 3 > ) > ) AND id NOT > IN ( > > SELECT lead_id > FROM purchase > INNER JOIN member_exclusion > WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND > purchase.member_id = 21101 > ) AND ( > affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS > NULL > ) AND purchase.member_id <> 21101 > GROUP BY lead.id As I replied to Franco for his query below, I believe this query is not equivalent to your original query for a few cases, but those might not come up. If you had a row in lead like id = 2, affiliate_id = 2 And rows in affiliate_lockout like: affiliate_locked_id=2, member_id=21101 affiliate_locked_id=2, member_id=31101 should this row in lead be shown or not? In the original query I think it would not (because lead.affiliate_id was IN the affiliate_lockout table where member_id=21101). In the above query I think it will, because one of the joined tables will have the lead information and a member_id that is not equal to 21101. ---(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] lock row in table
Hallo everybody, I have a problem that I can not to solve in a simple way. SOME INFORMATION: I have a postgresql database version 7.2.2 on Linux platform. I communicate with Postgresql from a Delphi application using microolap drivers and everything works fine. PROBLEM DESCRIPTION: I have to lock one row in table1 for user1. In the same time other users should be able to read this record but when other user for example user2 want to edit this record user2 should get information "The row you try to edit is currently edit" - or sometihing similar. Of course I can lock record with syntax "Begin; select * from table1 where ID=12 for update; update table1 set field1="New value" where ID=12; commit;" but I can not to inform other user that the record is edited? MAIN TARGET: How to get information that current record is edited? Which function can I use? Please help me, Maybe someone have similar problem? Greetings, Daniel
Re: [SQL] lock row in table
Of course I can lock record with syntax
"Begin;
select
* from table1 where ID=12 for update;
update table1 set
field1="New value" where ID=12;
commit;"
but I can not to inform other user that the
record is edited?
MAIN TARGET:
How to get information that current record is
edited? Which function can I use?
* as long as i know, the only message you could
create is just
* raise notice 'ur_message'; or
raise exception 'ur_error_message';
* and the only message that delphi could read
is just raise exception.
* And if you wanna make your own message,
perhaps you must using try and except
* and you could give your own message via
ShowMessage('ur_message') or other
* Class object on delphi.
Please help me, Maybe someone have similar
problem?
Greetings,
Daniel
Hope it Help, GOD Bless You and Bravo
PostgreSQL.
Re: [SQL] lock row in table
Hi,
No, I am sure that I can get this message from Delphi. Check it on web site
www.microolap.com
For example when I try to put a non-unique value in a primary key I get message from
database that I get conflict with primary key :-)
of course I use raise ... except ... but everything work perfectly... error handling
works correctly :-)
so I am waiting for an answer for my question how check that the rekord is currently
edited,
Greetings,
Daniel
>
>
>*** REPLY SEPARATOR ***
>
>On 2003-08-28 at 17:42 Yudha Setiawan wrote:
>
>Of course I can lock record with syntax
>"Begin;
>select * from table1 where ID=12 for update;
>update table1 set field1="New value" where ID=12;
>commit;"
>but I can not to inform other user that the record is edited?
>
>MAIN TARGET:
>How to get information that current record is edited? Which function can I
>use?
>
>* as long as i know, the only message you could create is just
>* raise notice 'ur_message'; or raise exception 'ur_error_message';
>* and the only message that delphi could read is just raise exception.
>
>* And if you wanna make your own message, perhaps you must using try and
>except
>* and you could give your own message via ShowMessage('ur_message') or
>other
>* Class object on delphi.
>
>
>Please help me, Maybe someone have similar problem?
>Greetings,
>Daniel
>
>Hope it Help, GOD Bless You and Bravo PostgreSQL.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] lock row in table
=?iso-8859-2?Q?Daniel_Micha=B3?= <[EMAIL PROTECTED]> writes: > I have to lock one row in table1 for user1. In the same time other users sh= > ould be able to read this record but when other user for example user2 want= > to edit this record user2 should get information "The row you try to edit= > is currently edit" - or sometihing similar. > Of course I can lock record with syntax "Begin; select * from table1 where = > ID=3D12 for update; update table1 set field1=3D"New value" where ID=3D12; c= > ommit;" > but I can not to inform other user that the record is edited? Try using the contrib/userlock/ functions. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] interval conversion
Hello all! I have a possibly stupid question- I'm doing some time calculations yielding intervals, and for my purposes I need to convert the interval(say, "1 day 8 hours") into (floating point) hours. While there's a plethora of handy date_extract functionality and the like, I need a conversion. Any suggestions on how to accompish this? Simpler the better, of course. Thanks! Brett ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] interval conversion
Hello all! I have a possibly stupid question- I'm doing some time calculations yielding intervals, and for my purposes I need to convert the interval(say, "1 day 8 hours") into (floating point) hours. While there's a plethora of handy date_extract functionality and the like, I need a conversion. Any suggestions on how to accompish this? Simpler the better, of course. extract(epoch from some_interval)/3600 Regards, Tomasz Myrta ---(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] length of array
Hello Thanks for that solution, Joe - nice use of nested functions ! Related to this problem, I want to constrain a selection using elements of this variable length array. I want to constrain where all elements of the array are 0. I would like to do it like this in Oracle select field from table N where [conditions] and NVL(N.level[1],0) = 0 and NVL(N.level[2],0) = 0 and NVL(N.level[3],0) = 0 and NVL(N.level[4],0) = 0 So if a row only has two elements in the array, but the first two both had values "0", then the row would return. At the moment, I have this : and N.level[1] = 0 and N.level[2] = 0 and N.level[3] = 0 and N.level[4] = 0 but my row with 2 elements in the array won't be returned with this condition. Chris -Original Message- From: Joe Conway [mailto:[EMAIL PROTECTED] Sent: 28 August 2003 01:40 To: Chris Faulkner Cc: [EMAIL PROTECTED] Subject: Re: [SQL] length of array Chris Faulkner wrote: > Is there a function in postgres to return the length of an array field ? I > have seen array_dims(array) but this returns a character value. Ideally, I'd > like something numeric returned. > Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do this (for a one-dimensional array at least): SELECT replace(split_part(array_dims(array_fld),':',1),'[','')::int as low FROM tbl; SELECT replace(split_part(array_dims(array_fld),':',2),']','')::int as high FROM tbl; In 7.4 (now in beta) there are two new functions, array_lower() and array_upper() that do what you're looking for: regression=# select array_lower(array_fld, 1) from tbl; array_lower - 1 (1 row) regression=# select array_upper(array_fld, 1) from tbl; array_upper - 2 (1 row) See the following links for more on 7.4's array support: http://developer.postgresql.org/docs/postgres/arrays.html http://developer.postgresql.org/docs/postgres/functions-array.html http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTA X-ARRAY-CONSTRUCTORS HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] interval conversion
>> I have a possibly stupid question- I'm doing some time calculations yielding >> intervals, and for my purposes I need to convert the interval(say, "1 day 8 >> hours") into (floating point) hours. While there's a plethora of handy >> date_extract functionality and the like, I need a conversion. >> >> Any suggestions on how to accompish this? Simpler the better, of course. > > extract(epoch from some_interval)/3600 [slaps head] I swear, I studied the date/time sections of the manual for quite some time...really...Sgh :-) Thanks Tomasz! Brett ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] length of array
Chris Faulkner wrote:
I would like to do it like this in Oracle
select field from table N where [conditions]
and NVL(N.level[1],0) = 0
and NVL(N.level[2],0) = 0
and NVL(N.level[3],0) = 0
and NVL(N.level[4],0) = 0
So if a row only has two elements in the array, but the first two both had
values "0", then the row would return. At the moment, I have this :
and N.level[1] = 0
and N.level[2] = 0
and N.level[3] = 0
and N.level[4] = 0
but my row with 2 elements in the array won't be returned with this
condition.
Is this what you're looking for?
regression=# select * from t1;
id |f1
+---
1 | {1,2}
2 | {0,0,0}
3 | {0,0,0,0}
3 | {1,2,3,0}
(4 rows)
regression=# SELECT * FROM t1 WHERE COALESCE(f1[1],0) = 0 and
COALESCE(f1[2],0) = 0 and COALESCE(f1[3],0) = 0 and COALESCE(f1[4],0) = 0;
id |f1
+---
2 | {0,0,0}
3 | {0,0,0,0}
(2 rows)
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] BEFORE UPDATE Triggers
PostgreSQL Version: 7.2.3 Procedural Language: PL/pgSQL I have a table which contains a field for the user who last modified the record. Whenever a row in this table is updated, I want to have an UPDATE trigger do the following things: 1) Ensure the UPDATE query supplied a value for the action_user column 2) Log the record to an audit table so I can retrieve a change log Part 2 was trivial, however it seemed natural that if I had the following conditional in the trigger function: IF NEW.action_user ISNULL THEN ... I could raise an exception if that field was not supplied. (which would be the case if the function were triggered on an INSERT) Unfortunately it seems this is not the case. The NEW record contains values representing both the values explicitly provided with the UPDATE as well as the existing values which were not stipulated in the query. Is there any clever way around this limitation? It isn't the end of the world if I cannot verify this constraint in postgres, however it would have made it easier to ensure no one is making mistakes. Oh, and I am aware of the current_user variable. In my case this is useless as I don't care about the user at the database layer but rather at the application layer. Thanks in advance, cva ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Forcing a trigger to run
Hello all, I have several tables with triggers on them that all collectively manage a series of summary tables. In any case, there are instances where changes to a given table may cause another trigger to need to be run. What I have is an EmailAddress table, with other tables describing aliases, forwards, etc. So, if an email address is changed, the triggers on it's aliases should be run to see if they are still valid. I was thinking of putting something in the trigger for EmailAddress that would effectively say: UPDATE EmailAddressAlias SET ID=ID WHERE EmailAddressID=CurrID; but that seems like a big hack. While this would force the alias' triggers to run, it seems to me that there should be a cleaner way of doing this. Suggestions, anyone? -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ `I am so amazingly cool you could keep a side of meat in me for a month. I am so hip I have difficulty seeing over my pelvis.' ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to join from two tables at once?
Stephan Szabo wrote: Probably you want something like: SELECT u.uid, u.txt, p.val FROM u INNER JOIN a ON (a.id=u.aid) LEFT JOIN p ON (p.uid=u.uid AND p.pkey=a.pkey); From the docs: A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same as you get from listing the two items at the top level of FROM. CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. These join types are just a notational convenience, since they do nothing you couldn't do with plain FROM and WHERE. ... so obviously there *is* something that INNER JOIN can do that regular ANDs can't. But I'm still not clear why one works and the other doesn't. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
