Re: [SQL] relevance
On Thursday 17 Jul 2003 8:21 am, Terence Kearns wrote: > >>select id from tablename where message like '%sql%'; > >> > >>If there any way to determine exactly how many times 'sql' is matched in > >>that search in each particular row, and then sort by the most matches, > >>or am I going to have to write a script to do the sorting for me? > > You could probably write a function in postgres (say, "matchcount()") > which returns the match count (possibly using perl and a regex). Why reinvent the wheel when tsearch already does the job perfectly and is PostgreSQL compaitable. Regds Mallah. > > SELECT matchcount(message,'sql') AS matchcount, id > FROM tablename > WHERE message LIKE '%sql%' > ORDER BY matchcount(message,'sql') DESC > > The ORDER BY will probably fail, but you can try :) ---(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] relevance
Rajesh Kumar Mallah wrote: On Thursday 17 Jul 2003 8:21 am, Terence Kearns wrote: select id from tablename where message like '%sql%'; If there any way to determine exactly how many times 'sql' is matched in that search in each particular row, and then sort by the most matches, or am I going to have to write a script to do the sorting for me? You could probably write a function in postgres (say, "matchcount()") which returns the match count (possibly using perl and a regex). Why reinvent the wheel when tsearch already does the job perfectly and is PostgreSQL compaitable. Tsearch2 looks like it would be very useful but it's a lot of work to implement IMHO. At least it will probably (assumption) be a lot more efficient for massive amounts of data. Remember the original question was regarding how to list results in order of how many hits were returned for each matched record. -- Terence Kearns ~ ph: +61 2 6201 5516 IT Database/Applications Developer Enterprise Information Systems Client Services Division University of Canberra www.canberra.edu.au ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] parse error for function def
CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS 'DECLARE BEGIN RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool; END;' LANGUAGE 'sql'; produces this error ERROR: parser: parse error at or near "RETURN" at character 20 I'm trying to create a function to use on a trigger to check reference to views since pg does not support foreign keys referencing views. -- Terence Kearns ~ ph: +61 2 6201 5516 IT Database/Applications Developer Enterprise Information Systems Client Services Division University of Canberra www.canberra.edu.au ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] unique value - trigger?
Hi folks, I'm back with my lnumbers table again. nymr=# \d lnumbers Table "lnumbers" Column | Type | Modifiers ---+---+--- lnid | integer | not null lnumber | character varying(10) | not null lncurrent | boolean | Primary key: lnumbers_pkey Triggers: RI_ConstraintTrigger_7575462 While each loco can have a number of different numbers, only one can be current at any one time. I want to make it so that if I set lncurrent to true for one row, any existing true rows are set to false. I'm guessing that I need to create a trigger to be actioned after an insert or update which would update set lncurrent=false where lnid not = but I can't seem to sus it put. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] Is it possible to connect to another database
Hi, I try to find how is it possible to connect 2 databases, with a symbolic link. I have to use tables in another database to test user or other information. Ben ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Is it possible to connect to another database
Take a look at dblink in the contrib directory... This may do what you need adam > Hi, > > I try to find how is it possible to connect 2 databases, with a symbolic > link. > > I have to use tables in another database to test user or other information. > > > Ben > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(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] Is it possible to connect to another database
$db_conn1 = pg_connect("dbname=db1");
$db_conn2 = pg_connect("dbname=db2");
.
You can't join two tables from different databases(, as far as i know :).
- Original Message -
From: "BenLaKnet" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 17, 2003 5:03 PM
Subject: [SQL] Is it possible to connect to another database
> Hi,
>
> I try to find how is it possible to connect 2 databases, with a symbolic
> link.
>
> I have to use tables in another database to test user or other
information.
>
>
> Ben
>
>
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] parse error for function def
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I'm trying to create a function to use on a trigger to check reference > to views since pg does not support foreign keys referencing views. Can you explain exactly what you are trying to do and why? You are getting the error because a SQL function does not RETURN, it must end with a SELECT statement. It also has no DECLARE, BEGIN, or END. You can either remove all of those or change the language to plpgsql. See: http://www.postgresql.org/docs/7.3/static/xfunc-sql.html In addition, you cannot (with SQL) use an argument as the tablename. You also probably want to use EXISTS, not "count..::bool". - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200307171005 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE/Fq/7vJuQZxSWSsgRAj01AKCz9BA4aYrp8pnqWy8VHA4i3WGjtgCgjndA yzNOE52VAvJBOEvilACSGvA= =EcwZ -END PGP SIGNATURE- ---(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] parse error for function def
Terence Kearns wrote: CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS 'DECLARE BEGIN RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool; END;' LANGUAGE 'sql'; produces this error ERROR: parser: parse error at or near "RETURN" at character 20 I'm trying to create a function to use on a trigger to check reference to views since pg does not support foreign keys referencing views. First, you are using wrong language. In 'sql' you do not need begin, end or return. Second, you cannot have trigger functions in sql anyway, so you'd need to change your language to 'plpgsql' - it may than even compile, but I am not sure, because I never used that language. Third, trigger functions are special in that they can only take constant strings as arguments, so your $1 = $3 is, most probably not going to work. They also must return 'opaque' (in 7.2) or 'triggers' (in 7.3) - you can't return bool, because there is nobody who'd be able to look at the result after the function is called. Instead, you should check your condition, and if it is not satisfied, raise an error to abort the transaction. Fourth, select count ... may not be very efficient if you just need to check if the key exists - you may be better off with select true ... limit 1; And finally, you can (relatively easily) write a function that will check if the key exists in the view whenever you insert/update the table... But what about the other way around - what if somebody deletes a key from the underlying table in the view while there is still referencing entries on the other table? You can't have a trigger on a view, so there would be no way to check that... Why not avoid all that by just creating an FK between the actual table(s), used by the view and the 'child' table you care about? Dima ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] NOT and AND problem
Dear All, I am having some confusion over a query which is supposed to achieve the following: To remove a record from a table if the one or both of the columns containing references to other tables no longer point to table rows which still exist. There are good reasons why I cannot use foreign keys to maintain referential integrity, but I will not go into them, but they give rise to the need to "clean-up" my database table from time to time. The query that I have had most success with looks like this: DELETE FROM myTable WHERE (NOT myTable.item_id = item.item_id) AND (NOT myTable.group_id = ep.group_id); Which is odd, because logically it shouldn't work. What I find with the above queries is that as follows: let myTable.item_id = item.item_id be A let myTable.group_id = ep.group_id be B The derived and actual truth tables for the results of the where clause follow: Derived: A | B | Result 1 | 1 | 0 1 | 0 | 0 0 | 1 | 0 0 | 0 | 1 Actual: A | B | Result 1 | 1 | 0 1 | 0 | 0 0 | 1 | 1 0 | 0 | 1 This makes no sense to me, as effectively rows 2 and 3 of the Actual results truth table are the same (unless there's some subtle difference with regards to the order of the statements, otherwise just substitute A for B and vice versa). The result that I actually want from the operation is this: A | B | Result 1 | 1 | 0 1 | 0 | 1 0 | 1 | 1 0 | 0 | 1 which would suggest a query like: DELETE FROM myTable WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = ep.group_id); which ought to provide the above output. Instead, using this query, the output I get is as follows: A | B | Result 1 | 1 | 1 1 | 0 | 1 0 | 1 | 1 0 | 0 | 1 I can only conclude that Postgres is doing something with regards to the other two tables which I am unaware of. Can anyone help me understand what is going on? Any suggestions gratefully received. Cheers Richard Richard Jones --- Systems Developer Theses Alive! - www.thesesalive.ac.uk Edinburgh University Library [EMAIL PROTECTED] 0131 651 1611 ---(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] unique value - trigger?
Gary Stainburn wrote: Hi folks, I'm back with my lnumbers table again. nymr=# \d lnumbers Table "lnumbers" Column | Type | Modifiers ---+---+--- lnid | integer | not null lnumber | character varying(10) | not null lncurrent | boolean | Primary key: lnumbers_pkey Triggers: RI_ConstraintTrigger_7575462 While each loco can have a number of different numbers, only one can be current at any one time. I want to make it so that if I set lncurrent to true for one row, any existing true rows are set to false. I'm guessing that I need to create a trigger to be actioned after an insert or update which would update set lncurrent=false where lnid not = Why "not"? I thought, you wanted just the opposite - update the ones that *do* have the same lnid? I'd also recommend you to add ' and lncurrent' to the query - otherwise every insert would be updating *every* row with the same lnid (it doesn't check if the new row is actually the same as the old one) before updating, and that may be expensive. You may also want to create a pratial index on lnumbers (lnid) where lncurrent to speed up your trigger but I can't seem to sus it put. What is the problem? Dima ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] NOT and AND problem
- Original Message - From: "Richard Jones" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, July 17, 2003 5:29 PM Subject: [SQL] NOT and AND problem > Dear All, > > I am having some confusion over a query which is supposed to achieve the > following: To remove a record from a table if the one or both of the > columns containing references to other tables no longer point to table rows > which still exist. There are good reasons why I cannot use foreign keys to > maintain referential integrity, but I will not go into them, but they give > rise to the need to "clean-up" my database table from time to time. The > query that I have had most success with looks like this: > > DELETE FROM myTable > WHERE (NOT myTable.item_id = item.item_id) > AND (NOT myTable.group_id = ep.group_id); > > Which is odd, because logically it shouldn't work. What I find with the > above queries is that as follows: > > let myTable.item_id = item.item_id be A > let myTable.group_id = ep.group_id be B > > The derived and actual truth tables for the results of the where clause > follow: > > Derived: > A | B | Result > 1 | 1 | 0 > 1 | 0 | 0 > 0 | 1 | 0 > 0 | 0 | 1 > > Actual: > A | B | Result > 1 | 1 | 0 > 1 | 0 | 0 > 0 | 1 | 1 > 0 | 0 | 1 > > This makes no sense to me, as effectively rows 2 and 3 of the Actual results > truth table are the same (unless there's some subtle difference with regards > to the order of the statements, otherwise just substitute A for B and vice > versa). > > The result that I actually want from the operation is this: > > A | B | Result > 1 | 1 | 0 > 1 | 0 | 1 > 0 | 1 | 1 > 0 | 0 | 1 > > which would suggest a query like: > > DELETE FROM myTable > WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = > ep.group_id); If u want this u can obtain by DELETE FROM myTable WHERE (NOT(myTable.item_id = item.item_id )) OR (NOT(myTable.group_id = ep.group_id)); You can write ! instead of NOT. Look at the operations precedence. The NOT might get executed before "=". > > which ought to provide the above output. Instead, using this query, the > output I get is as follows: > > A | B | Result > 1 | 1 | 1 > 1 | 0 | 1 > 0 | 1 | 1 > 0 | 0 | 1 > > I can only conclude that Postgres is doing something with regards to the > other two tables which I am unaware of. Can anyone help me understand what > is going on? Any suggestions gratefully received. > > Cheers > > Richard > > > Richard Jones > --- > Systems Developer > Theses Alive! - www.thesesalive.ac.uk > Edinburgh University Library > [EMAIL PROTECTED] > 0131 651 1611 > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [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
Re: [SQL] NOT and AND problem
DELETE FROM myTable WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id) AND NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id); Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Richard Jones > Sent: Thursday, July 17, 2003 10:29 AM > To: [EMAIL PROTECTED] > Subject: [SQL] NOT and AND problem > > > Dear All, > > I am having some confusion over a query which is supposed to > achieve the > following: To remove a record from a table if the one or both of the > columns containing references to other tables no longer point > to table rows > which still exist. There are good reasons why I cannot use > foreign keys to > maintain referential integrity, but I will not go into them, > but they give > rise to the need to "clean-up" my database table from time to > time. The > query that I have had most success with looks like this: > > DELETE FROM myTable > WHERE (NOT myTable.item_id = item.item_id) > AND (NOT myTable.group_id = ep.group_id); > > Which is odd, because logically it shouldn't work. What I > find with the > above queries is that as follows: > > let myTable.item_id = item.item_id be A > let myTable.group_id = ep.group_id be B > > The derived and actual truth tables for the results of the > where clause > follow: > > Derived: > A | B | Result > 1 | 1 | 0 > 1 | 0 | 0 > 0 | 1 | 0 > 0 | 0 | 1 > > Actual: > A | B | Result > 1 | 1 | 0 > 1 | 0 | 0 > 0 | 1 | 1 > 0 | 0 | 1 > > This makes no sense to me, as effectively rows 2 and 3 of the > Actual results > truth table are the same (unless there's some subtle > difference with regards > to the order of the statements, otherwise just substitute A > for B and vice > versa). > > The result that I actually want from the operation is this: > > A | B | Result > 1 | 1 | 0 > 1 | 0 | 1 > 0 | 1 | 1 > 0 | 0 | 1 > > which would suggest a query like: > > DELETE FROM myTable > WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = > ep.group_id); > > which ought to provide the above output. Instead, using this > query, the > output I get is as follows: > > A | B | Result > 1 | 1 | 1 > 1 | 0 | 1 > 0 | 1 | 1 > 0 | 0 | 1 > > I can only conclude that Postgres is doing something with > regards to the > other two tables which I am unaware of. Can anyone help me > understand what > is going on? Any suggestions gratefully received. > > Cheers > > Richard > > > Richard Jones > --- > Systems Developer > Theses Alive! - www.thesesalive.ac.uk > Edinburgh University Library > [EMAIL PROTECTED] > 0131 651 1611 > > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] NOT and AND problem
I can't help you explain what is going on with this query - like you, I am puzzled by the fact that it actually works, and have no idea how it is interpreted, and what it is doing... The right way to do what you want, I think, would be something like: delete from mytable where not exists (select 1 from item where item_id = mytable.item_id) or not exists (select 1 from ep where group_id=mytable.group_id); I replaced your AND with OR, because that's what you seem to be saying in the description of your problem... I hope, it helps.. Dima Richard Jones wrote: Dear All, I am having some confusion over a query which is supposed to achieve the following: To remove a record from a table if the one or both of the columns containing references to other tables no longer point to table rows which still exist. There are good reasons why I cannot use foreign keys to maintain referential integrity, but I will not go into them, but they give rise to the need to "clean-up" my database table from time to time. The query that I have had most success with looks like this: DELETE FROM myTable WHERE (NOT myTable.item_id = item.item_id) AND (NOT myTable.group_id = ep.group_id); Which is odd, because logically it shouldn't work. What I find with the above queries is that as follows: let myTable.item_id = item.item_id be A let myTable.group_id = ep.group_id be B The derived and actual truth tables for the results of the where clause follow: Derived: A | B | Result 1 | 1 | 0 1 | 0 | 0 0 | 1 | 0 0 | 0 | 1 Actual: A | B | Result 1 | 1 | 0 1 | 0 | 0 0 | 1 | 1 0 | 0 | 1 This makes no sense to me, as effectively rows 2 and 3 of the Actual results truth table are the same (unless there's some subtle difference with regards to the order of the statements, otherwise just substitute A for B and vice versa). The result that I actually want from the operation is this: A | B | Result 1 | 1 | 0 1 | 0 | 1 0 | 1 | 1 0 | 0 | 1 which would suggest a query like: DELETE FROM myTable WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = ep.group_id); which ought to provide the above output. Instead, using this query, the output I get is as follows: A | B | Result 1 | 1 | 1 1 | 0 | 1 0 | 1 | 1 0 | 0 | 1 I can only conclude that Postgres is doing something with regards to the other two tables which I am unaware of. Can anyone help me understand what is going on? Any suggestions gratefully received. Cheers Richard Richard Jones --- Systems Developer Theses Alive! - www.thesesalive.ac.uk Edinburgh University Library [EMAIL PROTECTED] 0131 651 1611 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] NOT and AND problem
And after reading Viorel's response I realized that you wanted the record deleted if EITHER "foreign key" was broken, not just if BOTH fkeys are broken, therefore simply change the AND to an OR: DELETE FROM myTable WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id) OR NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id); Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of > [EMAIL PROTECTED] > Sent: Thursday, July 17, 2003 10:29 AM > To: 'Richard Jones'; [EMAIL PROTECTED] > Subject: Re: [SQL] NOT and AND problem > > > DELETE FROM myTable > WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id) > AND NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id); > > Terry Fielder > Manager Software Development and Deployment > Great Gulf Homes / Ashton Woods Homes > [EMAIL PROTECTED] > Fax: (416) 441-9085 > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] Behalf Of Richard Jones > > Sent: Thursday, July 17, 2003 10:29 AM > > To: [EMAIL PROTECTED] > > Subject: [SQL] NOT and AND problem > > > > > > Dear All, > > > > I am having some confusion over a query which is supposed to > > achieve the > > following: To remove a record from a table if the one or > both of the > > columns containing references to other tables no longer point > > to table rows > > which still exist. There are good reasons why I cannot use > > foreign keys to > > maintain referential integrity, but I will not go into them, > > but they give > > rise to the need to "clean-up" my database table from time to > > time. The > > query that I have had most success with looks like this: > > > > DELETE FROM myTable > > WHERE (NOT myTable.item_id = item.item_id) > > AND (NOT myTable.group_id = ep.group_id); > > > > Which is odd, because logically it shouldn't work. What I > > find with the > > above queries is that as follows: > > > > let myTable.item_id = item.item_id be A > > let myTable.group_id = ep.group_id be B > > > > The derived and actual truth tables for the results of the > > where clause > > follow: > > > > Derived: > > A | B | Result > > 1 | 1 | 0 > > 1 | 0 | 0 > > 0 | 1 | 0 > > 0 | 0 | 1 > > > > Actual: > > A | B | Result > > 1 | 1 | 0 > > 1 | 0 | 0 > > 0 | 1 | 1 > > 0 | 0 | 1 > > > > This makes no sense to me, as effectively rows 2 and 3 of the > > Actual results > > truth table are the same (unless there's some subtle > > difference with regards > > to the order of the statements, otherwise just substitute A > > for B and vice > > versa). > > > > The result that I actually want from the operation is this: > > > > A | B | Result > > 1 | 1 | 0 > > 1 | 0 | 1 > > 0 | 1 | 1 > > 0 | 0 | 1 > > > > which would suggest a query like: > > > > DELETE FROM myTable > > WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = > > ep.group_id); > > > > which ought to provide the above output. Instead, using this > > query, the > > output I get is as follows: > > > > A | B | Result > > 1 | 1 | 1 > > 1 | 0 | 1 > > 0 | 1 | 1 > > 0 | 0 | 1 > > > > I can only conclude that Postgres is doing something with > > regards to the > > other two tables which I am unaware of. Can anyone help me > > understand what > > is going on? Any suggestions gratefully received. > > > > Cheers > > > > Richard > > > > > > Richard Jones > > --- > > Systems Developer > > Theses Alive! - www.thesesalive.ac.uk > > Edinburgh University Library > > [EMAIL PROTECTED] > > 0131 651 1611 > > > > > > ---(end of > > broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to > > [EMAIL PROTECTED]) > > > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] NOT and AND problem
Hello, Just to say thanks for such fast responses. The full working query is indeed as suggested (I cut the demo query down when I posted it, as it's got some awfully long names in it in real life): DELETE FROM eulepersongroup2workspaceitem WHERE NOT EXISTS ( SELECT 1 FROM workspaceitem WHERE workspace_item_id = eulepersongroup2workspaceitem.workspace_item_id ) OR NOT EXISTS ( SELECT 1 FROM epersongroup WHERE eperson_group_id = eulepersongroup2workspaceitem.eperson_group_id ); Thanks very much for your help. Regards Richard Richard Jones --- Systems Developer Theses Alive! - www.thesesalive.ac.uk Edinburgh University Library [EMAIL PROTECTED] 0131 651 1611 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] parse error for function def
On Thu, 17 Jul 2003, Terence Kearns wrote: > CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS > 'DECLARE > BEGIN >RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool; > END;' > LANGUAGE 'sql'; > > produces this error > ERROR: parser: parse error at or near "RETURN" at character 20 > > I'm trying to create a function to use on a trigger to check reference > to views since pg does not support foreign keys referencing views. As others have said, the function above is much closer to a plpgsql function, excepting that you still couldn't use $2 in the from clause without execute I believe. In addition, the above isn't going to simulate a foreign key unless the view is entirely static (if it were that simple, we'd have implemented it) since changes to the view's base table(s) could make the constraint be invalid as well. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] unique value - trigger?
On Thursday 17 Jul 2003 3:34 pm, Dmitry Tkach wrote: > Gary Stainburn wrote: > >Hi folks, > > > >I'm back with my lnumbers table again. > > > >nymr=# \d lnumbers > > Table "lnumbers" > > Column | Type | Modifiers > >---+---+--- > > lnid | integer | not null > > lnumber | character varying(10) | not null > > lncurrent | boolean | > >Primary key: lnumbers_pkey > >Triggers: RI_ConstraintTrigger_7575462 > > > >While each loco can have a number of different numbers, only one can be > >current at any one time. > > > >I want to make it so that if I set lncurrent to true for one row, any > > existing true rows are set to false. > > > >I'm guessing that I need to create a trigger to be actioned after an > > insert or update which would update set lncurrent=false where lnid not = > > > > Why "not"? I thought, you wanted just the opposite - update the ones > that *do* have the same lnid? > I'd also recommend you to add ' and lncurrent' to the query - otherwise > every insert would be updating *every* row with the same lnid (it > doesn't check if the new row is actually the same as the old one) before > updating, and that may be expensive. > You may also want to create a pratial index on lnumbers (lnid) where > lncurrent to speed up your trigger All good and valid points. > > >but I can't seem to sus it put. > > What is the problem? The problem is I don't know how to convert the following pseudo code to valid SQL: create trigger unique_current on insert/update to lnumbers if new.lncurrent = true update lnumbers set all other records for this loco to false > > Dima > > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] unique value - trigger?
The problem is I don't know how to convert the following pseudo code to valid SQL: create trigger unique_current on insert/update to lnumbers if new.lncurrent = true update lnumbers set all other records for this loco to false I see... The bad news is you can't do it directly... You can only create functions to run as triggers, not plain sql statements for some reason :-( The correct syntax is create trigger unique_current before insert or update on lnumbers for each row execute procedure reset_current_lnum(); Where reset_current_lnum () is a function, that you have to write either in "C" or in 'plpgsql'; I could give you some sample code in "C" to do that, but it's rather complicated if you are not used to writing postgres stored procs in C... plpgsql would be much easier, but I can't help you there, because I don't know the syntax ... something like this, I guess, but I doubt this will compile exactly as it is: create function reset_current_lnum () returns triggers as ' begin if new.lncurrent = true update lnumbers set lncurrent=false where lnid=new.lnid and lncurrent; endif return new; end;' language 'plpgsql'; Dima ---(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] unique value - trigger?
n Thu, Jul 17, 2003 at 12:56:52PM +0100, Gary Stainburn wrote: > > nymr=# \d lnumbers >Table "lnumbers" > Column | Type | Modifiers > ---+---+--- > lnid | integer | not null > lnumber | character varying(10) | not null > lncurrent | boolean | > Primary key: lnumbers_pkey > Triggers: RI_ConstraintTrigger_7575462 > > I want to make it so that if I set lncurrent to true for one row, any existing > true rows are set to false. > > I'm guessing that I need to create a trigger to be actioned after an insert or > update which would update set lncurrent=false where lnid not = Absolutely. Something like this will work: CREATE FUNCTION lnumbers_current_trigger() RETURNS TRIGGER AS ' BEGIN IF NEW.lncurrent THEN UPDATE lnumbers SET lncurrent = ''f'' WHERE lnid = NEW.lnid AND lnumber != NEW.lnumber AND lncurrent = ''t''; END IF; RETURN NEW; END' LANGUAGE 'plpgsql'; CREATE TRIGGER lnumbers_current AFTER UPDATE OR INSERT ON lnumbers FOR EACH ROW EXECUTE PROCEDURE lnumbers_current_trigger(); (Lightly tested only on 7.3.3) In the WHERE condition in the function, specifying "lncurrent = 't'" means that we don't update more rows than we have to. Making the trigger fire AFTER INSERT as well as AFTER UPDATE means that if you just add a new row with lncurrent set to true, it Does The Right Thing. In this particular example, the trigger will work perfectly well as a BEFORE, also. If you can't or don't want to install PL/PgSQL (or some other procedural language), you can do it with rules. It's more long-winded that way, although I also think it's more elegant... Richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Table Partitioning and Rules
Hello All,
I have a set of tables partitioned horizontally. DML below.
Essentially Im trying to store a persons information in a table in the
database. Since we could have millions of people, with duplicates! Ive decided
we need to partition the table into segments where all people with the LastName
starting from A to G will be in one table. H-N will be in another table and O-Z
in the third. Ive created a VIEW that does a UNION on all the tables.
Now, Im trying to create an INSERT and UPDATE RULE on the VIEW that would
direct an insert or update into the appropriate partition table depending on
the person LastName.
I cant seem to figure this out. Does anyone know how to do this?
Thanks,
Girish
-- Table: public."contact_A_G"
CREATE TABLE public."contact_A_G" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_A_G_pkey" PRIMARY KEY
("CONTACTID")
) WITH OIDS;
-- Table: public."contact_H_N"
CREATE TABLE public."contact_H_N" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_H_N_pkey" PRIMARY KEY
("CONTACTID")
) WITH OIDS;
-- Table: public."contact_O_Z"
CREATE TABLE public."contact_O_Z" (
"CONTACTID" int8 NOT NULL,
"LastName" varchar(50),
"FirstName" varchar(50),
CONSTRAINT "contact_O_Z_pkey" PRIMARY KEY
("CONTACTID")
) WITH OIDS;
CREATE VIEW Contact AS
SELECT * FROM "Contact_A_G"
UNION
SELECT * FROM "Contact_H_M"
UNION
SELECT * FROM "Contact_N_Z";
Re: [SQL] Recursive request ...
BenLaKnet wrote: I see connect by in Oracle ??? is there an equivalent in PostgreSQL or not ?? Someone is working on the SQL99 equivalent, but it isn't done yet. Perhaps for 7.5. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] OR vs UNION
Hello, I have a query that uses a series of ORs and I have heard that sometimes this type of query can be rewritten to use UNION instead and be more efficient. Are there any rules of thumb for when this might be the case? As an example here is a query of the type I am discussing: select distinct f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id from feature f, featureloc fl where (f.type_id = 219 OR f.type_id = 368 OR f.type_id = 514 OR f.type_id = 475 OR f.type_id = 426 OR f.type_id = 456 OR f.type_id = 461 OR f.type_id = 553 OR f.type_id = 89) and fl.srcfeature_id = 1 and f.feature_id = fl.feature_id and fl.fmin <= 2491413 and fl.fmax >= 2485521 which could easily be rewritten as a set of select statements for each type_id and then union them together. For this particular query, explain analyze indicates that this is the more efficient form, but I could easily see that at other times/for other parameters, a set unioned together would be better. Are there any guidelines for this? Thanks, Scott -- Scott Cain, Ph. D. [EMAIL PROTECTED] GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Table Partitioning and Rules
On Thursday 17 Jul 2003 6:20 pm, Girish Bajaj wrote: > Hello All, > > I have a set of tables partitioned horizontally. DML below. > > Essentially Im trying to store a persons information in a table in the > database. Since we could have millions of people, with duplicates! Ive > decided we need to partition the table into segments where all people with > the LastName starting from A to G will be in one table. H-N will be in > another table and O-Z in the third. Ive created a VIEW that does a UNION on > all the tables. I'd not bother - databases are good at managing large amounts of information. If you really need to you can set up multiple partial indexes: CREATE INDEX my_index_a ON contact (LastName) WHERE LastName>='A' AND LastName<'B' CREATE INDEX my_index_b ON contact (LastName) WHERE LastName>='B' AND LastName<'C' etc. > Now, Im trying to create an INSERT and UPDATE RULE on the VIEW that would > direct an insert or update into the appropriate partition table depending > on the person LastName. > > I cant seem to figure this out. Does anyone know how to do this? Something like: CREATE RULE ... ON INSERT TO Contact WHERE NEW.LastName>='A' AND NEW.LastName<'H' DO INSTEAD ...insert into correct table here... You'll want to read the chapter on the rule system in the "Server Programming" section of the manual. -- Richard Huxton ---(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] OR vs UNION
Scott, > I have a query that uses a series of ORs and I have heard that sometimes > this type of query can be rewritten to use UNION instead and be more > efficient. I'd be interested to know where you heard that; as far as I know, it could only apply to conditional left outer joins. > select distinct f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id > from feature f, featureloc fl > where >(f.type_id = 219 OR > f.type_id = 368 OR > f.type_id = 514 OR > f.type_id = 475 OR > f.type_id = 426 OR > f.type_id = 456 OR > f.type_id = 461 OR > f.type_id = 553 OR > f.type_id = 89) and > fl.srcfeature_id = 1 and > f.feature_id = fl.feature_id and > fl.fmin <= 2491413 and fl.fmax >= 2485521 Certainly a query of the above form would not benefit from being a union. For readability, you could use an IN() statement rather than a bunch of ORs ... this would not help performance, but would make your query easier to type/read. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Table Partitioning and Rules
Girish, > > Essentially Im trying to store a persons information in a table in the > > database. Since we could have millions of people, with duplicates! Ive > > decided we need to partition the table into segments where all people with > > the LastName starting from A to G will be in one table. H-N will be in > > another table and O-Z in the third. Ive created a VIEW that does a UNION on > > all the tables. This sounds hideously inefficient and a management headache besides. I think PostgreSQL will accept up to 2 billion rows in any one table, and splitting stuff into 3 tables will not improve your performance ... quite the opposite. Change your database design. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] OR vs UNION
On Thu, 2003-07-17 at 15:00, Josh Berkus wrote: > Scott, > > > I have a query that uses a series of ORs and I have heard that sometimes > > this type of query can be rewritten to use UNION instead and be more > > efficient. > > I'd be interested to know where you heard that; as far as I know, it could > only apply to conditional left outer joins. Hmm, don't know for sure where I heard it, however I do know from previous experience that unioned queries worked better in a somewhat similar query, though it was a different schema, so it is hard to compare directly. One way in which I thought it might make a difference is if I build partial indexes on feature_id for each of the type_ids of interest (there are several thousand in the database, but only 15 or 20 that I am interested in querying). That way, when I write the separate queries for each type_id, the query planner would have access to the partial indexes for each type, and therefore may be able to complete the individual queries very quickly. > > > select distinct f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id > > from feature f, featureloc fl > > where > >(f.type_id = 219 OR > > f.type_id = 368 OR > > f.type_id = 514 OR > > f.type_id = 475 OR > > f.type_id = 426 OR > > f.type_id = 456 OR > > f.type_id = 461 OR > > f.type_id = 553 OR > > f.type_id = 89) and > > fl.srcfeature_id = 1 and > > f.feature_id = fl.feature_id and > > fl.fmin <= 2491413 and fl.fmax >= 2485521 > > Certainly a query of the above form would not benefit from being a union. > > For readability, you could use an IN() statement rather than a bunch of ORs > ... this would not help performance, but would make your query easier to > type/read. -- Scott Cain, Ph. D. [EMAIL PROTECTED] GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Table Partitioning and Rules
On Thu, 2003-07-17 at 19:03, Josh Berkus wrote: > Girish, > > > > Essentially Im trying to store a persons information in a table in the > > > database. Since we could have millions of people, with duplicates! Ive > > > decided we need to partition the table into segments where all people with > > > the LastName starting from A to G will be in one table. H-N will be in > > > another table and O-Z in the third. Ive created a VIEW that does a UNION > on > > > all the tables. > > This sounds hideously inefficient and a management headache besides. I think > PostgreSQL will accept up to 2 billion rows in any one table, and splitting > stuff into 3 tables will not improve your performance ... quite the opposite. PostgreSQL will go well beyond 2 billion rows in a table. It just becomes difficult to use OIDs. signature.asc Description: This is a digitally signed message part
Re: [SQL] Table Partitioning and Rules
The problem is that Im worried about sequential scans. This particular table can have upto 150 cols and 250 million records. Now we have a reporting requirement that someone could select on ANY col and filter on any col as well. Meaning someone could so a SELECT on col number 1,2,310,1000 from contact where col num 75='X' and col num 139 = 'Y'. I cant possibly index all the cols in the table. So I thought Id best manage the data by splitting up the table into multiple partitions and eventually depending on application logic, only scan those tables that are necessary to scan sequentially instead of the whole big table. Im getting a little confused here cause eventually I would want to join in this 250 million gigantic table as well.. and that would be a real big problem causing loads of sequential scans wouldn't it? Thanks, Girish -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, July 17, 2003 2:03 PM To: Richard Huxton; Girish Bajaj; [EMAIL PROTECTED] Subject: Re: [SQL] Table Partitioning and Rules Girish, > > Essentially Im trying to store a persons information in a table in the > > database. Since we could have millions of people, with duplicates! Ive > > decided we need to partition the table into segments where all people with > > the LastName starting from A to G will be in one table. H-N will be in > > another table and O-Z in the third. Ive created a VIEW that does a UNION on > > all the tables. This sounds hideously inefficient and a management headache besides. I think PostgreSQL will accept up to 2 billion rows in any one table, and splitting stuff into 3 tables will not improve your performance ... quite the opposite. Change your database design. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] OR vs UNION
Josh Berkus <[EMAIL PROTECTED]> writes: > Certainly a query of the above form would not benefit from being a union. Actually we used to have code in the planner that would automatically transform an OR query to a UNION ALL construct (the old "ksqo" option). It fell into disfavor, partly because it didn't really preserve semantics exactly --- IIRC, should you mention the same field value more than once in the OR, the UNION ALL version would generate duplicate output rows. At the time it was put in, it offered significant performance wins, but subsequent planner improvements narrowed the gap to nearly nothing, and we eventually took it out. I'm not sure whether there's any possible win given the current state of the planner. If you dig for "ksqo" in the archives you can find all the details (at least on days when the search engine is working ;-)). regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Table Partitioning and Rules
Girish, > I cant possibly index all the cols in the table. Why not? Seriously, what you can do is use query logging to log the columns and combinations of columns that users most frequently ask for. Then you can index for those queries. Your partitioning scheme assumes that users will be searching on the last name most of the time, so I'd start with that. > Im getting a little confused here cause eventually I would want to join in > this 250 million gigantic table as well.. and that would be a real big > problem causing loads of sequential scans wouldn't it? Yes, it would. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Table Partitioning and Rules
OK, so basically you are trying to keep a hundered some odd attributes on everyone in the US. It's possible that a 150 column table is properly normalized (I have a similar situation) but it is rare. Suppose it is really properly normalized. You can still benefit from indexes on just some of the columns by choosing those most commonly used in queries. You may also want to research partial indexes (create index foo ... where bar=baz) which can under certain circumstances be far smaller and faster than full indexes. Breaking the table up won't improve the full table scan - in fact as each sub-table's file grows it will probably fragment on the disk much worse than a single growing file would which will, along with all the overhead of joining all the tables, make things worse. Review your structure carefully. Plan on $$$ for the hardware. Cheers, Steve On Thursday 17 July 2003 12:22 pm, Girish Bajaj wrote: > The problem is that Im worried about sequential scans. This particular > table can have upto 150 cols and 250 million records. Now we have a > reporting requirement that someone could select on ANY col and filter on > any col as well. Meaning someone could so a SELECT on col number > 1,2,310,1000 from contact where col num 75='X' and col num 139 = 'Y'. > > I cant possibly index all the cols in the table. So I thought Id best > manage the data by splitting up the table into multiple partitions and > eventually depending on application logic, only scan those tables that are > necessary to scan sequentially instead of the whole big table. > > Im getting a little confused here cause eventually I would want to join in > this 250 million gigantic table as well.. and that would be a real big > problem causing loads of sequential scans wouldn't it? > > Thanks, > Girish > > > -Original Message- > From: Josh Berkus [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 17, 2003 2:03 PM > To: Richard Huxton; Girish Bajaj; [EMAIL PROTECTED] > Subject: Re: [SQL] Table Partitioning and Rules > > Girish, > > > > Essentially Im trying to store a persons information in a table in the > > > database. Since we could have millions of people, with duplicates! Ive > > > decided we need to partition the table into segments where all people > > with > > > > the LastName starting from A to G will be in one table. H-N will be in > > > another table and O-Z in the third. Ive created a VIEW that does a > > > UNION > > on > > > > all the tables. > > This sounds hideously inefficient and a management headache besides. I > think > PostgreSQL will accept up to 2 billion rows in any one table, and splitting > stuff into 3 tables will not improve your performance ... quite the > opposite. > > Change your database design. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Table Partitioning and Rules
But what makes you think, that it is quicker to scan 10 tables with 25 million rows each than it would be to scan one table with 250 million rows? It won't... In fact, it will be *longer*. If you have a way to narrow the number of tables to scan down based on the condition, you can have that logic implemented with partial indices, as it was suggested earlier in this thread... Dima Girish Bajaj wrote: The problem is that Im worried about sequential scans. This particular table can have upto 150 cols and 250 million records. Now we have a reporting requirement that someone could select on ANY col and filter on any col as well. Meaning someone could so a SELECT on col number 1,2,310,1000 from contact where col num 75='X' and col num 139 = 'Y'. I cant possibly index all the cols in the table. So I thought Id best manage the data by splitting up the table into multiple partitions and eventually depending on application logic, only scan those tables that are necessary to scan sequentially instead of the whole big table. Im getting a little confused here cause eventually I would want to join in this 250 million gigantic table as well.. and that would be a real big problem causing loads of sequential scans wouldn't it? Thanks, Girish -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, July 17, 2003 2:03 PM To: Richard Huxton; Girish Bajaj; [EMAIL PROTECTED] Subject: Re: [SQL] Table Partitioning and Rules Girish, Essentially Im trying to store a persons information in a table in the database. Since we could have millions of people, with duplicates! Ive decided we need to partition the table into segments where all people with the LastName starting from A to G will be in one table. H-N will be in another table and O-Z in the third. Ive created a VIEW that does a UNION on all the tables. This sounds hideously inefficient and a management headache besides. I think PostgreSQL will accept up to 2 billion rows in any one table, and splitting stuff into 3 tables will not improve your performance ... quite the opposite. Change your database design. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] OR vs UNION
Actually, I have used a UNION to replace OR's, the case (simpliefied to) something like this: Sample 1: WHERE (f1 = 'v1' OR f1 = '') AND (f2 = 'v2' OR f2 = '') Changed to Sample 2: WHERE (f1 = 'v1') AND (f2 = 'v2') UNION WHERE (f1 = 'v1') AND (f2 = '') UNION WHERE (f1 = '') AND (f2 = '') Note that Sample 1 is actually a simplified version, the queries are not exactly equivalent. The point is that sample 2 ran MUCH faster because: a) The table was *very* large b) The OR clauses of sample 1 prevented the use of an INDEX, Reason: It is faster to scan an index 3 times then scan this very large table once. I do not know if there is a proof to say that one can *always* replace OR's with a union, but sometimes certainly, and in this case it made things much better... Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Josh Berkus > Sent: Thursday, July 17, 2003 3:00 PM > To: Scott Cain; [EMAIL PROTECTED] > Subject: Re: [SQL] OR vs UNION > > > Scott, > > > I have a query that uses a series of ORs and I have heard > that sometimes > > this type of query can be rewritten to use UNION instead and be more > > efficient. > > I'd be interested to know where you heard that; as far as I > know, it could > only apply to conditional left outer joins. > > > select distinct > f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id > > from feature f, featureloc fl > > where > >(f.type_id = 219 OR > > f.type_id = 368 OR > > f.type_id = 514 OR > > f.type_id = 475 OR > > f.type_id = 426 OR > > f.type_id = 456 OR > > f.type_id = 461 OR > > f.type_id = 553 OR > > f.type_id = 89) and > > fl.srcfeature_id = 1 and > > f.feature_id = fl.feature_id and > > fl.fmin <= 2491413 and fl.fmax >= 2485521 > > Certainly a query of the above form would not benefit from > being a union. > > For readability, you could use an IN() statement rather than > a bunch of ORs > ... this would not help performance, but would make your > query easier to > type/read. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] parse error for function def
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 17 July 2003 03:46, Terence Kearns wrote: > CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS > 'DECLARE > BEGIN >RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool; > END;' > LANGUAGE 'sql'; > > produces this error > ERROR: parser: parse error at or near "RETURN" at character 20 > > I'm trying to create a function to use on a trigger to check reference > to views since pg does not support foreign keys referencing views. If you want to write triggers, there is great documentation on that in the PL/pgSQL documentation. I'll give this a shot: CREATE FUNCTION base.fn_fkey_check(name, name, name, name) RETURNS TRIGGER AS ' BEGIN " $1 column1 (the table that has the foreign key column) " $2 - table2 $3 - column2 (the table/column that is the primary key being reference) EXECUTE ''SELECT $3 FROM $2 WHERE $3=NEW.$1''; IF FOUND THEN RETURN NEW RAISE ERROR ''Foreign key violation.''; RETURN; END' LANGUAGE 'plpgsql'; - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/Fxb9WgwF3QvpWNwRAh6tAJ9TxkqmKd8NrsQSwadV9FQ8PuSFIACg2sg9 6KPuw+msH/faa8F0xR+FSTI= =Yo2a -END PGP SIGNATURE- ---(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] Recursive request ...
Joe Conway <[EMAIL PROTECTED]> writes: > BenLaKnet wrote: > > I see connect by in Oracle > > ??? is there an equivalent in PostgreSQL or not ?? > > Someone is working on the SQL99 equivalent, but it isn't done yet. Perhaps for > 7.5. There's a connectby hack in the contrib/tablefunc directory. I haven't used it so I'm not clear on how powerful it is compared to the real deal, but people on one of the pgsql lists seemed to find it useful when it came up in the past. connectby(text relname, text keyid_fld, text parent_keyid_fld, text start_with, int max_depth [, text branch_delim]) - returns keyid, parent_keyid, level, and an optional branch string - requires anonymous composite type syntax in the FROM clause. See the instructions in the documentation below. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Table Partitioning and Rules
"Girish Bajaj" <[EMAIL PROTECTED]> writes: > I cant possibly index all the cols in the table. So I thought Id best manage > the data by splitting up the table into multiple partitions and eventually > depending on application logic, only scan those tables that are necessary to > scan sequentially instead of the whole big table. But that's only going to help if one of the columns they're searching on is the last name column isn't it? I'm a fan of partitioned tables but you have to consider what advantage you're trying to achieve to understand if it will actually be helpful for you: Partitioned tables only really improve query performance if virtually all queries use a common constraint. The canonical example is accounting tables being partitioned based on fiscal year. Virtually all the queries--even ones doing massive batch queries best served by sequential scans--will only scan the current fiscal year. In your case unless you can impose a constraint on the UI that users always perform their queries on a single letter of the alphabet at a time and only see results for people whose last names match that letter, it's not really a great match as far as query performance. The other advantage of partitioned tables is space management; it allows placing each partition on a separate physical storage space. However without native support in Postgres doing it via rules is going to be a headache. I would think you would be better off striping the disks together and storing it as a single large table. That's the only clean approach Postgres really allows at this point anyways. Finally, if I WAS going to partition based on the first letter of a text string, which I doubt I would, I would probably create 26 partitions right off the bat. Not try to make up arbitrary break points. If those arbitrary breakpoints turn out to be poorly chosen it'll be a complex manual job to move them. Whereas if you just have 26 partitions some will be large and some small and you can move partitions between physical storage freely to balance things. -- greg ---(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
