[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 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 "" 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='';
---(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='';
>
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
Re: [SQL] trigger/for key help
Bret Hughes <[EMAIL PROTECTED]> writes: > 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. Hm, "$1" works for me ... regression=# create table foo (f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE regression=# create table bar (f1 int references foo); CREATE TABLE regression=# \d bar Table "public.bar" Column | Type | Modifiers +-+--- f1 | integer | Foreign-key constraints: "$1" FOREIGN KEY (f1) REFERENCES foo(f1) regression=# alter table bar drop constraint "$1"; ALTER TABLE regression=# 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
