Re: [GENERAL] help with plpgsql
am Mon, dem 21.04.2008, um 17:46:49 +0200 mailte Pau Marc Munoz Torres folgendes: Hi everybody I trying to upload some plpsql functions to postgresql database using a perl script and i get the following error psql:/usr/local/Make2D-DB_II/pgsql/make2db_functions.pgsql:85: ERROR: language plpgsql does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language plpgsql already exists anybody knows what's wrong? Wild guess: you have different databases. The language exists in the database 'geldb', but your perl-script works on an other database. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help with plpgsql
Hi everybody I trying to upload some plpsql functions to postgresql database using a perl script and i get the following error psql:/usr/local/Make2D-DB_II /pgsql/make2db_functions.pgsql:85: ERROR: language plpgsql does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language plpgsql already exists anybody knows what's wrong? Thanks -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] help with plpgsql
Pau Marc Munoz Torres wrote: ERROR: language plpgsql already exists anybody knows what's wrong? Is there any chance you might be connecting to a different database with the perl script and with psql? Procedural languages must be installed into a particular database. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help with plpgsql
On Apr 21, 2008, at 8:51 AM, Pau Marc Munoz Torres wrote: psql:/usr/local/Make2D-DB_II /pgsql/make2db_functions.pgsql:85: ERROR: language plpgsql does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language plpgsql already exists Are you certain that you are dealing with the same database in both situations? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help with plpgsql
Pau Marc Munoz Torres wrote: Hi everybody I trying to upload some plpsql functions to postgresql database using a perl script and i get the following error psql:/usr/local/Make2D-DB_II /pgsql/make2db_functions.pgsql:85: ERROR: language plpgsql does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language plpgsql already exists anybody knows what's wrong? Thanks My first guess would be what db are you connected to? Is the script connected to the same db you are connected to when you run psql? I would probably add the CREATE LANGUAGE command to the start of the perl script. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help with plpgsql - subst variable value
*Alexander Bodnar * Try ObjectSeqName TEXT := foobar; EXECUTE 'CREATE SEQUENCE ' || ObjectSeqName || ' start 1 increment 1 maxvalue 2147483647'; Thanks, Chris Steffen ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] help with plpgsql function called by trigger
Heather Johnson wrote: Hello-- I need to make sure that every time a row is inserted into a table called users rows are automatically inserted into two other tables: join_bd and behavior_demographics. The inserts on join_bd and behavior_demographics need to create rows that are keyed to the users table with an integer id (called users_id). The join_bd row that's created also needs to contain a key for a record in behavior_demographics (bd_id). Here's what I did to try and accomplish this: CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS ' declare r RECORD; uid ALIAS FOR $1; begin INSERT INTO behavioral_demographics (users_id) VALUES (uid); SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid; INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid); end; ' LANGUAGE 'plpgsql'; Wouldn't it be nicer to put the second part of that SP in a trigger on behavioral_demographics? That can only work if inserting into users is the only way to insert records into behavioral_demographics, of course. I don't know the names of your columns in behavioral_demographics, but it would look something like this: CREATE FUNCTION insert_bd () RETURNS trigger AS ' begin INSERT INTO behavioral_demographics (users_id) VALUES (NEW.users_id); -- fires trigger on bd end; ' LANGUAGE 'plpgsql'; CREATE FUNCTION insert_join_bd () RETURNS trigger AS ' begin INSERT INTO join_bd (bd_id, users_id) VALUES (NEW.bd_id, NEW.users_id); end; ' LANGUAGE 'plpgsql'; CREATE TRIGGER insert_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd(); CREATE TRIGGER insert_join_bd_on_users AFTER INSERT ON behavioral_demographics FOR EACH ROW EXECUTE PROCEDURE insert_join_bd(); Considering these SP's are basically simple SQL statements, the triggers could also be implemented as a set of query rewrite rules (see CREATE RULE). That's usually more efficient, but I don't have a lot of experience with those... Regards, -- Alban Hertroys MAG Productions ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] help with plpgsql function called by trigger
Thank you! That *is* nicer. And thanks to Michael Fuhr too for his reference to the appropriate docs and suggestions. Between your two responses, I have a much better sense of how to go about this and where to look for extra help. Heather Alban Hertroys wrote: Heather Johnson wrote: Hello-- I need to make sure that every time a row is inserted into a table called users rows are automatically inserted into two other tables: join_bd and behavior_demographics. The inserts on join_bd and behavior_demographics need to create rows that are keyed to the users table with an integer id (called users_id). The join_bd row that's created also needs to contain a key for a record in behavior_demographics (bd_id). Here's what I did to try and accomplish this: CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS ' declare r RECORD; uid ALIAS FOR $1; begin INSERT INTO behavioral_demographics (users_id) VALUES (uid); SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid; INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid); end; ' LANGUAGE 'plpgsql'; Wouldn't it be nicer to put the second part of that SP in a trigger on behavioral_demographics? That can only work if inserting into users is the only way to insert records into behavioral_demographics, of course. I don't know the names of your columns in behavioral_demographics, but it would look something like this: CREATE FUNCTION insert_bd () RETURNS trigger AS ' begin INSERT INTO behavioral_demographics (users_id) VALUES (NEW.users_id);-- fires trigger on bd end; ' LANGUAGE 'plpgsql'; CREATE FUNCTION insert_join_bd () RETURNS trigger AS ' begin INSERT INTO join_bd (bd_id, users_id) VALUES (NEW.bd_id, NEW.users_id); end; ' LANGUAGE 'plpgsql'; CREATE TRIGGER insert_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd(); CREATE TRIGGER insert_join_bd_on_users AFTER INSERT ON behavioral_demographics FOR EACH ROW EXECUTE PROCEDURE insert_join_bd(); Considering these SP's are basically simple SQL statements, the triggers could also be implemented as a set of query rewrite rules (see CREATE RULE). That's usually more efficient, but I don't have a lot of experience with those... Regards, ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] help with plpgsql function called by trigger
On Wed, Mar 16, 2005 at 02:34:48PM -0500, Heather Johnson wrote:
CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS '
What version of PostgreSQL are you using? The opaque type has
been deprecated since 7.3; recent versions should use trigger.
And trigger functions aren't declared with arguments -- if the
function needs arguments, then use TG_ARGV. But I think your code
can use NEW instead of a function argument.
declare
r RECORD;
uid ALIAS FOR $1;
begin
INSERT INTO behavioral_demographics (users_id) VALUES (uid);
SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid;
INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid);
end;
' LANGUAGE 'plpgsql';
Based on the rest of your description, I think you're looking for
something like this:
CREATE FUNCTION insert_bd_join_bd() RETURNS trigger AS '
DECLARE
bdid integer;
BEGIN
INSERT INTO behavioral_demographics (users_id) VALUES (NEW.uid);
bdid := currval(''behavioral_demographics_bdid_seq'');
INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, NEW.uid);
RETURN NULL;
END;
' LANGUAGE plpgsql VOLATILE;
See the Trigger Procedures section of the PL/pgSQL chapter in the
documentation to learn more about NEW (and OLD, TG_ARGV, etc.), and
see the Sequence Manipulation Functions section of the Functions
and Operators chapter to learn more about currval().
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
