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
Or rather:
CREATE FUNCTION InList (_Key INTEGER, _PARENT INTEGER) RETURNS BOOLEAN AS $$
Declare
vResult boolean;
BEGIN
Select _Parent IN (SELECT ParentKey From SimpleListTable WHERE Key0 = _Key) into vResult;
Return vResult;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION InList (_Key INTEGER)
RETURNS BOOLEAN AS $$
BEGIN
RETURN EXISTS(SELECT 1 From SimpleListTable WHERE Key0 = _Key);
END;
$$ LANGUAGE plpgsql;
As a recommendation, table and field names in lowercase
Your function Returns true when True but NULL if false.
Looking at all the suggestions I came up with something that works, though I don’t know if it 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;
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:
(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 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!
The only other issues I see is if the ListItems parent is changed…I think that is extremely unlikely
Your function Returns true when True but NULL if false.
Ah yes, that’s because your ParentKeys are apparently NULL sometimes and
Select 1 IN (NULL,2) returns NULL. This is why it is indeed better to work with EXISTS in your scenario.
So I either have to implement the check as a trigger on insert (which I would rather not),
Why not? That would be the standard thing to do. Use a before-insert-trigger and return null from it if it violates the rule or raise an exception.
The only other issues I see is if the ListItems parent is changed…I think that is extremely unlikely
You can make sure it won’t ever change via a before trigger as well. Before triggers are made for this exact purpose - to check if the incoming data adheres to your business rules.