From: Jonathan Gardner <jgardner@jonathangardner.net>
To: Terence Kearns <terencek@isd.canberra.edu.au>,
	pgsql-sql@postgresql.org
Subject: Re: parse error for function def
Date: Thu, 17 Jul 2003 14:37:01 -0700
User-Agent: KMail/1.5
References: <3F167E9D.2090709@cts.canberra.edu.au>
In-Reply-To: <3F167E9D.2090709@cts.canberra.edu.au>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: clearsigned data
Content-Disposition: inline
Message-Id: <200307171437.02671.jgardner@jonathangardner.net>

-----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 =3D $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=
=20
PL/pgSQL documentation.

I'll give this a shot:

CREATE FUNCTION base.fn_fkey_check(name, name, name, name) RETURNS TRIGGER =
AS=20
'
BEGIN
    " $1 column1 (the table that has the foreign key column)
    " $2 - table2 $3 - column2 (the table/column that is the primary key be=
ing=20
reference)
    EXECUTE ''SELECT $3 FROM $2 WHERE $3=3DNEW.$1'';
    IF FOUND
    THEN RETURN NEW
=20=20=20=20
    RAISE ERROR ''Foreign key violation.'';
    RETURN;
END'
LANGUAGE 'plpgsql';

- --=20
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/Fxb9WgwF3QvpWNwRAh6tAJ9TxkqmKd8NrsQSwadV9FQ8PuSFIACg2sg9
6KPuw+msH/faa8F0xR+FSTI=3D
=3DYo2a
-----END PGP SIGNATURE-----