Greg Sabino Mullane wrote:
Names shortened to spare the line lengths:
SELECT bob.cid,
(SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1) AS tk1,
(SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 1) AS tk2,
(SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 2) AS tk3,
(SELECT tid FROM at
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> Don't you miss for each subselect an order by tid ?
No: since all the SELECTs are part of one statement, they
will have the same (pseudo-random) implicit order. Since
Josh's requirement said the order of the tids* was not important,
I can be la
On 8/17/2004 10:45 PM, Josh Berkus wrote:
Markus,
Hey, I see you figured out a workaround to writing a trigger for this. Let's
see if we can make it work.
ERROR: there is no unique constraint matching given keys
for referenced table "objects"
The reason for this is that CASCADE behavior
Ð ÐÑÐ, 18.08.2004, Ð 15:33, Jan Wieck ÐÐÑÐÑ:
> Meaning that not enforcing the uniqueness of those columns isn't an
> option.
The thing is that the columns _are_ unique, there's just no unique
constraint on them. They are unique because there's a unique constraint
on a subset of these columns. So
On 8/18/2004 9:49 AM, Markus Bertheau wrote:
Ð ÐÑÐ, 18.08.2004, Ð 15:33, Jan Wieck ÐÐÑÐÑ:
Meaning that not enforcing the uniqueness of those columns isn't an
option.
The thing is that the columns _are_ unique, there's just no unique
constraint on them. They are unique because there's a unique cons
Ð ÐÑÐ, 18.08.2004, Ð 16:06, Jan Wieck ÐÐÑÐÑ:
> I assume it is performance why you are denormalizing your data?
Please have a look at
http://archives.postgresql.org/pgsql-sql/2004-08/msg00157.php
for the schema and an explanation. I'm not denormalizing it as far as I
can tell.
Thanks
--
Marku
On Wed, 18 Aug 2004, Jan Wieck wrote:
> On 8/18/2004 9:49 AM, Markus Bertheau wrote:
>
> > В Срд, 18.08.2004, в 15:33, Jan Wieck пишет:
> >
> >> Meaning that not enforcing the uniqueness of those columns isn't an
> >> option.
> >
> > The thing is that the columns _are_ unique, there's just no uniq
Greg Sabino Mullane wrote:
Don't you miss for each subselect an order by tid ?
No: since all the SELECTs are part of one statement, they
will have the same (pseudo-random) implicit order.
Is this guaranted ?
Regards
Gaetano Mendola
---(end of broadcast)---
"Greg Sabino Mullane" <[EMAIL PROTECTED]> writes:
>> Don't you miss for each subselect an order by tid ?
> No: since all the SELECTs are part of one statement, they
> will have the same (pseudo-random) implicit order.
Nope; Gaetano's right, you cannot assume that. It's entirely possible
for the
Jan Wieck <[EMAIL PROTECTED]> writes:
> However, Bruce, this should be on the TODO list:
> * Allow foreign key to reference a superset of the columns
>covered by a unique constraint on the referenced table.
See the followup discussion as to why this is a bad idea.
Theo Galanakis wrote:
>
> Im running/playing with PG 8.0 locally and want to install the
> contrib/dblink and contrib/crosstab. Can this be done on Windows, is there a
> GMAKE.exe for Windows?? Someone enlighten me!
Uh, good question. I think you need the msys/mingw environment to add
contrib st
> However, Bruce, this should be on the TODO list:
>
> * Allow foreign key to reference a superset of the columns
>covered by a unique constraint on the referenced table.
It would probably be more beneficial to be able to create a unique
constraint without requiring the fields be ind
Tom Lane wrote:
Jan Wieck <[EMAIL PROTECTED]> writes:
However, Bruce, this should be on the TODO list:
* Allow foreign key to reference a superset of the columns
covered by a unique constraint on the referenced table.
See the followup discussion as to why this is a bad idea.
Maybe an alt
Josh Berkus wrote:
Folks,
I have a wierd business case. Annoyingly it has to be written in *portable*
SQL92, which means no arrays or custom aggregates. I think it may be
impossible to do in SQL which is why I thought I'd give the people on this
list a crack at it. Solver gets a free drink/
Richard Huxton <[EMAIL PROTECTED]> writes:
> * Allow multiple unique constraints to share an index where one is a
> superset of the others' columns.
> That way you can mark it unique without having the overhead of multiple
> indexes.
That just moves the uncertain-dependency problem over one spo
On 8/18/2004 12:18 PM, Tom Lane wrote:
Richard Huxton <[EMAIL PROTECTED]> writes:
* Allow multiple unique constraints to share an index where one is a
superset of the others' columns.
That way you can mark it unique without having the overhead of multiple
indexes.
That just moves the uncertain-d
On Wed, 2004-08-18 at 12:27, Jan Wieck wrote:
> On 8/18/2004 12:18 PM, Tom Lane wrote:
>
> > Richard Huxton <[EMAIL PROTECTED]> writes:
> >> * Allow multiple unique constraints to share an index where one is a
> >> superset of the others' columns.
> >
> >> That way you can mark it unique without
Jan Wieck <[EMAIL PROTECTED]> writes:
> If we allow for a unique index, that
> * it is NOT maintained (no index tuples in there)
> * depends on another index that has a subset of columns
> * if that subset-index is dropped, the index becomes maintained
> then the uncertainty is gone.
Joe, Elein:
> This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed
> version; crosstab(sourcesql, ncols)) works. If you really need it to be
> portable, though, application layer procedural code is likely to be the
> easiest and fastest way to go. crosstab just wraps the proced
Jan,
> In the case that a table constraint is a referential constraint,
> the table is referred to as the referencing table. The referenced
> columns of a referential constraint shall be the unique columns of
> some unique constraint of the referenced table.
Missed that one.
On Wed, 18 Aug 2004, Josh Berkus wrote:
> > In the case that a table constraint is a referential constraint,
> > the table is referred to as the referencing table. The referenced
> > columns of a referential constraint shall be the unique columns of
> > some unique constraint
Tom Lane wrote:
Jan Wieck <[EMAIL PROTECTED]> writes:
If we allow for a unique index, that
* it is NOT maintained (no index tuples in there)
* depends on another index that has a subset of columns
* if that subset-index is dropped, the index becomes maintained
then the uncertainty is go
Josh Berkus wrote:
This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed
version; crosstab(sourcesql, ncols)) works. If you really need it to be
portable, though, application layer procedural code is likely to be the
easiest and fastest way to go. crosstab just wraps the procedur
On 8/18/2004 12:46 PM, Tom Lane wrote:
Jan Wieck <[EMAIL PROTECTED]> writes:
If we allow for a unique index, that
* it is NOT maintained (no index tuples in there)
* depends on another index that has a subset of columns
* if that subset-index is dropped, the index becomes maintained
Joe,
> case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8
> -+-+-+-+-+-+-+-+-
>132113 | 021 | 115 | 106 | | | | |
>14 | 106 | 021 | 115 | 108 | 006 | 042 | 142 | 064
>213447 | 047 | | | | | | |
Jan,
>
> If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring
> that the redundant copy of y in b.y stays in sync with a.y.
So? What's denormalized about that? His other choice is to use a trigger.
What he's trying to do is ensure that the class selected for the FK
class_na
On Wed, Aug 18, 2004 at 10:05:13 -0700,
Josh Berkus <[EMAIL PROTECTED]> wrote:
>
> I have my own issue that forced me to use triggers. Given:
>
> table users (
> name
> login PK
> status
> etc. )
>
> table status (
> status
> relation
> label
>
Bruno,
> If users is supposed to reference status you can do this by adding a
> relation column to users, using a constraint to force relation to always be
> 'users' and then having (status, relation) being a foreign key.
But that requires the addition of an extra, indexed Text column to the tabl
Title: RE: [SQL]
> Les, Na grapso PG 8.0 sta arhithia mou?
We acutually use Unix on Prod and Test, however I was just playing locally and was curious how to extent the Win version of PG 8.0.
Theo
-Original Message-
From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]]
Sent: Wednes
On 8/18/2004 2:55 PM, Josh Berkus wrote:
Jan,
If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring
that the redundant copy of y in b.y stays in sync with a.y.
So? What's denormalized about that? His other choice is to use a trigger.
Because the value in b.y is redundant. b.x->
Title: Function Issue!
Can anyone tell me what is wrong with the function below ?
It throws an ERROR: syntax error at or near "FETCH" at character 551
CREATE OR REPLACE FUNCTION "public"."theo_test2" () RETURNS OPAQUE AS'
BEGIN
declare curr_theo cursor for select * from node_names;
f
Theo Galanakis <[EMAIL PROTECTED]> writes:
> Can anyone tell me what is wrong with the function below ?
> CREATE OR REPLACE FUNCTION "public"."theo_test2" () RETURNS OPAQUE AS'
> BEGIN
>declare curr_theo cursor for select * from node_names;
>fetch next from curr_theo;
>close curr_theo
32 matches
Mail list logo
