pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedparse error for function def
5+ messages / 5 participants
[nested] [flat]
* parse error for function def
@ 2003-07-17 10:46 Terence Kearns <terencek@isd.canberra.edu.au>
0 siblings, 4 replies; 5+ messages in thread
From: Terence Kearns @ 2003-07-17 10:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: parse error for function def
@ 2003-07-17 14:19 greg@turnstep.com
parent: Terence Kearns <terencek@isd.canberra.edu.au>
3 siblings, 0 replies; 5+ messages in thread
From: greg@turnstep.com @ 2003-07-17 14:19 UTC (permalink / raw)
To: pgsql-sql; +Cc: terencek@isd.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-----
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: parse error for function def
@ 2003-07-17 14:24 Dmitry Tkach <dmitry@openratings.com>
parent: Terence Kearns <terencek@isd.canberra.edu.au>
3 siblings, 0 replies; 5+ messages in thread
From: Dmitry Tkach @ 2003-07-17 14:24 UTC (permalink / raw)
To: Terence Kearns <terencek@isd.canberra.edu.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: parse error for function def
@ 2003-07-17 15:39 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Terence Kearns <terencek@isd.canberra.edu.au>
3 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-07-17 15:39 UTC (permalink / raw)
To: Terence Kearns <terencek@isd.canberra.edu.au>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: parse error for function def
@ 2003-07-17 21:37 Jonathan Gardner <jgardner@jonathangardner.net>
parent: Terence Kearns <terencek@isd.canberra.edu.au>
3 siblings, 0 replies; 5+ messages in thread
From: Jonathan Gardner @ 2003-07-17 21:37 UTC (permalink / raw)
To: Terence Kearns <terencek@isd.canberra.edu.au>; pgsql-sql
-----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 <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-----
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-17 21:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-17 10:46 parse error for function def Terence Kearns <terencek@isd.canberra.edu.au>
2003-07-17 14:19 ` greg@turnstep.com
2003-07-17 14:24 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-17 15:39 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-17 21:37 ` Jonathan Gardner <jgardner@jonathangardner.net>
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