Postgres Function question

[Karen]

I am trying to create what I think should be a simple function all I ever get back when I call it is NIL:

The Table:

CREATE TABLE IF NOT EXISTS SimpleListTable(
	Key0	            		SERIAL PRIMARY KEY,
	ParentKey			INTEGER REFERENCES SimpleListTable(Key0) ON DELETE CASCADE ,
	ID				VarChar(20) NOT NULL,
      	Description     		VarChar(80) ,
   					CONSTRAINT valid_child CHECK( ParentKey <> Key0) );

This table is a list of lists (I did something like this way back in the early 90s in an SQL like language)

I have a bunch of simple short lists that I don’t want to hardcode in the app and also not want to have to create individual tables for…

The Function:

CREATE FUNCTION InList (_Key INTEGER, _PARENT INTEGER) RETURNS BOOLEAN AS $$
BEGIN
   RETURN _Parent IN (SELECT ParentKey From SimpleListTable WHERE Key0 = _Key); 
END;
$$ LANGUAGE plpgsql;

When I test it it always returns NULL
I want to use this function for check constraints on fields in other tables to limit the legal vales to the proper list entries

Anybody know what I am doing wrong?

Thanks,
-Karen

[Torsten_B]

The return type is defined as boolean, the actual value calculated for return is a key.

[Karen]

Doesn’t the IN operator return a boolean?

In any case looking at all the suggestions in the TOF I came up with something that works, though I don’t know if it is the most efficient (cheapest) solution:

CREATE FUNCTION InList (_Key INTEGER, _PARENT INTEGER) RETURNS BOOLEAN AS $$
BEGIN
RETURN EXISTS (SELECT 1 From SimpleListTable WHERE Key0 = _Key AND ParentKey = _PARENT LIMIT 1);
END;
$$ LANGUAGE plpgsql;```

[Torsten_B]

The documentation about when IN returns NULL:

Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the IN construct will be null, not false. This is in accordance with SQL’s normal rules for Boolean combinations of null values.

[Karen]

Thanks,

In any case I juste realized there are issues with by solution…

I just realized other issues… If I use that function in a check constraint it would not allow setting that field to NULL…

Also looking at the Postgres docs they say:

PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. While a CHECK constraint that violates this rule may appear to work in simple tests, it cannot guarantee that the database will not reach a state in which the constraint condition is false (due to subsequent changes of the other row(s) involved). This would cause a database dump and restore to fail. The restore could fail even when the complete database state is consistent with the constraint, due to rows not being loaded in an order that will satisfy the constraint. If possible, use UNIQUE , EXCLUDE , or FOREIGN KEY constraints to express cross-row and cross-table restrictions.

(I don’t Understand Exclude)

This type of check constraint, although it is only about a single field it would likely get checked on data load and it might not matter that the field itself is a foreign Key that gets evaluated after the DB is fully loaded.

So I either have to implement the check as a trigger on insert (which I would rather not), or make sure that during a restore if the listItem does not yet exist, it still passes the check…

I THINK I came up with one:

CREATE FUNCTION InList (_Key INTEGER, _PARENT INTEGER) RETURNS BOOLEAN AS $$
BEGIN
	IF (_Key Is NULL) Then
		RETURN   TRUE;
  	ELSEIF EXISTS (SELECT 1 From SimpleListTable WHERE Key0 = _Key AND ParentKey = _PARENT) Then
  		RETURN TRUE;
  	ELSEIF NOT EXISTS(SELECT 1 From SimpleListTable WHERE Key0 = _Key) THEN
  		RETURN TRUE;
  	ELSE
  		RETURN FALSE;
  	END IF;
END;
$$ LANGUAGE plpgsql;

It looks like it would allow values not in the SimpleListTable at all, but that should not be the case because the field would be defined as a Foreign Key so that would get checked after the DB is fully loaded…

I will tentatively mark this as the solution, but if any Postgres experts see an issue with this please post about it!

Thanks,
-Karen