Chris, thank you for your time. Your reply was really helpful!
I followed your advice. I didn't sacrifice relational design (I didn't have
this intention) and I used joins/views.
Kind Regards,
George Antonopoulos
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Compos
On Wed, Jan 29, 2014 at 1:44 PM, George Ant wrote:
> Guys thank you for your replies, you really helped me a lot!!!
>
> I haven't use Postgres before and its the first time I am "playing" with
> composite types, so sorry if I hurted your eyes with my question!
>
> Kevin I followed your suggestion
Guys thank you for your replies, you really helped me a lot!!!
I haven't use Postgres before and its the first time I am "playing" with
composite types, so sorry if I hurted your eyes with my question!
Kevin I followed your suggestion and seems to work fine.I think it is what
I was looking for.
As a note, the following also works:
CREATE TYPE foo AS (bar text, baz int);
CREATE TABLE table_of_foo OF foo (primary key(bar));
The one thing that doesn't work is the REF syntax and the operators that go
along with that. However, you could come up with dereferencing functions
and operators one
antono124 wrote:
> Lets say that we have 2 tables.
> Create Table "table1" Of "type1"
> Create Table "table2" Of "type2"
>
> I want to refer the first table in the second. I want to
> reference the whole table not only one field, so something like
> that:
>
> CREATE TYPE type2 AS OBJECT (
> va
On 1/26/2014 9:02 AM, antono124 wrote:
I want to refer the first table in the second. I want to reference the whole
table not only one field, so something like that:
does this table1 have a primary key? if so, referencing the PK allows
you to fetch the whole row via a join. if this table do
On Mon, Jan 27, 2014 at 2:02 AM, antono124 wrote:
> Lets say that we have 2 tables.
> Create Table "table1" Of "type1"
> Create Table "table2" Of "type2"
>
> I want to refer the first table in the second. I want to reference the whole
> table not only one field, so something like that:
>
> CREATE
Hello guys!
I have found a example in Oracle and I am trying to do it in Postgre.
Lets say that we have 2 tables.
Create Table "table1" Of "type1"
Create Table "table2" Of "type2"
I want to refer the first table in the second. I want to reference the whole
table not only one field, so somethi
Merlin writes"
first, the way to do insert from composite type is like this:
insert into foo select (f).*;
if f is type of foo. The actual error you're getting is probably"
I gave this a try. Still bumping into syntax errors
CREATE TYPE oww_mart_tbls.type_log_site_process AS
(proc_id i
On Wed, Jun 13, 2012 at 12:25 PM, Little, Douglas
wrote:
> Hello,
>
> Thanks in advance for taking my question.
>
> Running on Greenplum 4.1.2/ based on PG 8.2.3 kernal
>
>
>
> We make extensive use of functions to do our ETL.
>
> So, I’m building a stored procedure template for our developers.
>
Hello,
Thanks in advance for taking my question.
Running on Greenplum 4.1.2/ based on PG 8.2.3 kernal
We make extensive use of functions to do our ETL.
So, I'm building a stored procedure template for our developers.
I'd like the template to log the sql statements to a logging table for
audit/deb
On Thu, Jan 26, 2012 at 3:22 AM, Leguevaques Alex
wrote:
>
> Hello,
> I'm new to Pg and exploring its advanced functionalities for a project.
> I find composite type very interesting, but what are problems/limitations ?
> I'd want to create this structure for example:
Composite types add a little
Hello,
I'm new to Pg and exploring its advanced functionalities for a project.
I find composite type very interesting, but what are problems/limitations ?
I'd want to create this structure for example:
Phone
Nom du champ
Type
Accès
Clef
Commentaire
typ_tel
integer
type téléphone (liste 25)
tel
On Wed, Oct 19, 2011 at 9:32 AM, erhaminus wrote:
>
> Hi,
>
> Is a way to define DEFAULT, NOT NULL and REFERENCES for members of composite
> type?
>
> For example:
>
> -- type
> CREATE TYPE bibl.bibliography AS
> (
> edition TEXT,
> publisher_id BIGINT
> );
>
> -- table def
> create
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of erhaminus
Sent: Wednesday, October 19, 2011 10:33 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Composite type, DEFAULT, NOT NULL, REFERENCES
Hi,
Is a way to
Hi,
Is a way to define DEFAULT, NOT NULL and REFERENCES for members of composite
type?
For example:
-- type
CREATE TYPE bibl.bibliography AS
(
edition TEXT,
publisher_id BIGINT
);
-- table def
create table bibl.monograph
(
id BIGSERIAL PRIMARY KEY
On 6. Okt, 08:48 h., Trigve wrote:
> Hi,
> I'm trying to make a custom composite type and use it as a PK and FK.
> When adding FK to table I've got trhis error: operator is not unique:
> "BigintRef" pg_catalog.= "BigintRef" (see below). Here is my type
> definition with operator:
>
> CREATE TYPE "
On Wed, Oct 6, 2010 at 2:48 AM, Trigve wrote:
> Hi,
> I'm trying to make a custom composite type and use it as a PK and FK.
> When adding FK to table I've got trhis error: operator is not unique:
> "BigintRef" pg_catalog.= "BigintRef" (see below). Here is my type
> definition with operator:
>
> CR
Hi,
I'm trying to make a custom composite type and use it as a PK and FK.
When adding FK to table I've got trhis error: operator is not unique:
"BigintRef" pg_catalog.= "BigintRef" (see below). Here is my type
definition with operator:
CREATE TYPE "BigintRef" AS
("Value" bigint,
"Null" bool
Grzegorz Jaśkiewicz wrote:
On Fri, May 29, 2009 at 3:37 AM, Scott Bailey wrote:
Did you read the article I sent you earlier?
Well, the difference here is that this way db doesn't really check
anything :) you just choose path of execution, that you created prior.
That's cheating :p
So yes, I
On Fri, May 29, 2009 at 3:37 AM, Scott Bailey wrote:
> Did you read the article I sent you earlier?
Well, the difference here is that this way db doesn't really check
anything :) you just choose path of execution, that you created prior.
That's cheating :p
So yes, I read that article, but that's
Grzegorz Jaśkiewicz wrote:
2009/5/27 Scott Bailey :
Who said anything about the application level?
can you give an example please ?
Did you read the article I sent you earlier? I'm doing almost the exact
same thing you are doing save the bytea field. I create a timespan
composite type t
2009/5/27 Scott Bailey :
> Who said anything about the application level?
can you give an example please ?
--
GJ
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Grzegorz Jaśkiewicz wrote:
well, I need database to guard data, not application.
Application can check things too, but database's job is to make sure
data is integral.
Who said anything about the application level?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
well, I need database to guard data, not application.
Application can check things too, but database's job is to make sure
data is integral.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Scott Bailey wrote:
Grzegorz Jaśkiewicz wrote:
Why is it not possible to create domain on composite type ?
Consider the example, I got (a bytea, b timestamp, c timestamp). Where
b < c always, and both b and c have some default value, a can stay
null.
Now, I don't want to go berserk, and create
Grzegorz Jaśkiewicz wrote:
Why is it not possible to create domain on composite type ?
Consider the example, I got (a bytea, b timestamp, c timestamp). Where
b < c always, and both b and c have some default value, a can stay
null.
Now, I don't want to go berserk, and create aditional table for t
When I start to complain about domains and types in postgresql, people
often ask me - so what's exactly wrong with it - well, here you go. I
am trying to provide some feedback ;)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www
2009/5/25 Grzegorz Jaśkiewicz :
> Why is it not possible to create domain on composite type ?
>
> Consider the example, I got (a bytea, b timestamp, c timestamp). Where
> b < c always, and both b and c have some default value, a can stay
> null.
> Now, I don't want to go berserk, and create adition
Why is it not possible to create domain on composite type ?
Consider the example, I got (a bytea, b timestamp, c timestamp). Where
b < c always, and both b and c have some default value, a can stay
null.
Now, I don't want to go berserk, and create aditional table for that,
because type is shared b
On Wed, Dec 17, 2008 at 12:23 PM, Andrew Gould
wrote:
> What are composite types used for? Do they allow you to search multiple
> fields for a value more easily?
A number of things really. Starting with 8.4, they can be used with
indexes and comparisons. so the list is growing. The main utilit
What are composite types used for? Do they allow you to search multiple
fields for a value more easily?
Thanks,
Andrew
On Wed, Dec 17, 2008 at 11:09 AM, Ketema Harris wrote:
> I have just re-read chapter 8.15 of the 8.3 manual and I am understanding
> why a composite type would evaluate to NUll if any of it elements are null.
>
> Can anyone explain this behavior? To me if I have a composite type column
> and ther
I have just re-read chapter 8.15 of the 8.3 manual and I am
understanding why a composite type would evaluate to NUll if any of it
elements are null.
Can anyone explain this behavior? To me if I have a composite type
column and there are some values in it, its incomplete, yes, but not
nu
Hi,
I'm using composite types within my database and I sometimes need to
modify type either by adding new column or e.g. renaming a column. Of
course I can't do that on existing composite type (actually I can, but
that is quite complicated) so maybe I should use table instead ? With
tables (th
On 5/24/07, Marek Lewczuk <[EMAIL PROTECTED]> wrote:
Hello everyone,
it's great to have in next release (8.3) great feature: composite type
array. I'm waiting to see how it works, however I wonder whether it will
be possible to create an index on a column of composite type array just
like we have
Hello everyone,
it's great to have in next release (8.3) great feature: composite type
array. I'm waiting to see how it works, however I wonder whether it will
be possible to create an index on a column of composite type array just
like we have an index on integer[] column (using gin or intarra
On Sun, Nov 19, 2006 at 02:09:11AM -0500, Tom Lane wrote:
> Ron Peterson <[EMAIL PROTECTED]> writes:
> > How should I create a composite type value out of columns a and b in
> > table tt that I can insert into table atable?
>
> Hm, it works for me with an explicit cast:
>
> INSERT INTO
> atable
Ron Peterson <[EMAIL PROTECTED]> writes:
> How should I create a composite type value out of columns a and b in
> table tt that I can insert into table atable?
Hm, it works for me with an explicit cast:
INSERT INTO
atable
SELECT
ROW(a, b)::atype
FROM
tt;
Perhaps we should allow this in an
I'm trying to understand how to insert a composite type value. I'm
having trouble with the last sql statement below. It errors out with:
ERROR: cannot cast type record to atype
How should I create a composite type value out of columns a and b in
table tt that I can insert into table atable?
C
Juan Miguel Paredes <[EMAIL PROTECTED]> writes:
> DECLARE
> res helpdesk.tp_res_conestadosdisponiblessoporte;
> BEGIN
> IF estadoactual = 'Abierto' THEN
> SELECT INTO res.filas *
> FROM tbl_estadosoporte
Without having looked at the code, I suspect that plpgsql just assumes
res.filas
Hi, folks!
Reading previous posts on returning composite types in pl/pgsql, I
still haven't found a good answer for this issue:
Let's say we create this table:
/*/
CREATE TABLE "tbl_estadosoporte" (
"id" CHAR(1
On Apr 13, 2005, at 11:50 AM, Tom Lane wrote:
Thank you for the great info. If I may, here's another question. I am
in
the need of new scalar types, essentially domain'd smallints, hence
why my composite type had but one composite member. Domain'd
smallints would be great, but it seems when they g
James Robinson <[EMAIL PROTECTED]> writes:
> Thank you for the great info. If I may, here's another question. I am in
> the need of new scalar types, essentially domain'd smallints, hence
> why my composite type had but one composite member. Domain'd
> smallints would be great, but it seems when th
On Apr 12, 2005, at 4:48 PM, Tom Lane wrote:
James Robinson <[EMAIL PROTECTED]> writes:
insert into simple_table values (null, '(43)'); -- GRR works!!! It'll
let any smallint in. What happened to the constraint?
The composite-type input routine doesn't check any constraints ...
and that includes do
James Robinson <[EMAIL PROTECTED]> writes:
> insert into simple_table values (null, '(43)'); -- GRR works!!! It'll
> let any smallint in. What happened to the constraint?
The composite-type input routine doesn't check any constraints ...
and that includes domains. You can make it work if you don
I'm trying to experiment with domains and composite types under 8.0.2.
It seems that domain constraints don't fire when the domain is embedded
within a composite type:
---
create domain simple as smallint default 0 constraint limits check
(VALUE IN (0,1,2,3));
create type comp_simple as ( simp_
<[EMAIL PROTECTED]> writes:
> My question is - HOW do I construct the argument to
> my_function(my_type) so that the function recognises that I am passing
> into it composite data?
Sorry, don't think you're going to have much joy on that in 7.3.
In 8.0 you could do it with a ROW() constructor, bu
create type my_type (my_test text, my_int integer);
create function my_function(my_type)
returns timestamp as
'begin
return (current_timestamp);
end;
'
language 'plpgsql';
In 7.3.5, we are trying to get around the 32 argument limit of the
functions WITHOUT re-compiling with a greater func_max_ar
Ivan Sergio Borgonovo wrote:
On Tue, 27 Apr 2004 10:12:13 -0700
thisSession := ( ''t'', md5( now( ) || rand( ) ) );
- md5 takes TEXT as an argument, not a numeric type
Since it works you surely fixed my code but this should't be an issue
since I tried
test1=# select md5( now( ) || r
On Tue, 27 Apr 2004 10:12:13 -0700
Ron St-Pierre <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo wrote:
> > --HERE!!!
> > thisSession := ( ''t'', md5( now( ) || rand( ) ) );
> >
> - md5 takes TEXT as an argument, not a numeric type
Since it works you surely fixed my code but this shou
Ron St-Pierre wrote:
Ivan Sergio Borgonovo wrote:
what's wrong with this?
create type tSession
as ( ty_found boolean, ty_Session char(32) );
create or replace function GetSessionID( integer )
returns tSession as '
declare
thisSession tSession;
begin
--HERE!!!
thisSession := ( ''
Ivan Sergio Borgonovo wrote:
what's wrong with this?
create type tSession
as ( ty_found boolean, ty_Session char(32) );
create or replace function GetSessionID( integer )
returns tSession as '
declare
thisSession tSession;
begin
--HERE!!!
thisSession := ( ''t
what's wrong with this?
create type tSession
as ( ty_found boolean, ty_Session char(32) );
create or replace function GetSessionID( integer )
returns tSession as '
declare
thisSession tSession;
begin
--HERE!!!
thisSession := ( ''t'', md5( now( ) || rand( )
54 matches
Mail list logo
