Thread: %TYPE

Checking out a bug in my code I find in my table definition:

CREATE TABLE publications
(
    ...
    name character varying(60) NOT NULL,
    ...
);

while in the function I use to populate the web page, which
amalgamates info from several sources, I have:

CREATE TYPE npc_page_details_type AS
(
    ...
    instance_name character varying(40),
    ...
);

(where instances inherits name from publications). In other words, I
changed one definition and forgot to change the other.

I've been looking to see if postgresql allows type definitions like
the ones I'm used to from Oracle, where I can say:

CREATE TYPE npc_page_details_type AS
(
    ...
    instance_name instances.name%type,
    ...
);

and it will pick up the current type of instances.name when the user
type gets compiled. However I haven't been able to find anything. Is
anything like that available? Or should I just update my definitions
and grit my teeth and carry on?


Cheers,
G.

On Aug 22, 2007, at 21:42 , Ged wrote:

> I've been looking to see if postgresql allows type definitions like
> the ones I'm used to from Oracle, where I can say:
>
> CREATE TYPE npc_page_details_type AS
> (
>     ...
>     instance_name instances.name%type,
>     ...
> );
>
> and it will pick up the current type of instances.name when the user
> type gets compiled.

I think you could use domains for something like this.

CREATE DOMAIN instance_name_type AS character varying;

And then use the domain in your types.

CREATE TYPE npc_page_details_type AS
(
     instance_name instance_name_type
);

CREATE table publications
(
     name instance_name_type PRIMARY KEY
);

However, in this particular case I don't see the point. There is no
performance advantage to using varchar(n) over just plain varchar or
the PostgreSQL type text, which although nonstandard tends to be more
common among developers used to PostgreSQL. Only if you have a
specific business reason that these columns absolutely must not have
more than x number of characters should you use varchar(n). Otherwise
you're going to be changing data types every time you want to change
the limit which is not all that much fun.

Michael Glaesemann
grzm seespotcode net

Ged wrote:
> Ty for those comments.
>
> Hmm, I did try it out before posting of course, and I've just tried it
> again to make sure I hadn't boobed with a typo. It seems my ISP is
> running 8.0.8 and it's definitely not working on that. It *is* in the
> 8.0.13 documentation also though... So now I'm off to beg them to
> upgrade.

Hmm - should work in any 8.0.x, the development team don't add new
features in point releases. I'm not sure if this feature wasn't there in
7.4 too.

Might be a bug affecting you though - could be worth checking the
release-notes in the back of the manual.

--
   Richard Huxton
   Archonet Ltd

On Aug 23, 2007, at 7:57 , Richard Huxton wrote:

> Ged wrote:
>> Ty for those comments.
>> Hmm, I did try it out before posting of course, and I've just
>> tried it
>> again to make sure I hadn't boobed with a typo. It seems my ISP is
>> running 8.0.8 and it's definitely not working on that. It *is* in the
>> 8.0.13 documentation also though... So now I'm off to beg them to
>> upgrade.
>
> Hmm - should work in any 8.0.x, the development team don't add new
> features in point releases. I'm not sure if this feature wasn't
> there in 7.4 too.
>
> Might be a bug affecting you though - could be worth checking the
> release-notes in the back of the manual.

I don't seem to have received the message Richard's responding to.
The archives seem to be missing a couple as well.

http://archives.postgresql.org/pgsql-general/2007-08/threads.php#01346

Anyone else besides Richard catch these messages? Any idea where they
may have ended up?If someone wouldn't mind sending them on to me, I'd
appreciate it.

Thanks!

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann wrote:
>
> I don't seem to have received the message Richard's responding to. The
> archives seem to be missing a couple as well.
>
> http://archives.postgresql.org/pgsql-general/2007-08/threads.php#01346
>
> Anyone else besides Richard catch these messages? Any idea where they
> may have ended up?If someone wouldn't mind sending them on to me, I'd
> appreciate it.

It's a "forgot to cc:" issue - forgot to remind, I'm afraid.

Checking back in my inbox I only see one msg I replied to and the
section "Ty for those comments...beg them to upgrade" was the whole body
of the message.

--
   Richard Huxton
   Archonet Ltd

On Aug 24, 2007, at 1:32 , Richard Huxton wrote:

> It's a "forgot to cc:" issue - forgot to remind, I'm afraid.

Thanks for checking, Richard. Was wondering if my email service was
acting up again.

Michael Glaesemann
grzm seespotcode net