Обсуждение: CREATE TYPE and %ROWTYPE
(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.
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 |
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
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
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
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
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