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

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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/s...reatelang.html
http://www.postgresql.org/docs/7.4/s...elanguage.html

If you want all newly-created databases to have the language, then
create it in template1.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

> 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/s...reatelang.html
> http://www.postgresql.org/docs/7.4/s...elanguage.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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org