From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | Damjan Pipan <damjan(dot)pipan(at)siol(dot)net>, pgsql-general(at)postgresql(dot)org, Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] problems with dropped columns |
Date: | 2003-03-06 05:32:01 |
Message-ID: | 3E66DD51.9060205@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Christopher Kings-Lynne wrote:
> I want to fix this bug, however I can't see how the example below is
> failing... (Obeys dropped columns) I'm not up with my SRFs, so would
> someone be able to post a concise SQL script that demonstrates the failure?
>
> I can see in the code that it should be failing, but I need a demonstrated
> example...
Taking it a bit further...
CREATE TABLE fk_test (f1 int, f2 int);
insert into fk_test(f1, f2) values(1, 21);
insert into fk_test(f1, f2) values(2, 22);
ALTER TABLE fk_test DROP COLUMN f2;
ALTER TABLE fk_test ADD COLUMN f3 int;
insert into fk_test(f1, f3) values(3, 33);
insert into fk_test(f1, f3) values(4, 34);
CREATE OR REPLACE FUNCTION test()
RETURNS SETOF fk_test AS '
DECLARE
rec fk_test%ROWTYPE;
BEGIN
FOR rec IN SELECT * FROM fk_test LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
regression=# select * from test();
f1 | f3
----+----
1 |
2 |
3 |
4 |
(4 rows)
regression=# ALTER TABLE fk_test DROP COLUMN f3;
ALTER TABLE
regression=# select * from test();
f1
----
1
2
3
4
(4 rows)
regression=# ALTER TABLE fk_test ADD COLUMN f3 int;
ALTER TABLE
regression=# select * from test();
WARNING: Error occurred while executing PL/pgSQL function test
WARNING: line 5 at return next
ERROR: Wrong record type supplied in RETURN NEXT
CREATE OR REPLACE FUNCTION test()
RETURNS SETOF fk_test AS '
DECLARE
rec fk_test%ROWTYPE;
BEGIN
FOR rec IN SELECT * FROM fk_test LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
regression=# select * from test();
f1 | f3
----+----
1 |
2 |
3 |
4 |
(4 rows)
Joe
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-03-06 05:47:48 | Re: foreign key constraint across databases |
Previous Message | Joe Conway | 2003-03-06 05:20:21 | Re: [HACKERS] problems with dropped columns |
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Gutierrez | 2003-03-06 05:53:47 | Re: XML ouput for psql |
Previous Message | Rod Taylor | 2003-03-06 05:29:13 | Row level stats |