Terence Kearns wrote:
CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS 'DECLARE BEGIN RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool; END;' LANGUAGE 'sql';
produces this error ERROR: parser: parse error at or near "RETURN" at character 20
I'm trying to create a function to use on a trigger to check reference to views since pg does not support foreign keys referencing views.
First, you are using wrong language. In 'sql' you do not need begin, end or return.
Second, you cannot have trigger functions in sql anyway, so you'd need to change your language to 'plpgsql' - it may than even compile, but I am not sure, because I never used that language.
Third, trigger functions are special in that they can only take constant strings as arguments, so your $1 = $3 is, most probably not going to work. They also must return 'opaque' (in 7.2) or 'triggers' (in 7.3) - you can't return bool, because there is nobody who'd be able to look at the result after the function is called. Instead, you should check your condition, and if it is not satisfied, raise an error to abort the transaction.
Fourth, select count ... may not be very efficient if you just need to check if the key exists - you may be better off with select true ... limit 1;
And finally, you can (relatively easily) write a function that will check if the key exists in the view whenever you insert/update the table... But what about the other way around - what if somebody deletes a key from the underlying table in the view while there is still referencing entries on the other table? You can't have a trigger on a view, so there would be no way to check that...
Why not avoid all that by just creating an FK between the actual table(s), used by the view and the 'child' table you care about?
Dima
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
