Thread: 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 logic
>
>       Accept 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 = 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.

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