Обсуждение: language "plpgsql" does not exist
Hi All,
I am trying to build a function to insert or update data in one table from
date stored in another table. Eventually I hope to trigger this process based
on data being INSERTed into the original table. I have written the code below
based on the documentation and some help from this list. When I execute the
code I get this error.
ERROR: language "plpgsql" does not exist
What does this mean? How do I correct the error?
Kind Regards,
Keith
PS This is PostgreSQL v7.3.6 on Red Hat Enterprise Linux v3
CREATE FUNCTION xfer_gl_account_data() RETURNS INTEGER AS '
DECLARE
rcrd_gl_account RECORD;
BEGIN
FOR rcrd_gl_account IN SELECT
data_transfer.tbl_peachtree_gl_acount.account_id,
data_transfer.tbl_peachtree_gl_acount.description,
data_transfer.tbl_peachtree_gl_acount.account_type,
data_transfer.tbl_peachtree_gl_acount.inactive
FROM data_transfer.tbl_peachtree_gl_acount
ORDER BY gl_number
LOOP
SELECT peachtree.tbl_gl_account.account_id
FROM peachtree.tbl_gl_account
WHERE peachtree.tbl_gl_account.account_id = rcrd_gl_account.account_id;
IF NOT FOUND THEN
INSERT INTO peachtree.tbl_gl_account
( peachtree.tbl_gl_account.account_id,
peachtree.tbl_gl_account.description,
peachtree.tbl_gl_account.account_type,
peachtree.tbl_gl_account.inactive )
VALUES ( rcrd_gl_account.account_id,
rcrd_gl_account.description,
rcrd_gl_account.account_type,
rcrd_gl_account.inactive );
ELSE
UPDATE peachtree.tbl_gl_account
SET peachtree.tbl_gl_account.description =
rcrd_gl_account.description,
peachtree.tbl_gl_account.account_type =
rcrd_gl_account.account_type,
peachtree.tbl_gl_account.inactive = rcrd_gl_account.inactive
WHERE peachtree.tbl_gl_account.account_id =
rcrd_gl_account.account_id;
END IF;
END LOOP;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
______________________________________________
99main Internet Services http://www.99main.com
terry@rhino:/esc/pgrnd/prog$ createlang -l rnd Procedural Languages Name | Trusted? ---------+---------- plperl | yes plpgsql | yes Do you get something like the above when you execute the createlang -l command? On Thursday 16 December 2004 06:02 pm, Keith Worthington saith: > Hi All, > > I am trying to build a function to insert or update data in one table from > date stored in another table. Eventually I hope to trigger this process > based on data being INSERTed into the original table. I have written the > code below based on the documentation and some help from this list. When I > execute the code I get this error. > > ERROR: language "plpgsql" does not exist > > What does this mean? How do I correct the error? > > Kind Regards, > Keith > > PS This is PostgreSQL v7.3.6 on Red Hat Enterprise Linux v3 > > CREATE FUNCTION xfer_gl_account_data() RETURNS INTEGER AS ' > DECLARE > rcrd_gl_account RECORD; > BEGIN > FOR rcrd_gl_account IN SELECT > data_transfer.tbl_peachtree_gl_acount.account_id, > > data_transfer.tbl_peachtree_gl_acount.description, > > data_transfer.tbl_peachtree_gl_acount.account_type, > > data_transfer.tbl_peachtree_gl_acount.inactive FROM > data_transfer.tbl_peachtree_gl_acount ORDER BY gl_number > LOOP > SELECT peachtree.tbl_gl_account.account_id > FROM peachtree.tbl_gl_account > WHERE peachtree.tbl_gl_account.account_id = > rcrd_gl_account.account_id; IF NOT FOUND THEN > INSERT INTO peachtree.tbl_gl_account > ( peachtree.tbl_gl_account.account_id, > peachtree.tbl_gl_account.description, > peachtree.tbl_gl_account.account_type, > peachtree.tbl_gl_account.inactive ) > VALUES ( rcrd_gl_account.account_id, > rcrd_gl_account.description, > rcrd_gl_account.account_type, > rcrd_gl_account.inactive ); > ELSE > UPDATE peachtree.tbl_gl_account > SET peachtree.tbl_gl_account.description = > rcrd_gl_account.description, > peachtree.tbl_gl_account.account_type = > rcrd_gl_account.account_type, > peachtree.tbl_gl_account.inactive = > rcrd_gl_account.inactive WHERE peachtree.tbl_gl_account.account_id = > rcrd_gl_account.account_id; > END IF; > END LOOP; > RETURN 1; > END; > ' LANGUAGE 'plpgsql'; > > > ______________________________________________ > 99main Internet Services http://www.99main.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- Quote: 22 "The national budget must be balanced. The public debt must be reduced; the arrogance of the authorities must be moderated and controlled. Payments to foreign governments must be reduced, if the nation doesn't want to go bankrupt. People must again learn to work, instead of living on public assistance." --Marcus Tullius Cicero, 55 B.C. Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com
On Thu, Dec 16, 2004 at 06:02:04PM -0500, Keith Worthington wrote: > I am trying to build a function to insert or update data in one table from > date stored in another table. Eventually I hope to trigger this process based > on data being INSERTed into the original table. I have written the code below > based on the documentation and some help from this list. When I execute the > code I get this error. > > ERROR: language "plpgsql" does not exist Run createlang or CREATE LANGUAGE. http://www.postgresql.org/docs/7.4/static/xplang.html http://www.postgresql.org/docs/7.4/static/app-createlang.html http://www.postgresql.org/docs/7.4/static/sql-createlanguage.html If you want all newly-created databases to have the language, then create it in template1. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
> On Thu, Dec 16, 2004 at 06:02:04PM -0500, Keith Worthington wrote: > > > I am trying to build a function to insert or update data > > in one table from date stored in another table. Eventually > > I hope to trigger this process based on data being INSERTed > > into the original table. I have written the code below > > based on the documentation and some help from this list. > > When I execute the code I get this error. > > > > ERROR: language "plpgsql" does not exist > > Run createlang or CREATE LANGUAGE. > > http://www.postgresql.org/docs/7.4/static/xplang.html > http://www.postgresql.org/docs/7.4/static/app-createlang.html > http://www.postgresql.org/docs/7.4/static/sql-createlanguage.html > > If you want all newly-created databases to have the language, then > create it in template1. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ Great! I am finding my way around the documentation better all the time. :-) Kind Regards, Keith ______________________________________________ 99main Internet Services http://www.99main.com