[SQL]
Im running/playing with PG 8.0 locally and want to install the contrib/dblink and contrib/crosstab. Can this be done on Windows, is there a GMAKE.exe for Windows?? Someone enlighten me! __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Greg Sabino Mullane wrote: Names shortened to spare the line lengths: SELECT bob.cid, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1) AS tk1, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 1) AS tk2, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 2) AS tk3, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 3) AS tk4, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 4) AS tk5, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 5) AS tk6, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 6) AS tk7, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 7) AS tk8, FROM (SELECT DISTINCT cid FROM ats) AS bob; Don't you miss for each subselect an order by tid ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Don't you miss for each subselect an order by tid ? No: since all the SELECTs are part of one statement, they will have the same (pseudo-random) implicit order. Since Josh's requirement said the order of the tids* was not important, I can be lazy and get away with it in this case. An order by would not hurt, of course. * As far as PG goes, this is not an ideal abbreviation! :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200408180745 -BEGIN PGP SIGNATURE- iD8DBQFBI0InvJuQZxSWSsgRApW7AKCpFN6TMQ3WjcJgZse5f+Ap6/Y7RACfSSlc MZusqEadF2xZrE4PLOhmMek= =pnQz -END PGP SIGNATURE- ---(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] multi column foreign key for implicitly unique columns
On 8/17/2004 10:45 PM, Josh Berkus wrote: Markus, Hey, I see you figured out a workaround to writing a trigger for this. Let's see if we can make it work. ERROR: there is no unique constraint matching given keys for referenced table objects The reason for this is that CASCADE behavior gets quite odd when there is an FK reference to a non-unique column. We used to allow it, in 7.1, and I was responsible for a number of bug reports that led to us disallowing it. It should be theoretically implementable and relationally sound but will require a *lot* of troubleshooting to make work. So far, nobody's really interested enough. SQL92 4.10 Integrity constraints: ... In the case that a table constraint is a referential constraint, the table is referred to as the referencing table. The referenced columns of a referential constraint shall be the unique columns of some unique constraint of the referenced table. ... Meaning that not enforcing the uniqueness of those columns isn't an option. PostgreSQL is currently happy with a UNIQUE constraint that covers those columns in any order, which is to the letter spec compliant. Really interested will not do here. Jan However, you have an easy way out: ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name); This will add the unique constraint that Postgres wants without changing your data at all. -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] multi column foreign key for implicitly unique columns
, 18.08.2004, 15:33, Jan Wieck : Meaning that not enforcing the uniqueness of those columns isn't an option. The thing is that the columns _are_ unique, there's just no unique constraint on them. They are unique because there's a unique constraint on a subset of these columns. So no additional uniqueness enforcing needed. -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] multi column foreign key for implicitly unique columns
On 8/18/2004 9:49 AM, Markus Bertheau wrote: , 18.08.2004, 15:33, Jan Wieck : Meaning that not enforcing the uniqueness of those columns isn't an option. The thing is that the columns _are_ unique, there's just no unique constraint on them. They are unique because there's a unique constraint on a subset of these columns. So no additional uniqueness enforcing needed. Yes, you are right, a superset of columns of a unique constraint is allways unique as well. I assume it is performance why you are denormalizing your data? However, Bruce, this should be on the TODO list: * Allow foreign key to reference a superset of the columns covered by a unique constraint on the referenced table. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] multi column foreign key for implicitly unique columns
, 18.08.2004, 16:06, Jan Wieck : I assume it is performance why you are denormalizing your data? Please have a look at http://archives.postgresql.org/pgsql-sql/2004-08/msg00157.php for the schema and an explanation. I'm not denormalizing it as far as I can tell. Thanks -- Markus Bertheau [EMAIL PROTECTED] ---(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] multi column foreign key for implicitly unique columns
On Wed, 18 Aug 2004, Jan Wieck wrote: On 8/18/2004 9:49 AM, Markus Bertheau wrote: В Срд, 18.08.2004, в 15:33, Jan Wieck пишет: Meaning that not enforcing the uniqueness of those columns isn't an option. The thing is that the columns _are_ unique, there's just no unique constraint on them. They are unique because there's a unique constraint on a subset of these columns. So no additional uniqueness enforcing needed. Yes, you are right, a superset of columns of a unique constraint is allways unique as well. True, but the spec explicitly asks for the columns to be the members of a unique constraint, not that the columns be provably unique. See the other portion of the thread related to dropping constraints for other spec extensions doing this implies. I think the actual specific change to make it look for the subset should be trivial, but we'd really need to work out those dependency issues at the very least and I'm afraid there are more such little corners. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Greg Sabino Mullane wrote: Don't you miss for each subselect an order by tid ? No: since all the SELECTs are part of one statement, they will have the same (pseudo-random) implicit order. Is this guaranted ? Regards Gaetano Mendola ---(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] SQL Challenge: Arbitrary Cross-tab
Greg Sabino Mullane [EMAIL PROTECTED] writes: Don't you miss for each subselect an order by tid ? No: since all the SELECTs are part of one statement, they will have the same (pseudo-random) implicit order. Nope; Gaetano's right, you cannot assume that. It's entirely possible for the planner to choose different plans depending on the OFFSET. (Maybe not very likely, with such small offsets, but could happen.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] multi column foreign key for implicitly unique columns
Jan Wieck [EMAIL PROTECTED] writes: However, Bruce, this should be on the TODO list: * Allow foreign key to reference a superset of the columns covered by a unique constraint on the referenced table. See the followup discussion as to why this is a bad idea. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] /contrib and win32
Theo Galanakis wrote: Im running/playing with PG 8.0 locally and want to install the contrib/dblink and contrib/crosstab. Can this be done on Windows, is there a GMAKE.exe for Windows?? Someone enlighten me! Uh, good question. I think you need the msys/mingw environment to add contrib stuff, and this definately is a problem for many users. -- 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] multi column foreign key for implicitly unique columns
However, Bruce, this should be on the TODO list: * Allow foreign key to reference a superset of the columns covered by a unique constraint on the referenced table. It would probably be more beneficial to be able to create a unique constraint without requiring the fields be indexed. Gets rid of most of the overhead from double uniques. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] multi column foreign key for implicitly unique columns
Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: However, Bruce, this should be on the TODO list: * Allow foreign key to reference a superset of the columns covered by a unique constraint on the referenced table. See the followup discussion as to why this is a bad idea. Maybe an alternative todo? * Allow multiple unique constraints to share an index where one is a superset of the others' columns. That way you can mark it unique without having the overhead of multiple indexes. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Josh Berkus wrote: Folks, I have a wierd business case. Annoyingly it has to be written in *portable* SQL92, which means no arrays or custom aggregates. I think it may be impossible to do in SQL which is why I thought I'd give the people on this list a crack at it. Solver gets a free drink/lunch on me if we ever meet at a convention. Might be possible. Would certainly be ugly. The Problem: for each case there are from zero to eight timekeepers authorized to work on the case, out of a pool of 150 timekeepers. This data is stored vertically: authorized_timekeepers: case_id | timekeeper_id 213447 | 047 132113 | 021 132113 | 115 132113 | 106 etc. But, a client's e-billing application wants to see these timekeepers displayed in the following horizontal format: case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 213447 | 047 | | | | | | | | 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | etc. Order does not matter for timekeepers 1-8. This is a daunting problem because traditional crosstab solutions do not work; timekeepers 1-8 are coming out of a pool of 150. Can it be done? Or are we going to build this with a row-by-row procedural loop? (to reiterate: I'm not allowed to use a custom aggregate or other PostgreSQL advanced feature) If it can be done, it might be extremely ugly. I am thinking a massive set of left self joins (since there could be between 0 and 8). Something like: select case_id FROM authorized_timekeeper t0 LEFT JOIN (SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper GROUP BY case_id) t1 ON case_id LEFT JOIN (SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper WHERE timekeeper_id t1.timekeeper GROUP BY case_id) t2 ON case_id LEFT JOIN (SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper WHERE timekeeper_id NOT IN (t1.timekeeper, t2.timekeeper) GROUP BY case_id) t3 etc If this is not an option, instead I would create a series of views. Something like: CREATE VIEW t1 AS select case_id, min(timekeeper_id) AS tk_id from authorized_timekeepers group by case_id; CREATE VIEW t2 AS select case_id, min(timekeeper_id) AS tk_id from authorized_timekeepers WHERE tk_id NOT IN (SELECT tk_id FROM t1) group by case_id; CREATE VIEW t3 AS select case_id, min(timekeeper_id) AS tk_id FROM authorized_timekeepers WHERE tk_id NOT IN (SELECT tk_id FROM t1) AND tk_id NOT IN (SELECT tk_id FROM t2) GROUP BY case_id; Etc. Then you do a left join among the views. Hope that this helps. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] multi column foreign key for implicitly unique columns
Richard Huxton [EMAIL PROTECTED] writes: * Allow multiple unique constraints to share an index where one is a superset of the others' columns. That way you can mark it unique without having the overhead of multiple indexes. That just moves the uncertain-dependency problem over one spot, ie, it's the fabricated unique constraint that you can't pin down the requirements for. regards, tom lane ---(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] multi column foreign key for implicitly unique columns
On 8/18/2004 12:18 PM, Tom Lane wrote: Richard Huxton [EMAIL PROTECTED] writes: * Allow multiple unique constraints to share an index where one is a superset of the others' columns. That way you can mark it unique without having the overhead of multiple indexes. That just moves the uncertain-dependency problem over one spot, ie, it's the fabricated unique constraint that you can't pin down the requirements for. If we allow for a unique index, that * it is NOT maintained (no index tuples in there) * depends on another index that has a subset of columns * if that subset-index is dropped, the index becomes maintained then the uncertainty is gone. At the time someone drops the other constraint or unique index, the data is unique with respect to the superset of columns. So building the unique index data at that time will succeed. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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] multi column foreign key for implicitly unique columns
On Wed, 2004-08-18 at 12:27, Jan Wieck wrote: On 8/18/2004 12:18 PM, Tom Lane wrote: Richard Huxton [EMAIL PROTECTED] writes: * Allow multiple unique constraints to share an index where one is a superset of the others' columns. That way you can mark it unique without having the overhead of multiple indexes. That just moves the uncertain-dependency problem over one spot, ie, it's the fabricated unique constraint that you can't pin down the requirements for. If we allow for a unique index, that Silly question, but why does UNIQUE require an index at all? Yes, current implementation does, and agreed that checks will be mighty slow without an index (so are CASCADES to a non-indexed column)... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] multi column foreign key for implicitly unique columns
Jan Wieck [EMAIL PROTECTED] writes: If we allow for a unique index, that * it is NOT maintained (no index tuples in there) * depends on another index that has a subset of columns * if that subset-index is dropped, the index becomes maintained then the uncertainty is gone. At the time someone drops the other constraint or unique index, the data is unique with respect to the superset of columns. So building the unique index data at that time will succeed. My goodness this is getting ugly. The notion of having to invoke an index build as a side-effect of a DROP sounds like a recipe for trouble. (Sample problem: what you're actually trying to do is drop the entire table ... but because the subset-index happens to get visited first, you go off and build the superset-index before you let the DROP finish. User will be unhappy, if table is large. Or try this one: the superset- index build actually fails because you've already dropped something it depends on. This seems quite possible in cases involving cascading from a drop of an individual column or datatype, for instance.) I'd like to see more than one person needing it, before we go to that kind of trouble to do something that's not in the spec. regards, tom lane ---(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] SQL Challenge: Arbitrary Cross-tab
Joe, Elein: This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed version; crosstab(sourcesql, ncols)) works. If you really need it to be portable, though, application layer procedural code is likely to be the easiest and fastest way to go. crosstab just wraps the procedural code in an SRF for you. No, you're missing one factor in the spec. Timekeeper_1 for case_id = 182738 is not the same timekeeper as Timekeeper_1 for case_id = 217437. That's why traditional crosstab plans don't work. Anyway, here's the wrap-up: I tried Stephan's idea, it works, but it's so slow that we're going to to the procedural loop. Thanks, all! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] multi column foreign key for implicitly unique columns
Jan, In the case that a table constraint is a referential constraint, the table is referred to as the referencing table. The referenced columns of a referential constraint shall be the unique columns of some unique constraint of the referenced table. Missed that one. Interesting. AFAIK, the uniqueness of referenced columns is NOT a requirement of Relaitonal Algebra. So why does SQL require it? Maybe I'll ask Joe Celko after he finishes moving to Austin. I have my own issue that forced me to use triggers. Given: table users ( name login PK status etc. ) table status ( status relation label definition PK status, relation ) the relationship is: users.status = status.status AND status.relation = 'users'; This is a mathematically definable constraint, but there is no way in standard SQL to create an FK for it.This is one of the places I point to whenever we have the SQL is imperfectly relational discussion. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] multi column foreign key for implicitly unique columns
On Wed, 18 Aug 2004, Josh Berkus wrote: In the case that a table constraint is a referential constraint, the table is referred to as the referencing table. The referenced columns of a referential constraint shall be the unique columns of some unique constraint of the referenced table. Missed that one. Interesting. AFAIK, the uniqueness of referenced columns is NOT a requirement of Relaitonal Algebra. So why does SQL require it? Maybe I'll ask Joe Celko after he finishes moving to Austin. I have my own issue that forced me to use triggers. Given: table users ( name login PK status etc. ) table status ( status relation label definition PK status, relation ) the relationship is: users.status = status.status AND status.relation = 'users'; This is a mathematically definable constraint, but there is no way in standard SQL to create an FK for it.This is one of the places I point to whenever we have the SQL is imperfectly relational discussion. Well, I think SQL does give a way of specifying that constraint through assertions and check constraints with subselects. We just don't support either of those constructs. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] multi column foreign key for implicitly unique columns
On 8/18/2004 12:46 PM, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: If we allow for a unique index, that * it is NOT maintained (no index tuples in there) * depends on another index that has a subset of columns * if that subset-index is dropped, the index becomes maintained then the uncertainty is gone. At the time someone drops the other constraint or unique index, the data is unique with respect to the superset of columns. So building the unique index data at that time will succeed. My goodness this is getting ugly. The notion of having to invoke an index build as a side-effect of a DROP sounds like a recipe for trouble. The idea sure needs some refinement :-) I'd like to see more than one person needing it, before we go to that kind of trouble to do something that's not in the spec. Actually, the whole thing strikes me more as a sign for a denormalized database schema. If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring that the redundant copy of y in b.y stays in sync with a.y. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Joe, case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 -+-+-+-+-+-+-+-+- 132113 | 021 | 115 | 106 | | | | | 14 | 106 | 021 | 115 | 108 | 006 | 042 | 142 | 064 213447 | 047 | | | | | | | Darn I wish this didn't have to be portable -- --Josh 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] multi column foreign key for implicitly unique columns
Jan, If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring that the redundant copy of y in b.y stays in sync with a.y. So? What's denormalized about that? His other choice is to use a trigger. What he's trying to do is ensure that the class selected for the FK class_name, field_name relates to the same class_name in objects. -- --Josh 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] multi column foreign key for implicitly unique columns
On Wed, Aug 18, 2004 at 10:05:13 -0700, Josh Berkus [EMAIL PROTECTED] wrote: I have my own issue that forced me to use triggers. Given: table users ( name login PK status etc. ) table status ( status relation label definition PK status, relation ) the relationship is: users.status = status.status AND status.relation = 'users'; This is a mathematically definable constraint, but there is no way in standard SQL to create an FK for it.This is one of the places I point to whenever we have the SQL is imperfectly relational discussion. If users is supposed to reference status you can do this by adding a relation column to users, using a constraint to force relation to always be 'users' and then having (status, relation) being a foreign key. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] multi column foreign key for implicitly unique columns
Bruno, If users is supposed to reference status you can do this by adding a relation column to users, using a constraint to force relation to always be 'users' and then having (status, relation) being a foreign key. But that requires the addition of an extra, indexed Text column to the table. And the status reference table is not the only place I need this construct; I have other relationships to reference tables which are similar. It would amount to adding probably a total of 25-40 columns to various tables in my database overall; maybe 100MB of completely redundant data. :-( I'll wait for ASSERTIONS, I think. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(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]
Title: RE: [SQL] Les, Na grapso PG 8.0 sta arhithia mou? We acutually use Unix on Prod and Test, however I was just playing locally and was curious how to extent the Win version of PG 8.0. Theo -Original Message- From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 18 August 2004 3:44 PM To: Theo Galanakis Subject: Re: [SQL] O kyrios Theo Galanakis egrapse stis Aug 18, 2004 : Im running/playing with PG 8.0 locally and want to install the contrib/dblink and contrib/crosstab. Can this be done on Windows, is there a GMAKE.exe for Windows?? Someone enlighten me! Theodore, Sooner or later you will discover the reasons why you should use UNIX in the first place. __ This email, including attachments, is intended only for the addressee and may be confidential, privileged and subject to copyright. If you have received this email in error, please advise the sender and delete it. If you are not the intended recipient of this email, you must not use, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner. -- -Achilleus
Re: [SQL] multi column foreign key for implicitly unique columns
On 8/18/2004 2:55 PM, Josh Berkus wrote: Jan, If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring that the redundant copy of y in b.y stays in sync with a.y. So? What's denormalized about that? His other choice is to use a trigger. Because the value in b.y is redundant. b.x-a.x-a.y is exactly the same value and he even wants to ensure this with the constraint. Jan What he's trying to do is ensure that the class selected for the FK class_name, field_name relates to the same class_name in objects. -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Function Issue!
Title: Function Issue! Can anyone tell me what is wrong with the function below ? It throws an ERROR: syntax error at or near FETCH at character 551 CREATE OR REPLACE FUNCTION public.theo_test2 () RETURNS OPAQUE AS' BEGIN declare curr_theo cursor for select * from node_names; fetch next from curr_theo; close curr_theo; END; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; However, this appears to work : begin; declare curr_theo cursor for select * from node_names; fetch next from curr_theo; close curr_theo; end; __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
Re: [SQL] Function Issue!
Theo Galanakis [EMAIL PROTECTED] writes: Can anyone tell me what is wrong with the function below ? CREATE OR REPLACE FUNCTION public.theo_test2 () RETURNS OPAQUE AS' BEGIN declare curr_theo cursor for select * from node_names; fetch next from curr_theo; close curr_theo; END; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; The DECLARE has to go before the BEGIN: CREATE OR REPLACE FUNCTION public.theo_test2 () RETURNS OPAQUE AS' DECLARE curr_theo cursor for select * from node_names; BEGIN fetch next from curr_theo; close curr_theo; END; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; I think you are missing an OPEN step too, and the FETCH syntax is wrong for plpgsql. Read the plpgsql doc section about using cursors --- it is not at all identical to what you do in plain SQL. regards, tom lane ---(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
