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