SEO

#1 (**permalink**) 04-12-2008, 02:34 AM

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:

1) Add a REPLACE on CREATE TYPE (create or replace type)

2) Allow ALTER TYPE to alter the definition of the type (currently it only
allows changes to schema and ownership)

It may get tricky with the first option because there'd be orphaned
dependencies on objects which reference that particular type while it's
being re-created.

I'll work on a patch according to feedback.

#2 (**permalink**) 04-12-2008, 02:34 AM

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

#3 (**permalink**) 04-12-2008, 02:34 AM

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#4 (**permalink**) 04-12-2008, 02:35 AM

"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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#5 (**permalink**) 04-12-2008, 02:35 AM

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#6 (**permalink**) 04-12-2008, 02:35 AM

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
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Improving ALTER TYPE support