Re: [SQL] constraint and ordered value
On Dec 29, 2005, at 2:16 AM, Bruno Wolff III wrote: On Wed, Dec 28, 2005 at 00:52:18 +0700, David Garamond <[EMAIL PROTECTED]> wrote: Is it possible to use only CHECK constraint (and not triggers) to completely enforce ordered value of a column (colx) in a table? By that I mean: 1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on; 2. When deleting (or updating), "holes" must not be formed, e.g. if there are three rows then row with colx=3 must be the first one deleted, and then colx=2 the second, and so on. I can see #1 being accomplished using a NOT NULL + UNIQUE constraint and a CHECK constraint that calls some PL function where the function does a simple checking (new.colx=1 if COUNT(colx)==0, or new.colx=MAX (colx)+1). But is it possible to do #2 using only constraints? No. A constraint only applies to one row at a time. If you try to work around this by calling a function that does queries it isn't guarenteed to work. And if you are thinking of calling a function that does a query, you aren't looking at saving time over using triggers. Also, if you are going to have concurrent updates, you are going to need to do table locking to make this work. And, finally, you should ask yourself *why* are you doing this, given that one of the fundamental properties of a table (relation) is that the rows (tuples) are *unordered.* So much of what makes a relational db a wonderful thing for storing data depends on this notion. If you provide an explanation of what you are trying to model, perhaps we can help you find a better schema design. [snip] -- Daryl (setq email '( daryl at eddl dot us )) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Arrays in PL/pgSQL routines?
Can arrays be declared in PL/pgSQL routines? If so, how? Section 8.10 of the documentation (http://www.postgresql.org/docs/7.4/static/arrays.html) tells how to declare and use arrays as table columns. But I don’t find any part of the documentation that says how to declare a simple array local to a PL/pgSQL function. I tried the following guess, but it only won me a “syntax error at or near VARCHAR: DECLARE my_array VARCHAR []; … ~ TIA ~ Ken
Re: [SQL] constraint and ordered value
Daryl Richter wrote: >> No. A constraint only applies to one row at a time. If you try to >> work around >> this by calling a function that does queries it isn't guarenteed to >> work. >> And if you are thinking of calling a function that does a query, you >> aren't >> looking at saving time over using triggers. >> >> Also, if you are going to have concurrent updates, you are going to >> need to >> do table locking to make this work. > > And, finally, you should ask yourself *why* are you doing this, given > that one of the fundamental properties of a table (relation) is that > the rows (tuples) are *unordered.* So much of what makes a relational > db a wonderful thing for storing data depends on this notion. > > If you provide an explanation of what you are trying to model, perhaps > we can help you find a better schema design. Thanks for the insightful answers. Actually I'm just learning about and trying out CHECK constraints in Postgres (and Firebird) :-) Regards, dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Arrays in PL/pgSQL routines?
Ken Winter wrote:
Can arrays be declared in PL/pgSQL routines? If so, how?
DECLARE
try:
my_array VARCHAR[] := '{}';
not sure if this works in 7.4 though, if that's the version that you are
using.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Arrays in PL/pgSQL routines?
On Thu, Dec 29, 2005 at 12:46:28PM -0500, Ken Winter wrote:
> Can arrays be declared in PL/pgSQL routines? If so, how?
>
> Section 8.10 of the documentation
> (http://www.postgresql.org/docs/7.4/static/arrays.html) tells how to declare
> and use arrays as table columns. But I don't find any part of the
> documentation that says how to declare a simple array local to a PL/pgSQL
> function. I tried the following guess, but it only won me a "syntax error
> at or near VARCHAR:
>
> DECLARE
>
> my_array VARCHAR [];
What version of PostgreSQL are you using? Could you post a complete
function instead of just an excerpt? The following works for me in
7.4.10 and later but not in 7.3.12:
CREATE FUNCTION foo(varchar, varchar, varchar) RETURNS varchar[] AS '
DECLARE
my_array varchar[] := ''{}'';
BEGIN
my_array[1] := $1;
my_array[2] := $2;
my_array[3] := $3;
RETURN my_array;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;
SELECT foo('a', 'b', 'c');
foo
-
{a,b,c}
(1 row)
Array handling was improved in 7.4; in earlier versions you'll
probably get an error like the following after SELECT:
WARNING: plpgsql: ERROR during compile of foo near line 4
ERROR: syntax error at or near "["
That's a little different than your "syntax error at or near VARCHAR."
Was that the actual error message?
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Arrays in PL/pgSQL routines?
Bricklen ~
That works. (Odd that the initialization seems to be necessary to make it
work.) Thanks! Yes, I'm using version 7.4.
~ Ken
> -Original Message-
> From: Bricklen Anderson [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 29, 2005 12:53 PM
> To: Ken Winter
> Cc: PostgreSQL pg-sql list
> Subject: Re: [SQL] Arrays in PL/pgSQL routines?
>
> Ken Winter wrote:
> > Can arrays be declared in PL/pgSQL routines? If so, how?
> >
>
> > DECLARE
> >
> try:
> my_array VARCHAR[] := '{}';
>
> not sure if this works in 7.4 though, if that's the version that you are
> using.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Arrays in PL/pgSQL routines?
"Ken Winter" <[EMAIL PROTECTED]> writes: > That works. (Odd that the initialization seems to be necessary to make it > work.) Thanks! Yes, I'm using version 7.4. Possibly what you're running into is that 7.4 is restrictive about what you can do with an array that's NULL. Until pretty recently, assigning to an element of a NULL array left the array still NULL --- this is an artifact of the fact that the system sees the assignment as a binary operation with the array and the new element as inputs. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Arrays in PL/pgSQL routines?
On Thu, Dec 29, 2005 at 01:23:28PM -0500, Ken Winter wrote: > That works. (Odd that the initialization seems to be necessary to make it > work.) Thanks! Yes, I'm using version 7.4. 7.4.what? Absence of initialization shouldn't cause a syntax error; at least it doesn't in 7.4.10. However, in 7.4 you do have to initialize an array before assigning to its elements, else the array will remain NULL (initialization isn't necessary in the latest 8.x releases). -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
