Обсуждение: Improving ALTER TYPE support
I've been working on a function which returns a setof a composite type. Everytime I've changed the structure of the returningsetof, I've had to change the type accordingly, which current means doing a drop type ... cascade down to the function.We should allow one of the following: <br /><br />1) Add a REPLACE on CREATE TYPE (create or replace type)<br /><br/>2) Allow ALTER TYPE to alter the definition of the type (currently it only allows changes to schema and ownership)<br/><br />It may get tricky with the first option because there'd be orphaned dependencies on objects which referencethat particular type while it's being re-created. <br /><br />I'll work on a patch according to feedback.
John, > I've been working on a function which returns a setof a composite type. > Everytime I've changed the structure of the returning setof, I've had to > change the type accordingly, which current means doing a drop type ... > cascade down to the function. We should allow one of the following: Why not go all the way and work out a way to define an SRF return type as a part of the function? e.g. CREATE OR REPLACE FUNCTION some_srf ( param1 int, param2 text ) RETURNS some_srf ( col1 int, col2 numeric ) AS .... Then the "replace" function would automatically rebuild the type. -- Josh Berkus PostgreSQL @ Sun San Francisco
Makes more sense to do that, and I think it'll be cleaner to implement as well.
On 5/23/06, Josh Berkus <josh@agliodbs.com > wrote:
John,
> I've been working on a function which returns a setof a composite type.
> Everytime I've changed the structure of the returning setof, I've had to
> change the type accordingly, which current means doing a drop type ...
> cascade down to the function. We should allow one of the following:
Why not go all the way and work out a way to define an SRF return type as a
part of the function? e.g.
CREATE OR REPLACE FUNCTION some_srf ( param1 int, param2 text )
RETURNS some_srf ( col1 int, col2 numeric ) AS ....
Then the "replace" function would automatically rebuild the type.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Josh Berkus <josh@agliodbs.com> writes: >> I've been working on a function which returns a setof a composite type. >> Everytime I've changed the structure of the returning setof, I've had to >> change the type accordingly, which current means doing a drop type ... >> cascade down to the function. We should allow one of the following: > Why not go all the way and work out a way to define an SRF return type as a > part of the function? e.g. Um, isn't that exactly what the OUT parameter support already gives you, ie, an anonymous record type? regards, tom lane
I guess I don't understand what one has to do with the other (SRF's returning records and OUT parameters). I always thought they were exclusive, could you elaborate?
On 5/24/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Josh Berkus <josh@agliodbs.com> writes:
>> I've been working on a function which returns a setof a composite type.
>> Everytime I've changed the structure of the returning setof, I've had to
>> change the type accordingly, which current means doing a drop type ...
>> cascade down to the function. We should allow one of the following:
> Why not go all the way and work out a way to define an SRF return type as a
> part of the function? e.g.
Um, isn't that exactly what the OUT parameter support already gives you,
ie, an anonymous record type?
regards, tom lane
"John Jawed" <johnjawed@gmail.com> writes: > I guess I don't understand what one has to do with the other (SRF's > returning records and OUT parameters). I always thought they were exclusive, > could you elaborate? (BTW, please don't post uselessly HTML-ified mail.) If you write something like create function foo (in p1 int, out r1 int, out r2 text)returns setof record then you've effectively got a function returning an anonymous composite type (here, with one int and one text column). I don't see a case for inventing a separate facility that will pretty much just duplicate this functionality. regards, tom lane
Tom, > If you write something like > > create function foo (in p1 int, out r1 int, out r2 text) > returns setof record D'oh! I feel like a dork now. I forgot we had this. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Ok, this way works and the proposed way isn't necessary. On 5/24/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "John Jawed" <johnjawed@gmail.com> writes: > > I guess I don't understand what one has to do with the other (SRF's > > returning records and OUT parameters). I always thought they were exclusive, > > could you elaborate? > > (BTW, please don't post uselessly HTML-ified mail.) > > If you write something like > > create function foo (in p1 int, out r1 int, out r2 text) > returns setof record > > then you've effectively got a function returning an anonymous composite > type (here, with one int and one text column). I don't see a case for > inventing a separate facility that will pretty much just duplicate this > functionality. > > regards, tom lane >