Postgres Function Question

[KarenA]

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

[Maximilian_Tyrtania1 (Maximilian Tyrtania)]

I’d try

RETURN Query Select _Parent IN (SELECT ParentKey From SimpleListTable WHERE Key0 = _Key);

[Maximilian_Tyrtania1 (Maximilian Tyrtania)]

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;

[Mauricio_Pulla]

Hello KarenA

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

Mauricio.

[KarenA]

Hi Maximillian.

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;

Thanks All,
-Karen

[Mauricio_Pulla]

Limit, it is not necessary, you are doing a search by PK…

Mauricio.

[KarenA]

I just realized there are 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 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

Thanks,
-Karen

[Maximilian_Tyrtania1 (Maximilian Tyrtania)]

Hi Karen,

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.