Message-ID: <3F16B1AB.70500@openratings.com>
Date: Thu, 17 Jul 2003 10:24:43 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: Terence Kearns <terencek@isd.canberra.edu.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: parse error for function def
References: <3F167E9D.2090709@cts.canberra.edu.au>
In-Reply-To: <3F167E9D.2090709@cts.canberra.edu.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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