-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thursday 17 July 2003 03:46, 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.
If you want to write triggers, there is great documentation on that in the
PL/pgSQL documentation.
I'll give this a shot:
CREATE FUNCTION base.fn_fkey_check(name, name, name, name) RETURNS TRIGGER AS
'
BEGIN
" $1 column1 (the table that has the foreign key column)
" $2 - table2 $3 - column2 (the table/column that is the primary key being
reference)
EXECUTE ''SELECT $3 FROM $2 WHERE $3=NEW.$1'';
IF FOUND
THEN RETURN NEW
RAISE ERROR ''Foreign key violation.'';
RETURN;
END'
LANGUAGE 'plpgsql';
- --
Jonathan Gardner <[EMAIL PROTECTED]>
(was [EMAIL PROTECTED])
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/Fxb9WgwF3QvpWNwRAh6tAJ9TxkqmKd8NrsQSwadV9FQ8PuSFIACg2sg9
6KPuw+msH/faa8F0xR+FSTI=
=Yo2a
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
