Lists: | pgsql-general |
---|
From: | "R(dot)A(dot)" <adeveloper(at)bluebottle(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Can I create a TYPE (or DOMAIN) with arguments? |
Date: | 2008-01-09 09:53:00 |
Message-ID: | 1199872380.4784997c65ec5@mail.bluebottle.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hello,
I'm trying to create a composite type with an argument, to create one field of this type like character varying(x), but I don't know if this can be done with PostgreSQL. I want something like:
CREATE TYPE mytype AS (
tx character varying(x),
t2nd integer
);
I need to limit the number of chars in this field tx. And different lengths will be used when creating tables with this type (so a Domain will suffer the same problem). I'm not looking for a solution like a separate column with maxChars and a Check, because lots of columns will be required (and others design reasons).
Any suggestions?
Thanks.
----------------------------------------------------------------------
Free pop3 email with a spam filter.
http://www.bluebottle.com/tag/5
From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | "R(dot)A(dot)" <adeveloper(at)bluebottle(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Can I create a TYPE (or DOMAIN) with arguments? |
Date: | 2008-01-10 14:58:07 |
Message-ID: | AD51AA17-B989-434F-BF0A-1337CC1ED356@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Jan 9, 2008, at 3:53 AM, R.A. wrote:
> Hello,
>
> I'm trying to create a composite type with an argument, to create
> one field of this type like character varying(x), but I don't know
> if this can be done with PostgreSQL. I want something like:
> CREATE TYPE mytype AS (
> tx character varying(x),
> t2nd integer
> );
> I need to limit the number of chars in this field tx. And different
> lengths will be used when creating tables with this type (so a
> Domain will suffer the same problem). I'm not looking for a
> solution like a separate column with maxChars and a Check, because
> lots of columns will be required (and others design reasons).
Postgres doesn't support parameterized type declarations directly
(that I've ever heard of), but you could probably write a function
that uses EXECUTE to do this.
Erik Jones
DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | R(dot)A(dot) <adeveloper(at)bluebottle(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Can I create a TYPE (or DOMAIN) with arguments? |
Date: | 2008-01-10 15:29:49 |
Message-ID: | b42b73150801100729h423bfc89g54afdc965d3613a3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Jan 9, 2008 4:53 AM, R.A. <adeveloper(at)bluebottle(dot)com> wrote:
> Hello,
>
> I'm trying to create a composite type with an argument, to create one field of this type like character varying(x), but I don't know if this can be done with PostgreSQL. I want something like:
> CREATE TYPE mytype AS (
> tx character varying(x),
> t2nd integer
> );
> I need to limit the number of chars in this field tx. And different lengths will be used when creating tables with this type (so a Domain will suffer the same problem). I'm not looking for a solution like a separate column with maxChars and a Check, because lots of columns will be required (and others design reasons).
It would be really neat if you could do that, but you can't :-(. What
you can do is make a trigger function taking mytype which and apply
the constraint that way...
merlin
From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Erik Jones <erik(at)myemma(dot)com> |
Cc: | "R(dot)A(dot)" <adeveloper(at)bluebottle(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Can I create a TYPE (or DOMAIN) with arguments? |
Date: | 2008-01-10 15:57:43 |
Message-ID: | 20080110155743.GE29714@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, Jan 10, 2008 at 08:58:07AM -0600, Erik Jones wrote:
> Postgres doesn't support parameterized type declarations directly
> (that I've ever heard of), but you could probably write a function
> that uses EXECUTE to do this.
IIRC 8.3 will include the user-defined typmod which will allow such
constructs...
Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
Cc: | Erik Jones <erik(at)myemma(dot)com>, "R(dot)A(dot)" <adeveloper(at)bluebottle(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Can I create a TYPE (or DOMAIN) with arguments? |
Date: | 2008-01-10 16:19:45 |
Message-ID: | 6734.1199981985@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Thu, Jan 10, 2008 at 08:58:07AM -0600, Erik Jones wrote:
>> Postgres doesn't support parameterized type declarations directly
>> (that I've ever heard of), but you could probably write a function
>> that uses EXECUTE to do this.
> IIRC 8.3 will include the user-defined typmod which will allow such
> constructs...
That won't help for this particular problem, though --- composite types
don't take typmods, and there'd be no mechanism to pass it down to the
varchar field if they did. I don't think the OP can solve his problem
just with spare parts. In 8.3 he could write a primitive type that
behaves the way he wants, but it'd take an annoyingly large amount
of custom C code :-(
regards, tom lane
From: | "R(dot)A(dot)" <adeveloper(at)bluebottle(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Martijn van Oosterhout <kleptog(at)svana(dot)org>, Erik Jones <erik(at)myemma(dot)com>, "R(dot)A(dot)" <adeveloper(at)bluebottle(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Can I create a TYPE (or DOMAIN) with arguments? |
Date: | 2008-01-16 13:21:29 |
Message-ID: | 200801161325.m0GDPaUT020296@mi0.bluebottle.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I think the function-trigger approach will be useful to me to bypass this problem.
Thanks to all again for your suggestions!
----------------------------------------------------------------------
Free pop3 email with a spam filter.
http://www.bluebottle.com/tag/5