Thread: parse error for function def

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.



-- 
Terence Kearns ~ ph: +61 2 6201 5516
IT Database/Applications Developer
Enterprise Information Systems
Client Services Division
University of Canberra
www.canberra.edu.au

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> 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.

Can you explain exactly what you are trying to do and why? You are getting 
the error because a SQL function does not RETURN, it must end with a 
SELECT statement. It also has no DECLARE, BEGIN, or END. You can either 
remove all of those or change the language to plpgsql. See:

http://www.postgresql.org/docs/7.3/static/xfunc-sql.html

In addition, you cannot (with SQL) use an argument as the tablename. You 
also probably want to use EXISTS, not "count..::bool".

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200307171005

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE/Fq/7vJuQZxSWSsgRAj01AKCz9BA4aYrp8pnqWy8VHA4i3WGjtgCgjndA
yzNOE52VAvJBOEvilACSGvA=
=EcwZ
-----END PGP SIGNATURE-----

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

On Thu, 17 Jul 2003, 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.

As others have said, the function above is much closer to a plpgsql
function, excepting that you still couldn't use $2 in the from clause
without execute I believe.  In addition, the above isn't going to simulate
a foreign key unless the view is entirely static (if it were that simple,
we'd have implemented it) since changes to the view's base table(s) could
make the constraint be invalid as well.

-----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
theprimary 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 <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=
=Yo2a
-----END PGP SIGNATURE-----