Assertion constraint replacement?
Hello,
sql-createtable.html says about CONSTRAINTS:
CHECK (expression)
CHECK clauses specify integrity constraints or tests which new or
updated rows must satisfy for an insert or update operation to
succeed. Each constraint must be an expression producing a Boolean
result. A condition appearing within a column definition should
reference that column's value only, while a condition appearing as
a table constraint may reference multiple columns.
Currently, CHECK expressions cannot contain subselects nor refer
to variables other than columns of the current row.
But I want to add a constraint which has to check the value to insert
into a certain table column which has to be obtained from another
table under certain WHERE conditions (not just an index).
Going on reading sql-createtable.html:
Assertions
An assertion is a special type of integrity constraint and shares
the same namespace as other constraints. However, an assertion is
not necessarily dependent on one particular table as constraints
are, so SQL92 provides the CREATE ASSERTION statement as an
alternate method for defining a constraint:
CREATE ASSERTION name CHECK ( condition )
PostgreSQL does not implement assertions at present.
So Assertions might be the thing I'm looking for. How can I implement a
kind of logic
Accept value for column if it is contained in
select SomeId from OtherTable where SomeOtherColumn = Value ;
Kind regards
Andreas.
On Thu, 16 May 2002, Tille, Andreas wrote:
An assertion is a special type of integrity constraint and shares
the same namespace as other constraints. However, an assertion is
not necessarily dependent on one particular table as constraints
are, so SQL92 provides the CREATE ASSERTION statement as an
alternate method for defining a constraint:CREATE ASSERTION name CHECK ( condition )
PostgreSQL does not implement assertions at present.
So Assertions might be the thing I'm looking for. How can I implement a
kind of logicAccept value for column if it is contained in
select SomeId from OtherTable where SomeOtherColumn = Value ;
Triggers are probably your best bet. Note too that changes to othertable
may also make the assertion fail, so probably an insert/update trigger
on the main table and update/delete trigger on OtherTable.
Hi all,
I am trying load an array from a text column.
select '''{' || concatkey || '}''' from visitor where user_id = 477373
returns
'{151038144,0,0,0,0,101931,0,0,0,0}'
This looks like the string I need to load the Array.
But...
create temp table test
(
tesrp int8 [10]
)
insert into test
select '''{' || concatkey || '}''' from visitor where user_id = 477373
returns
Error: ' but expression is of type 'text'
You will need to rewrite or cast the expression (State:S1000, Native Code:
7)
I'm sure I need to CAST the result... But to what type.. I have tried lots
of different things but still carn't work it out.
Can someone help...
Thanks
Andrew
On Fri, 17 May 2002 09:00:27 +1000
"Andrew Bartley" <abartley@evolvosystems.com> wrote:
insert into test
select '''{' || concatkey || '}''' from visitor where user_id = 477373returns
Error: ' but expression is of type 'text'
You will need to rewrite or cast the expression (State:S1000, Native Code:
7)I'm sure I need to CAST the result... But to what type.. I have tried lots
of different things but still carn't work it out.
Hi, Andrew.
No matter what type you cast the result to, it seems like there's no chance
that it can be inserted into array's column; actually, I couldn't either.
But, if using a dynamic query in plpgsql, you would be able to insert.
CREATE OR REPLACE FUNCTION fn_visitor (int4) RETURNS boolean AS '
DECLARE
sql text';
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM visitor WHERE user_id = $1 LOOP
sql := ''insert into test values(''''{''
|| rec.concatkey
|| ''}'''');'';
EXECUTE sql;
RAISE NOTICE ''% is inserted.'', rec.concatkey;
END LOOP;
RETURN true;
END;
' language 'plpgsql' ;
SELECT fn_visitor(477373);
Regards,
Masaru Sugawara