Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] problems with dropped columns

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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


In response to

Responses

pgsql-hackers by date

Next:From: Alan GutierrezDate: 2003-03-06 05:53:47
Subject: Re: XML ouput for psql
Previous:From: Rod TaylorDate: 2003-03-06 05:29:13
Subject: Row level stats

pgsql-general by date

Next:From: Stephan SzaboDate: 2003-03-06 05:47:48
Subject: Re: foreign key constraint across databases
Previous:From: Joe ConwayDate: 2003-03-06 05:20:21
Subject: Re: [HACKERS] problems with dropped columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group