[SQL] cursors and for loops?
Hello, I am wondering if I can use a cursor in a for loop. I haven't been able to get it to work. I am just beginning plpgsql and I am struggling here. I am trying to do this: create or replace function ttest(varchar) RETURNS varchar AS ' DECLARE parId ALIAS FOR $1; dennis varchar; tmp_xvalues RECORD; attrVals CURSOR (thePar varchar) IS select '' '' || name ||''=''|| value || as rval from attbl where idcol = thePar; BEGIN OPEN attrVals(parId); for tmp_xvalues in fetch all from attrVals loop dennis := dennis || tmp_xvalues.rval; end loop; return dennis; END; ' language plpgsql; If I try to use this function like so: select ttest('blah') I get: Error: ERROR: syntax error at or near all at character 15 I guess I am confused what I can put in for var in expression loop and what fetch next or fetch all evaluates to. Dennis [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] trigger/for key help
On Sat, 11 Apr 2004, Bret Hughes wrote: S*t s*t s*t. I have managed to screw up the system tables trying to delete a foreign key on a new table I was going to start using tomorrow. elevating-# \d diag_logs Table diag_logs Column | Type | Modifiers -++--- sernum | integer| not null default nextval('public.diag_logs_sernum_seq'::text) display | integer| not null tdate | date | not null ttime | time without time zone | not null tstatus | smallint | not null ttype | smallint | not null Indexes: diag_logs_display, diag_logs_tdate, diag_logs_tstatus Primary key: diag_logs_pkey There used to be to foreign key constraints named $1 and $2 (I cut and pasted sql from a dump of another table that caused the trigger names that I was trying to get rid of) These were created with alter table like this : elevating=# ALTER TABLE ONLY diag_logs ADD CONSTRAINT $2 FOREIGN KEY (ttype) REFERENCES test_types(num); ALTER TABLE based on a bunch of surfing I deleted the six rows in pg_tigger that referred to elevating=# delete from pg_trigger where tgargs like '%diag_logs%'; DELETE 6 For future note, in recent versions (IIRC 7.3 and above), you should probably use ALTER TABLE DROP CONSTRAINT, in versions older than that, you should select the triggers and use DROP TRIGGER name so as to have the system handle the next step for you. elevating=# drop table diag_logs; ERROR: 2 trigger record(s) not found for relation diag_logs This is because reltriggers in the pg_class row for the table in question is incorrect. You can fix this by updating the rows (*). You'll probably want to fix pg_constraint as well, but I think that the table will function properly at least until you try to dump it. (*) something like this should work update pg_class set reltriggers=(select count(*) from pg_trigger where tgrelid=pg_class.oid) where relname='insert name here'; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] cursors and for loops?
Dennis [EMAIL PROTECTED] writes: I am wondering if I can use a cursor in a for loop. Something like LOOP FETCH ...; EXIT WHEN NOT found; ... END LOOP; should do it. 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--top 10 for each key value?
No, it will not work twice in the same query as is. If you want to code two counter buckets and pass in some way to distinguish between the two yada yada yada it is possible. It is also possible to code this to do multi-level counting/breaks/calculations, etc. But the SD dictionary is by connection. So any values stored in it need to be initialized at the appropriate time *outside* of the first use. elein On Sun, Apr 11, 2004 at 12:38:20AM -0400, Greg Stark wrote: elein [EMAIL PROTECTED] writes: create or replace function pycounter(integer) returns integer as ' if args[0] == 0: SD[nextno] = 1 return SD[nextno] try: SD[nextno] += 1 except: SD[nextno] = 1 return SD[nextno] ' language 'plpythonu'; And clearly it can be done faster as a little C function. Does this approach have a hope of working if it's used twice in the same query? -- 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] trigger/for key help
On Sun, 2004-04-11 at 10:17, Stephan Szabo wrote: On Sat, 11 Apr 2004, Bret Hughes wrote: S*t s*t s*t. I have managed to screw up the system tables trying to delete a foreign key on a new table I was going to start using tomorrow. elevating=# drop table diag_logs; ERROR: 2 trigger record(s) not found for relation diag_logs This is because reltriggers in the pg_class row for the table in question is incorrect. You can fix this by updating the rows (*). You'll probably want to fix pg_constraint as well, but I think that the table will function properly at least until you try to dump it. (*) something like this should work update pg_class set reltriggers=(select count(*) from pg_trigger where tgrelid=pg_class.oid) where relname='insert name here'; ok I updated the rows by relname individually so I could limit issues should I screw it up. I can now get a dump of the db but still cannot drop the two tables I added. I get a different message now : elevating=# \d diag_logs; Table diag_logs Column | Type | Modifiers -++--- sernum | integer| not null default nextval('public.diag_logs_sernum_seq'::text) display | integer| not null tdate | date | not null ttime | time without time zone | not null tstatus | smallint | not null ttype | smallint | not null Indexes: diag_logs_display, diag_logs_tdate, diag_logs_tstatus Primary key: diag_logs_pkey elevating=# drop table diag_logs; ERROR: could not find tuple for constraint 2041954 and elevating=# \d test_types; Table test_types Column | Type | Modifiers +---+ num| smallint | not null progname | character(25) | not null severity_level | smallint | not null default 1 Primary key: test_types_pkey elevating=# drop table test_types; ERROR: could not find tuple for constraint 2041971 elevating=# I can find no reference to these oids anywhere. I wasgonig to restart postmaster incase something is cached but I hate to since I don't know if it will restart properly. TIA Bret ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] cursors and for loops?
Tom Lane writes: Something like LOOP FETCH ...; EXIT WHEN NOT found; ... END LOOP; Thank you! I tried finding documentation on found in this context and didn't come up with anything. Can you point me to where it is documented? Also, I am not getting the results I think I should be getting. Is there any kind of debug setting, or if not that, a way to output text (i.e. printf) from plpgsql? Thanks, Dennis [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] trigger/for key help
Bret Hughes [EMAIL PROTECTED] writes: elevating=# drop table test_types; ERROR: could not find tuple for constraint 2041971 I can find no reference to these oids anywhere. You haven't looked very hard then ;-). I expect this is because pg_depend has links from the table to the constraints it owns. You could probably remove the pg_depend entries to make it possible to drop the table. Use ALTER TABLE next time, eh? Hand manipulation of the system catalogs is *not* for those who don't know exactly what they are doing. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] trigger/for key help
On Sun, 2004-04-11 at 17:00, Tom Lane wrote: Bret Hughes [EMAIL PROTECTED] writes: elevating=# drop table test_types; ERROR: could not find tuple for constraint 2041971 I can find no reference to these oids anywhere. You haven't looked very hard then ;-). I expect this is because pg_depend has links from the table to the constraints it owns. You could probably remove the pg_depend entries to make it possible to drop the table. Use ALTER TABLE next time, eh? Hand manipulation of the system catalogs is *not* for those who don't know exactly what they are doing. regards, tom lane Hmm I thought I had but pg_depend is one that I missed. I deleted all refs to rows in pg_depends and was able to drop the table. FWIW I tried to use alter table but could never get the parser to accept $1 as a constraint name. I used single and double quotes as well as a lame attempt \$1. Thanks I am breathing better. I was about to pg_dump and restore into a test database to see what would happen then since there was no reference to any of the oids in the error messages found in the dump. I can now resume climbing up the learning curve. Bret ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings