When I try that sample (and other variations) it complains about having the
select in the check (specifically,
Error executing SQL ALTER TABLE password_tokens ADD CONSTRAINT
active_password_token_check CHECK (
SELECT count(*) FROM password_tokens p1, password_tokens p2
WHERE p1.consumed=true
AND p2.consumed=true
AND p1.expiry > now()
AND p2.expiry > now()
) <=1;: ERROR: syntax error at or near "SELECT"
It *is* possible to do a query inside a check, right?
On Friday, April 12, 2013 2:23:42 AM UTC-7, Noel Grandin wrote:
>
> Something like:
>
> ALTER TABLE password_tokens
> ADD CONSTRAINT active_password_token_check
> CHECK (
> SELECT count(*) FROM password_tokens p1, password_tokens p2
> WHERE p1.user_id=p2.user_id
> AND p1.consumed=true
> AND p1.consumed = true
> AND p1.expiry>now()
> AND p2.expiry>now()
> ) <= 1
>
>
> On 2013-04-12 01:00, [email protected] <javascript:> wrote:
>
> Hello
>
> I'm trying to port some things over from a postgres database we have.
> I'm stuck on a check constraint on one of our postgres tables. Basically I
> need to see if any other rows in the table would prevent the current one
> from being inserted.
>
> In this case, two rows cannot
> * share a user id,
> * have "consumed" true, AND
> * have an expiry date some time in the future.
>
> in postgres, I'd use a function, and pass values from the row into the
> function, which would perform a SELECT over the table to see if any
> existing rows would prevent this row from being inserted.
>
> CREATE OR REPLACE FUNCTION check_password_reset_token(int) RETURNS
> boolean AS '
>
> SELECT count(*)>0
> FROM password_tokens
> WHERE (user_id=$1 AND consumed=true AND expiry>now());
>
> ' LANGUAGE SQL;
>
> ALTER TABLE password_tokens ADD CONSTRAINT active_password_token_check
> CHECK (check_password_reset_token(user_id));
>
> When I try the above code in H2, it complains about not understanding
> functions, but when i try to move the whole thing into the check constraint
> in the alter table statement, then I get stuck on actually passing the
> row's values into the parts of the SELECT up there.
>
> I know h2 supports Java functions, so that's a last resort, but we're
> only using h2 for in-memory testing and I don't want to have to test the
> test code, as it were. Is there another way to perform the same check
> constraint in h2?
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
