pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
problems creating functions and triggers
2+ messages / 2 participants
[nested] [flat]

* problems creating functions and triggers
@ 2000-01-10 22:32 Werner Fraga <werner.fraga@computalog.com>
  2000-01-11 00:40 ` Re: [SQL] problems creating functions and triggers Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Werner Fraga @ 2000-01-10 22:32 UTC (permalink / raw)
  To: pgsql-sql

I am having problems creating functions and triggers in Postgres 6.5.3
on Linux Mandrake 6.1.

1. I noticed that some examples show the LANGUAGE in the CREATE FUNCTION
command can be 'plpgsql'. However, if I try using this instead of 'sql', I get
ERROR: Unrecognized language specified in a CREATE FUNCTION ...
However, I read somewhere that triggers don't work with LANGUAGE 'sql'.

Am I missing some part of Postgres installation?

2. If I create a function successfully (with LANGUAGE 'sql', then try to
create a trigger using this function, I get

create function checkStationID (int4)
  returns int4 as
'select Station.StationID from Station where Station.StationID = $1;'
language 'SQL';
CREATE

create trigger ifStationExists
  BEFORE INSERT OR UPDATE ON Employee FOR EACH ROW
  EXECUTE PROCEDURE checkStationID (stationID);
ERROR:  CreateTrigger: function checkstationid() does not exist

Is this because LANGUAGE should be 'plpgsql'? The function does exist; I can
run it using 'Select checkStationID (5)'.

3. Is there anywhere I can find a list of the system tables? (that section in
Bruce's book is still empty...)



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] problems creating functions and triggers
  2000-01-10 22:32 problems creating functions and triggers Werner Fraga <werner.fraga@computalog.com>
@ 2000-01-11 00:40 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2000-01-11 00:40 UTC (permalink / raw)
  To: werner.fraga@computalog.com; +Cc: pgsql-sql

Werner Fraga <werner.fraga@computalog.com> writes:
> 1. I noticed that some examples show the LANGUAGE in the CREATE FUNCTION
> command can be 'plpgsql'. However, if I try using this instead of 'sql', I get
> ERROR: Unrecognized language specified in a CREATE FUNCTION ...

You need to run the "createlang" script to install plpgsql in the
particular database you are using.

If you want plpgsql to be available by default in a given installation,
you can install it in template1 --- subsequently created DBs will be
clones of template1.

> 3. Is there anywhere I can find a list of the system tables? (that section in
> Bruce's book is still empty...)

\dS in psql...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-01-11 00:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-10 22:32 problems creating functions and triggers Werner Fraga <werner.fraga@computalog.com>
2000-01-11 00:40 ` Tom Lane <tgl@sss.pgh.pa.us>

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