From: Dmitry Tkach <dmitry@openratings.com>
To: Terence Kearns <terencek@isd.canberra.edu.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: parse error for function def
Date: Thu, 17 Jul 2003 10:24:43 -0400
Message-ID: <3F16B1AB.70500@openratings.com> (raw)
In-Reply-To: <3F167E9D.2090709@cts.canberra.edu.au>
References: <3F167E9D.2090709@cts.canberra.edu.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, terencek@isd.canberra.edu.au
Subject: Re: parse error for function def
In-Reply-To: <3F16B1AB.70500@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox