Thread: CREATE TYPE and %ROWTYPE

CREATE TYPE and %ROWTYPE

From

Steve Tucknott

Date:

03 April 2005, 17:14:13

(Sorry if this appears twice - I sent the previous mail as the wrong mail ID)

PostgreSQL 7.4.5

Is there a way of using %ROWTYPE in custom types?
I want to return two/three complete record areas to a calling 4gl program.
I can define a custom type, ie:
CREATE TYPE myTest AS (
fld1      INTEGER,
fld2      VARCHAR,
fld3       .......etc
);
This works fine. But if I define:
CREATE TYPE myTest AS (
area1              tableName1%ROWTYPE,
area2              tableName2%ROWTYPE
);
I get an error. This then means that I have to define myTest with the individual fields (which I can do) - but which in turn means that if the tables change, I have to remember to change the procs.

Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

Re: CREATE TYPE and %ROWTYPE

From

Steve Tucknott

Date:

08 April 2005, 10:46:41

Before I go too far, has anyone got any ideas on this?

On Sun, 2005-04-03 at 14:18, Steve Tucknott wrote:
> (Sorry if this appears twice - I sent the previous mail as the wrong
> mail ID)
>
> PostgreSQL 7.4.5
>
> Is there a way of using %ROWTYPE in custom types?
> I want to return two/three complete record areas to a calling 4gl
> program.
> I can define a custom type, ie:
> CREATE TYPE myTest AS (
> fld1      INTEGER,
> fld2      VARCHAR,
> fld3       .......etc
> );
> This works fine. But if I define:
> CREATE TYPE myTest AS (
> area1              tableName1%ROWTYPE,
> area2              tableName2%ROWTYPE
> );
> I get an error. This then means that I have to define myTest with the
> individual fields (which I can do) - but which in turn means that if the
> tables change, I have to remember to change the procs.
>
>
>
> Regards,
>
> Steve Tucknott
>
> ReTSol Ltd
>
> DDI: 01903 828769
>
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772

Re: CREATE TYPE and %ROWTYPE

From

Tom Lane

Date:

08 April 2005, 10:51:59

Steve Tucknott <steve@retsol.co.uk> writes:
> Before I go too far, has anyone got any ideas on this?

IIRC, we only support %ROWTYPE with function parameters.  I don't think
anyone's thought about what it would take to use it in other contexts
such as columns of composite types.  It might be trivial ... or not.

            regards, tom lane

Re: CREATE TYPE and %ROWTYPE

From

Steve Tucknott

Date:

08 April 2005, 10:56:13

Tom,
Is there another trick that I'm missing? Is there a simple way of
returning two complete record areas without using custom types?
On Fri, 2005-04-08 at 07:51, Tom Lane wrote:
> Steve Tucknott <steve@retsol.co.uk> writes:
> > Before I go too far, has anyone got any ideas on this?
>
> IIRC, we only support %ROWTYPE with function parameters.  I don't think
> anyone's thought about what it would take to use it in other contexts
> such as columns of composite types.  It might be trivial ... or not.
>
>             regards, tom lane
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772

Re: CREATE TYPE and %ROWTYPE

From

Steve Tucknott

Date:

08 April 2005, 12:20:17

Tom,

Can I also clarify:
If I want to return a record area (row) from a table I cannot use:
RETURNS RECORD or
RETURNS tableName%ROWTYPE

But I can return a record area (row) If I first define the row as a
custom type then use:
RETURNS customType

Am I missing something here? Why do I need to define a custom type to
return a row?

What I am trying to do is to define a set of standard lookups in the
database that return the complete record area of the table(s) being
queried.

On Fri, 2005-04-08 at 08:01, Steve Tucknott wrote:
> Tom,
> Is there another trick that I'm missing? Is there a simple way of
> returning two complete record areas without using custom types?
> On Fri, 2005-04-08 at 07:51, Tom Lane wrote:
> > Steve Tucknott <steve@retsol.co.uk> writes:
> > > Before I go too far, has anyone got any ideas on this?
> >
> > IIRC, we only support %ROWTYPE with function parameters.  I don't think
> > anyone's thought about what it would take to use it in other contexts
> > such as columns of composite types.  It might be trivial ... or not.
> >
> >             regards, tom lane
> --
>
>
> Regards,
>
> Steve Tucknott
> ReTSol Ltd
>
> DDI    01903 828769
> MOBILE    07736715772
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772

Re: CREATE TYPE and %ROWTYPE

From

Steve Tucknott

Date:

08 April 2005, 12:35:14

Ignore the previous mail - I have just found that the simple:
RETURNS tableName
works fine - I was adding the %ROWTYPE unnecessarily.
Apologies.

On Fri, 2005-04-08 at 09:25, Steve Tucknott wrote:
> Tom,
>
> Can I also clarify:
> If I want to return a record area (row) from a table I cannot use:
> RETURNS RECORD or
> RETURNS tableName%ROWTYPE
>
> But I can return a record area (row) If I first define the row as a
> custom type then use:
> RETURNS customType
>
> Am I missing something here? Why do I need to define a custom type to
> return a row?
>
> What I am trying to do is to define a set of standard lookups in the
> database that return the complete record area of the table(s) being
> queried.
>
> On Fri, 2005-04-08 at 08:01, Steve Tucknott wrote:
> > Tom,
> > Is there another trick that I'm missing? Is there a simple way of
> > returning two complete record areas without using custom types?
> > On Fri, 2005-04-08 at 07:51, Tom Lane wrote:
> > > Steve Tucknott <steve@retsol.co.uk> writes:
> > > > Before I go too far, has anyone got any ideas on this?
> > >
> > > IIRC, we only support %ROWTYPE with function parameters.  I don't think
> > > anyone's thought about what it would take to use it in other contexts
> > > such as columns of composite types.  It might be trivial ... or not.
> > >
> > >             regards, tom lane
> > --
> >
> >
> > Regards,
> >
> > Steve Tucknott
> > ReTSol Ltd
> >
> > DDI    01903 828769
> > MOBILE    07736715772
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> --
>
>
> Regards,
>
> Steve Tucknott
> ReTSol Ltd
>
> DDI    01903 828769
> MOBILE    07736715772
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772